Microsoft® SQL Server® 2012 Bible

1001

Chapter 44: Interpreting Query Execution Plans

44

Understanding Execution Plan Operators

SQL server uses many different operators when creating execution plans. Some represent

specifi c physical tasks, whereas most logically represent a collection of hidden tasks. Many

of these operators are related to specifi c statements types — statements that you may

rarely see. Others are much more common.

Table 44-1 lists the key operators regarding select queries and indexing.

TABLE 44 -1 Query Execution Plan Operators

Icon Defi nition Description

Clustered index

scan

In a clustered index scan, SQL Server reads the entire clus-

tered index — typically sequentially — but it can be other-

wise depending on the isolation level and the

fragmentation.

SQL Server chooses this operation when the set of rows

requested by the WHERE clause or JOIN condition is a large

percentage of rows needed from the table or no index is

available to select the range.

Table scan A table scan is similar to a clustered index scan but scans a

heap.

Clustered index

seek

In a clustered index seek, SQL Server navigates the

clustered index B-tree to retrieve specifi c rows.

The benefi t of the clustered index seek is that when the

rows are determined, all the columns are immediately

available.

Hash match A hash match is an join method that builds an in-memory

hash table and iteratively matches with data from another

table. A hash match is more effi cient if one table is signifi -

cantly larger than the other table.

Merge join The merge join is the fastest method of joining two tables

if both tables are pre-sorted.

Nested-loop A nested-loop join iterates through an outer result set one

record at a time and fi nds the matching rows in the inner

result set (often a table) for each row from the fi rst.

Typically, nested-loop joins are best suited when a large

index table is joined with a small table.

Index scan

(nonclustered)

In a nonclustered index scan, SQL Server reads through all

the index sequentially looking for the data. This is typically

must faster than a clustered index scan because the

non-clustered data structure is much smaller.

Continues

c44.indd 1001c44.indd 1001 7/31/2012 10:19:28 AM7/31/2012 10:19:28 AM

http://www.it-ebooks.info