996
Part VIII: Performance Tuning and Optimization
What’s New with Query Execution Plans?
There are several new execution plan features in SQL Server 2012. For me, one of the more exciting
features is the graphical warnings in the execution plan that detail potential problems with implicit
conversions and operators that can spill to tempdb.■ (^) Management Studio’s Query Editor can switch the graphical query execution plan into XML
from the context menu.
■ (^) A saved query execution plan now includes the full query, so when it’s re-opened, the original
source query can be viewed in the Query Editor.
■ (^) The XML query execution plan is formatted better. The XML plan schema has also been
greatly enhanced, which leads to many new operators in a query plan that will give additional
insight into query tuning.
■ (^) The Query Editor can now open the XML query execution plan that’s returned from the
sys.dm_exec_query_plan() dynamic management function as a graphical plan with a
single click.
Viewing Query Execution Plans
You have several options for viewing execution plans. This chapter looks at the options
for viewing them graphically and as a result set in Management Studio, as well as viewing
them via Dynamic Management Views and capturing them in SQL Server Profi ler.■ (^) Management Studio can display the estimated or actual query execution plan
graphically, through XML, or via a result set.
■ (^) The Showplan directive can return the estimated query plan as a message or result set.
■ (^) The STATISTICS PROFILE directive can return the actual query plan as a message or
result set.
■ (^) SQL Profi ler can capture the query execution plan as plain text or as XML that can
be viewed in graphic form.
■ (^) Execution plans in the plan cache may be viewed using dynamic management views
(DMVs).
In addition, many of the preceding methods enable an execution plan to be saved as an
XML fi le, which can be opened later using Management Studio.
Estimated versus Actual Execution Plans
SQL Server can return the estimated query execution plan before the query is executed, or
it can return the actual query execution plan with the results of the query.
The difference between the estimated and the actual typically isn’t the plan; the sequence
of physical operations are often the same. The difference in the estimated versus actual
plans are the number of rows returned by each operator. Before the query is executed, the
c44.indd 996c44.indd 996 7/31/2012 10:19:27 AM7/31/2012 10:19:27 AM
http://www.it-ebooks.info