Database Reference
In-Depth Information
Figure 9-4.
Execution plan with an index on the OrderDate column
As you can see, SQL Server exploited both the nonclustered indexes as index seeks (rather than scans) and then
employed an intersection algorithm to obtain the index intersection of the two subsets. It then did a
Key Lookup
from the resulting dataset to retrieve the rest of the data not included in the indexes. But, the complexity of the plan
suggests that performance might be worse. Checking the statistics I/O and time, you can see that in fact you did get a
good performance improvement:
Table 'SalesOrderHeader'. Scan count 2, logical reads 10
CPU time = 0 ms, elapsed time = 31 ms.
The reads dropped from 689 to 10 even though the plan used three different access points within the table. The
execution time also dropped. You can also see there are additional operations occurring within the plan, such as the
Sort
and the
Key Lookup
, that you might be able to eliminate with further adjustments to the indexes. However, it's
worth noting, since you're returning all the columns through the
SELECT *
command, that you can't eliminate the
Key
Lookup
by using
INCLUDE
columns, so you may also need to adjust the query.
To improve the performance of a query, SQL Server can use multiple indexes on a table. Therefore, instead of
creating wide index keys, consider creating multiple narrow indexes. SQL Server will be able to use them together
where required, and when not required, queries benefit from narrow indexes. While creating a covering index,
determine whether the width of the index will be acceptable and whether using include columns will get the job
done. If not, then identify the existing nonclustered indexes that include most of the columns required by the covering
index. You may already have two existing nonclustered indexes that jointly serve all the columns required by the
covering index. If it is possible, rearrange the column order of the existing nonclustered indexes appropriately,
allowing the optimizer to consider an index intersection between the two nonclustered indexes.
At times, it is possible that you may have to create a separate nonclustered index for the following reasons:
•
Reordering the columns in one of the existing indexes is not allowed.
•
Some of the columns required by the covering index may not be included in the existing
nonclustered indexes.
•
The total number of columns in the two existing nonclustered indexes may be more than the
number of columns required by the covering index.
In such cases, you can create a nonclustered index on the remaining columns. If the combined column order of
the new index and an existing nonclustered index meets the requirement of the covering index, the optimizer will
be able to use index intersection. While identifying the columns and their order for the new index, try to maximize
their benefit by keeping an eye on other queries, too.
Drop the index that was created for the tests.
DROP INDEX Sales.SalesOrderHeader.IX_Test;
