1030
Part VII: Performance Tuning and Optimization
Query Path 9 — Filter by Unordered Composite Index
One common indexing myth is that the order of the index key columns doesn’t matter, that
is, SQL Server can use an index so long as the column is anywhere in the index. Like most
myths, it’s a half truth.Searching an index requires the leading index key column to be present in the search
predicate. Searching for col1, col2 works great when the index includes col1 as the lead-
ing index key with col2 following it. However, searching solely for col2 without col1 in
the predicate requires scanning all the leaf level data if another suitable index is not
present.Query Path 9 demonstrates the ineffi ciency of fi ltering on an index column that is not the
leading index key column.StartDate is the second key in the composite index, so the data is there. Will the query
use the index?SELECT WorkOrderID
FROM Production.WorkOrder
WHERE StartDate = '2006-01-04';The query optimizer uses the IX_WorkOrder_ProductID composite nonclustered index,
as shown in Figure 45-16, because it’s narrower than the clustered index, so more rows fi t
on a page. Because the fi lter is by the second column, it can’t use the index; instead SQL
Server is forced to scan every row and fi lter (in the scan operation) to select the correct
rows. Essentially, it’s doing the same operation as manually scanning a telephone book for
everyone with a fi rst name of Tim.FIGURE 45-16
Filtering by the second key column of an index forces an index scan.c45.indd 1030c45.indd 1030 7/31/2012 10:16:43 AM7/31/2012 10:16:43 AM
http://www.it-ebooks.info