Database Reference
In-Depth Information
Execution Plans
Once you have identified a costly query, you need to find out
why
it is so costly. You can identify the costly procedure
from Extended Events or sys.dm_exec_procedure_stats, rerun it in Management Studio, and look at the execution
plan used by the query optimizer. An execution plan shows the processing strategy (including multiple intermediate
steps) used by the query optimizer to execute a query.
To create an execution plan, the query optimizer evaluates various permutations of indexes and join strategies.
Because of the possibility of a large number of potential plans, this optimization process may take a long time
to generate the most cost-effective execution plan. To prevent the overoptimization of an execution plan, the
optimization process is broken into multiple phases. Each phase is a set of transformation rules that evaluate various
permutations of indexes and join strategies ultimately attempting to find a good enough plan, not a perfect plan.
It's that difference between good enough and perfect that can lead to poor performance because of inadequately
optimized execution plans. The query optimizer will attempt only a limited number of optimizations before it simply
goes with the least costly plan it has currently.
After going through a phase, the query optimizer examines the estimated cost of the resulting plan. If the
query optimizer determines that the plan is cheap enough, it will use the plan without going through the remaining
optimization phases. However, if the plan is not cheap enough, the optimizer will go through the next optimization
phase. I will cover execution plan generation in more depth in Chapter 9.
SQL Server displays a query execution plan in various forms and from two different types. The most commonly
used forms in SQL Server 2012 are the graphical execution plan and the XML execution plan. Actually, the graphical
execution plan is simply an XML execution plan parsed for the screen. The two types of execution plan are the
estimated plan and the actual plan. The
estimated
plan represents the results coming from the query optimizer, and
the
actual
plan is that same plan plus some runtime metrics. The beauty of the estimated plan is that it doesn't require
the query to be executed. The plans generated by these types can differ, but only if a statement-level recompile occurs
during execution. Most of the time the two types of plans will be the same. The primary difference is the inclusion of
some execution statistics in the actual plan that are not present in the estimated plan.
The graphical execution plan uses icons to represent the processing strategy of a query. To obtain a graphical
estimated execution plan, select Query
->
Display Estimated Execution Plan. An XML execution plan contains the
same data available through the graphical plan but in a more programmatically accessible format. Further, with the
XQuery capabilities of SQL Server, XML execution plans can be queried as if they were tables. An XML execution plan
is produced by the statement
SET SHOWPLAN_XML
for an estimated plan and by the statement
SET STATISTICS XML
for
the actual execution plan. You can also right-click a graphical execution plan and select Showplan XML. You can also
pull plans directly out of the plan cache using a DMO, sys.dm_exec_query_plan. The plans stored in cache have no
runtime information, so they are technically estimated plans.
■
you should make sure your database is set to Compatibility mode 120 so that it accurately reflects updates to
SQl Server 2014.
Note
You can obtain the estimated XML execution plan for the costliest query identified previously using the
SET SHOWPLAN_XML
command as follows:
USE AdventureWorks2012;
GO
SET SHOWPLAN_XML ON;
GO
