Microsoft® SQL Server® 2012 Bible

1020

Part VII: Performance Tuning and Optimization

At fi rst glance it would seem that this query should generate the same query execution

plan as the fi rst query path (select * from table), but just like the narrow range

query, the between operator needs a consecutive range of rows, and this causes the query

optimizer to select index seek to return ordered rows.

There’s no guarantee that another row might be added after the query plan is generated

and before it’s executed. Therefore, for range queries, an index seek is the fastest possible

way to ensure that only the correct rows are selected.

Index seeks and index scans both perform well when returning large sets of data. The

minor difference between the two queries’ durations listed in the performance chart (refer

to Table 45-1) is more likely variances in my computer’s performance. There were some iter-

ations of the index seek that performed faster than some iterations of the index scan.

FIGURE 45-8

An index seek operation has the option of seeking to fi nd the fi rst row, and then sequentially

scanning on a block of data.

Clustered Index

PK_WorkOrder_WorkOrderID

Seek

Scan

Query Path 4 — Filter by Nonkey Column

In the previous query paths, the clustered index key was used in the query predicate to

fi nd the rows. Because the query predicate matched the clustered index key column, all

the data was available using a simple clustered key. But what if that isn’t the case?

Consider this query:

SELECT *

FROM Production.WorkOrder

WHERE StartDate = '07/15/2007'

c45.indd 1020c45.indd 1020 7/31/2012 10:16:40 AM7/31/2012 10:16:40 AM

http://www.it-ebooks.info