Index Analysis - SQL Server Query Performance Tuning - page 147

The combination of the two indexes acts like a covering index reducing the reads against the table from 689 to 4
because it's using two Index Seek operations joined together instead of a clustered index scan.
But what if the WHERE clause didn't result in both indexes being used? Instead, you know that both indexes exist
and that a seek against each would work like the previous query, so you choose to use an index hint.

SELECT soh.SalesPersonID,
soh.OrderDate
FROM Sales.SalesOrderHeader AS soh WITH
(INDEX (IX_Test,
IX_SalesOrderHeader_SalesPersonID))
WHERE soh.OrderDate BETWEEN '4/1/2002' AND '7/1/2002';

The results of this new query are shown in Figure 9-7 , and the I/O is as follows:

Table 'Workfile'. Scan count 0, logical reads 0
Table 'Worktable'. Scan count 0, logical reads 0
Table 'SalesOrderHeader'. Scan count 2, logical reads 59
CPU time = 16 ms, elapsed time = 144 ms.

Figure 9-7. Execution plan with index join through a hint

The reads have clearly increased, and you have work tables and work files that use tempdb to store data during
the processing. Most of the time, the optimizer makes good choices when it comes to indexes and execution plans.
Although query hints are available to allow you to take control from the optimizer, this control can cause as many
problems as it solves. In attempting to force an index join as a performance benefit, instead the forced selection of
indexes slowed down the execution of the query.
Remove the test index before continuing.

DROP INDEX Sales.SalesOrderHeader.IX_Test;

■ While generating a query execution plan, the sQl server optimizer goes through the optimization phases not only
to determine the type of index and join strategy to be used but also to evaluate the advanced indexing techniques such as
index intersection and index join. therefore, in some cases, instead of creating wide covering indexes, consider creating
multiple narrow indexes. sQl server can use them together to serve as a covering index yet use them separately where
required. But you will need to test to be sure which works better in your situation—wider indexes or index intersections
and joins.

Note