Index Analysis - SQL Server Query Performance Tuning - page 144

Before building a lot of covering indexes, consider how SQL Server can effectively and automatically create
covering indexes for queries on the fly using index intersection.

Index Intersections
If a table has multiple indexes, then SQL Server can use multiple indexes to execute a query. SQL Server can
take advantage of multiple indexes, selecting small subsets of data based on each index and then performing an
intersection of the two subsets (that is, returning only those rows that meet all the criteria). SQL Server can exploit
multiple indexes on a table and then employ a join algorithm to obtain the index intersection between the two subsets.
In the following SELECT statement, for the WHERE clause columns, the table has a nonclustered index on the
SalesPersonID column, but it has no index on the OrderDate column:

--SELECT * is intentionally used in this query
SELECT soh.*
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesPersonID = 276
AND soh.OrderDate BETWEEN '4/1/2005' AND '7/1/2005';

Figure 9-3 shows the execution plan for this query.

Figure 9-3. Execution plan with no index on the OrderDate column

As you can see, the optimizer didn't use the nonclustered index on the SalesPersonID column. Since the value
of the OrderDate column is also required, the optimizer chose the clustered index to fetch the value of all the referred
columns. The I/O for retrieving this data was as follows:

Table 'SalesOrderHeader'. Scan count 1, logical reads 689
CPU time = 16 ms, elapsed time = 47 ms.

To improve the performance of the query, the OrderDate column can be added to the nonclustered index on the
SalesPersonId column or defined as an included column on the same index. But in this real-world scenario, you may
have to consider the following while modifying an existing index:

• It may not be permissible to modify an existing index for various reasons.

• The existing nonclustered index key may be already quite wide.

• The cost of other queries using the existing index will be affected by the modification.

In such cases, you can create a new nonclustered index on the OrderDate column.

CREATE NONCLUSTERED INDEX IX_Test
ON Sales.SalesOrderHeader (OrderDate);

Run your SELECT command again.
Figure 9-4 shows the resultant execution plan of the SELECT statement.