1021
Chapter 45: Indexing Strategies
45
There’s no index with a key column of StartDate. This means that the query optimizer
can’t choose an index to satisfy the query and must resort to scanning the entire table and
then manually searching for rows that match the where clause. Without an index, this
query path is 23 times slower than the clustered index seek query path.The cost isn’t the fi lter operation alone. (It’s only 7 percent of the total query cost.) The real
cost is having to scan in every row and pass 72,591 rows to the fi lter operation, as shown in
the query execution plan in Figure 45-9.FIGURE 45-9
Query Path 4 (fi lter by nonkey column) passes every row from an index scan to a fi lter
operation to manually select the rows.Management Studio suggests a missing index could potentially help this query execute
faster. Management Studio can even generate the code to create the missing index using
the context menu.Use care when considering implementing these suggested indexes. These index suggestions are suited specifi cally
for the query being investigated and often proves to not be the best index for your overall indexing strategy. Too often
the missing index is not the best index, and it often wants to build a nonclustered index that includes every column.c45.indd 1021c45.indd 1021 7/31/2012 10:16:40 AM7/31/2012 10:16:40 AM
http://www.it-ebooks.info