Databases Reference
In-Depth Information
Table 1-1 shows the different statements you can use to obtain an estimated or actual
execution plan in text, graphic, or XML format. Note that, when you run any of these
statements using the ON clause, it will apply to all subsequent statements until the
option is manually set to OFF again.
Estimated Execution Plan
Actual Execution Plan
Text
Plan
SET SHOWPLAN_TEXT ON
SET STATISTICS PROFILE ON
SET SHOWPLAN_ALL ON
Graphic
Plan
Management Studio
Management Studio
XML
Plan
SET SHOWPLAN_XML ON
SET STATISTICS XML ON
Table 1-1:
Statements for displaying query plans.
As you can see in Table 1-1, there are two commands to get estimated text plans;
SET
SHOWPLAN_TEXT
and
SET SHOWPLAN_ALL
. Both statements show the estimated
execution plan, but
SET SHOWPLAN_ALL
also shows some additional information,
including the estimated number of rows, estimated CPU cost, estimated I/O cost, and
estimated operator cost. However, recent versions of Books Online, including that of
SQL Server 2008 R2, indicate that all text versions of execution plans will be deprecated
in a future version of SQL Server.
