An execution plan is a plan containing the steps SQL
Server works through in order to satisfy a query. Most stored procedures
or ad hoc Transact-SQL queries access more than one table, and most
tables have a clustered index and usually multiple non-clustered
indexes. The SQL Server Operating System has a component called the
Query Optimizer, which is responsible for selecting the optimal
execution plan to satisfy a query.
Getting
visibility of execution plans is important since it can help developers
understand several key aspects of designing a well-performing database
including the following:
Knowledge of the steps SQL
Server uses to deliver query results provides insight into how the
Query Optimizer functions and ideas about how you can influence the
choices made to help ensure data is returned in the most efficient
method possible.
When
troubleshooting a poorly performing query, an execution plan provides a
very helpful view of the cost of each step involved. In many situations,
a query plan consists of multiple steps each with different associated
costs. Performance tuning is often an iterative process of identifying
the most expense step then reducing this cost, either through altering
the query/ stored procedure or by making changes to indexes, statistics,
an underlying table, or several of these possibilities.
Compilation and Recompilation
Compilation is the process
by which the Query Optimizer determines a good plan to execute a query.
Recompilation is the process by which the Query Optimizer recalculates a
query plan because a condition affecting query execution has changed.
The first time a query
is executed, SQL Server checks the procedure cache, an area of memory
reserved for compiled query plans. If a plan is found, it will be used.
Otherwise the optimizer compiles an execution plan based on statistics
to determine the best method to satisfy a query. Once the query plan has
been compiled, it is stored in the procedure cache for re-use in the
future.
Recompilation occurs
when plans stored in the procedure cache can’t be used because of a
change since the plan was compiled. Below is a summary of common
conditions that could influence recompilation:
Changes to statistics used to generate the cached plan
Using sp_recompile or calling a stored procedure using WITH RECOMPILE
ALTER TABLE or ALTER VIEW commands affecting the query
Changes to indexes referenced by the query plan (including dropping indexes)
Large number of inserts or deletes (exceeding a threshold value)
Cost-Based Optimization
Understanding
query execution plans requires an introduction to query execution
within SQL Server. SQL Server uses a cost-based optimizer where
alternative relational database management systems use rule-based
optimization or a hybrid of rule- and cost-based optimization.
Cost-based optimization
means that a query plan is chosen based on a cost value that is placed
on each of the different query plans or access methods that could be
used to satisfy a query. Once the options have been evaluated (index
seeks or scan, table scans, hashes and sorts, etc.), the optimizer
selects and executes the execution plan with the lowest cost.
The Query Optimizer
doesn’t actually execute the query with many different plans; its
decision is based on statistics. Statistics within SQL Server are a
histogram of the data whereby values for each column are sampled at
preset intervals throughout the table. Using this method, SQL Server has
a view on data distribution without having to examine every row for
each query.
Statistics are essentially
summary data the optimizer uses to make decisions about query execution.
If statistics are missing or inaccurate, the optimizer is likely to
make a poor choice when executing queries. This can affect query
response times and overall server performance.
When troubleshooting
SQL Server performance problems, it is important to examine statistics
and query plans with a view to including or eliminating statistics
problems to reach a resolution. Using the Database Console Commands
(DBCC) family of commands, you can determine when statistics were last
updated and how many rows were sampled, as well as resample statistics
to rule out statistics from the possible causes of poorly performing
queries.
Capturing Query Execution Plans
Execution plans can be
obtained using either SQL Server Management Studio or SQL Profiler.
Depending on the nature of the problem, you’ll need to determine the
most appropriate tool. Queries with large execution plans are often best
captured with SQL Profiler, whereas validating ad hoc changes and the
impact of changes is often better suited to SQL Server Management
Studio. There are two ways to display an execution plan: either in a
graphical plan or in text format. The following sections describe each
method, as well as how to capture and interpret the plans.
Graphical Execution Plans
Graphical
execution plans provide a visual representation of the steps required
to satisfy a query. More information can be drawn from the execution
plan by moving the pointer over each step. To view a graphical execution
plan, choose one of the following from the Query menu:
Both plans look the
same or at least similar . Figure 1
shows an actual execution plan for a query that joins three tables.
Here, you can see that 61 percent of the total query execution time is
spent executing a table scan on a table named “_Individual”. Creating a
clustered index on this table would remove the table scan and improve
query completion time.
Graphical execution plans
can be cumbersome to view and navigate especially for large queries or
queries executed in multiple batches. It is often easier to navigate a
query plan in full-screen mode: just right-click on the plan, choose Save Execution Plan As,
and then give the file name the default extension.sqlplan. When you
double-click the execution plan, it opens in full-screen view making it
much easier to explore.
Text-Based Execution Plans
This actual query execution plan can also be captured using the SHOWPLAN_TEXT SET option.
|--Hash Match(Inner Join, HASH:([ca].[AddressID])=([a].[AddressID]))
|--Hash Match(Inner Join,HASH:([c].[CustomerID])=([ca].[CustomerID]))
| |--Table Scan(OBJECT:([AdventureWorks].[dbo].[__Customer] AS [c]),
WHERE:([AdventureWorks].[dbo].[__Customer].[CustomerType] as
[c].[CustomerType]=N'I'))
| |--Hash Match(Inner Join, HASH:([i].[CustomerID])=([ca].[CustomerID]))
| |--Table Scan(OBJECT:([AdventureWorks].[dbo].[__Individual] AS [i]))
| |--Table Scan(OBJECT:([AdventureWorks].[dbo].[__CustomerAddress] AS [ca]))
|--Table Scan(OBJECT:([AdventureWorks].[dbo].[__Address] AS [a]))
Interpreting Graphic Execution Plans
Reading query execution
plans requires an investigative approach and an understanding of how
they represent decisions and steps within queries. Graphic execution
plans contain icons representing operations that occur during query
execution. SQL Server Books Online contains a complete list of
operators; Table 1 lists a sample of common operators with corresponding icons and descriptions:
Table 1. Common Operators and Corresponding Icons
| Icon | Operator | Description |
|---|
 | Clustered index scan | Scans a clustered index |
 | Clustered index seek | Seeks through a clustered index to find specific row(s) |
 | Non-clustered index scan | Scans a non-clustered index |
 | Non-clustered index seek | Seeks through a non-clustered index |
 | Nested loop join | Shown when a nested loop is used |
 | Merge join | Used to perform some inner and outer joins |
 | Hash match | Represents a hash join where a computed hash table is used to match rows based on hash values |
 | RID lookup | Retrieves a row based on a row identifier (RID) (replaces bookmark lookup in SQL Server 2005) |
