Database Reference
In-Depth Information
Figure 2-9.
Ordered index scan execution plan
It is worth mentioning that the
order by
clause is not required for an ordered scan to be triggered. An ordered
scan just means that SQL Server reads the data based on the order of the index key.
SQL Server can navigate through indexes in both directions, forward and backward. However, there is one
important aspect that you must keep in mind. SQL Server does not use parallelism during backward index scans.
■
You can check scan direction by examining the
INDEX SCAN
or
INDEX SEEK
operator properties in the execution
plan. Keep in mind, however, that Management studio does not display these properties in the graphical representation of
the execution plan. You need to open the properties window to see it.
Tip
The Enterprise Edition of SQL Server has an optimization feature called
merry-go-round scan
, which allows
multiple tasks to share the same index scan. Let's assume that you have session S1, which is scanning the index.
At some point in the middle of the scan, another session, S2, runs the query that needs to scan the same index. With a
merry-go-round scan, S2 joins S1 at its current scan location. SQL Server reads each page only once, passing rows to
both sessions.
When the S1 scan reaches the end of the index, S2 starts scanning data from the beginning of the index until the
point where the S2 scan started. A merry-go-round scan is another example of why you cannot rely on the order of the
index keys, and why you should always specify an
ORDER BY
clause when it matters.
The second access method is called an
allocation order scan
. SQL Server accesses the table data through the IAM
pages similar to how it does this with heap tables. The query in Listing 2-7 and in Figure
2-10
illustrates this example.
Figure
2-11
shows the execution plan for the query.
Listing 2-7.
Allocation order scan query
select Name
from dbo.Customers with (nolock)
