1026
Part VII: Performance Tuning and Optimization
FIGURE 45-12
With the StartDate column included in the index, the queries are solved with an index
seek — a perfect covering index.In this case the index seek operator uses the index (keyed by ProductID) to seek the rows
matching ProductID = 945.Then the index seek operator continues to select the correct rows by fi ltering the rows by
the included column (AND StartDate = '2006-01-04'). In the index seek properties
(see Figure 45-13), the predicate is fi ltering by the StartDate column.The performance difference between the bookmark lookup solution and the covering
index is dramatic. When comparing the query optimizer cost and the logical reads
(refer to Table 45-1), the query paths that use a covering index are approximately
12 times more effi cient. (The duration appears less in the fi gure due to my limited
hardware.)c45.indd 1026c45.indd 1026 7/31/2012 10:16:42 AM7/31/2012 10:16:42 AM
http://www.it-ebooks.info