Query Optimization and Execution - Pro SQL Server Internals - page 473

SQL Server can use either Scan or Seek access methods with an index. Listing 25-19 shows an example of INDEX
hint usage, which forces SQL Server to use the IDX_Orders_OrderDate index in the query.
Listing 25-19. INDEX Query Hint
select OrderId, OrderDate, CustomerID, Total
from dbo.Orders with (Index = IDX_Orders_OrderDate)
where OrderDate between @StartDate and @EndDate
One of the legitimate use-cases for an INDEX query hint is forcing SQL Server to use one of the composite indexes
in those cases where correct cardinality estimation is impossible. Consider the case when a table stores location
information for multiple devices that belong to different accounts, as shown in Listing 25-20. Let's assume that
DeviceId is unique only within a single account.
Listing 25-20. Composite indexes and uneven data distribution: Table Creation
create table dbo.Locations
(
AccountId int not null,
DeviceId int not null,
UtcTimeTag datetime2(0) not null,
/* Other Columns */
);
create unique clustered index IDX_Locations_AccountId_UtcTimeTag_DeviceId
on dbo.Locations(AccountId, UtcTimeTag, DeviceId);
create unique nonclustered index IDX_Locations_AccountId_DeviceId_UtcTimeTag
on dbo.Locations(AccountId, DeviceId, UtcTimeTag);
The table stores information for multiple accounts, and it is common to have data distributed very unevenly in
such a scenario where some accounts have hundreds or even thousands of devices while others have just a few of
them. Let's assume that we would like to select the data that belongs to a subset of devices for a specific time frame,
as shown in Listing 25-21.
Listing 25-21. Composite indexes and uneven data distribution: Query
select *
from dbo.Locations
where
AccountId = @AccountID and
UtcTimeTag between @StartTime and @StopTime and
DeviceID in (select DeviceID from #ListOfDevices);
SQL Server has two different choices for the execution plan. The first choice uses Nonclustered Index Seek and
Key Lookup , which is better when you need to select data for a very small percent of the devices from the account.
In all other cases, it is more efficient to use Clustered Index Seek with AccountId and UtcTimeTag as seek predicates,
and to perform a range scan for all devices that belong to the account.
Unfortunately, SQL Server would not have enough data to perform correct cardinality estimation in either case.
It can estimate the selectivity of particular AccountID data based on the histogram from either index; however, it is not
enough to estimate cardinality for the list of devices.