Reading Execution Plans
The ability to read an
execution plan takes time and practice. Use the following guidelines to
extract the most interesting elements from execution plans:
Read plans right to left and top to bottom.
Use the + symbol in the bottom-right corner to navigate large plans.
Mouse-over each step to get more information.
Mouse-over connecting lines to see details such as number of rows transferred.
Note that heavier weight connecting lines indicate more rows moved.
Where a query consists of multiple batches, investigate the most expensive batch first.
It is essential to keep in
mind the actual query. As you browse the execution plan, try to
visualize how SQL Server is collecting, joining, and reducing the data
from a very wide dataset (entire tables) to return just the results
required to satisfy the query.
Once you’re familiar
with the look and feel of execution plans, try to use the plans to draw
conclusions. A common approach to tuning poorly performing queries is to
identify the highest cost batch and then determine alternatives—such as
new indexes, statistics, or altering the query—to remove the step.
Estimated and Actual Execution Plans
Execution plans are
useful in understanding how SQL Server delivers query results. There
are two types of plans you can capture: estimated plans and actual plans. Estimated plans are captured by selecting Display Estimated Execution Plan from the Query menu. The estimated plan displays the plan the optimizer should follow if the query were executed (without actually executing the query). Actual execution plans are captured by selecting Include Actual Execution Plan from the Query menu.
Selecting actual
execution plan executes a query and captures the actual execution plan
used to deliver the query results. The output is visible in a new tab
within the query results pane: select this tab to reveal the graphic
actual execution plan. If the query uses any temporary tables, you’ll
need to use the actual execution plan because the temporary table won’t
be constructed if the code isn’t executed. If you are using table
variables instead of temporary tables, an estimated execution plan can
be used.
In most cases, an estimated
query plan is the same as an actual execution plan. However, there are
differences in some instances usually related to statistics. Use DBCC
commands to verify statistics on the objects accessed by the query and
recalculate these statistics if necessary.
