Database Reference
In-Depth Information
Table 25-1. Commands that generate actual and estimated execution plans
Plan Type
Command / Menu Item
Execute a query
Include Estimated Row
and Execution Count
Include Actual Row
and Execution Count
Graphical
Display Estimated Execution Plan
No
Yes
No
Include Actual Execution Plan
Yes
Yes
Yes
Text
SET SHOWPLAN_TEXT ON
No
No
No
SET SHOWPLAN_ALL ON
No
Yes
No
SET STATISTICS PROFILE ON
Yes
Yes
Yes
XML
SET SHOWPLAN_XML ON
No
Yes
No
SET STATISTICS PROFILE XML
Yes
Yes
Yes
One of the key metrics to which you should pay attention when analyzing execution plans is the
difference between actual and estimated row counts. a large discrepancy between these two values is often a sign of
cardinality estimation errors due to inaccurate statistics.
Important
Operators
SQL Server uses two types of operators: logical and physical . These operators are used during the different stages of
the Query Life Cycle in different types of query trees. SQL Server uses logical operators during the parsing and binding
stages and replaces them with physical operators during optimization. For example, an inner join logical operator can
be replaced with one of three physical join operators in the execution plan.
It is impossible to cover all physical operators in this chapter, however we will discuss a few common ones that
you will often encounter in execution plans.
Joins
There are multiple variations of physical join operators in SQL Server, which dictate how join predicates are matched
and what is included in the resulting row. However, in terms of algorithms, there are just three join types: nested loop ,
merge, and hash joins.
Nested Loop Join
A nested loop join is simplest join algorithm. As with any join type, it accepts two inputs, which are called outer and
inner tables. The algorithm for an inner nested loop join is shown in Listing 25-6, and the algorithm for an outer
nested loop join is shown in Listing 25-7.
Listing 25-6. Inner nested loop join algorithm
for each row R1 in outer table
for each row R2 in inner table
if R1 joins with R2
return join (R1, R2)
 
 
Search WWH ::




Custom Search