Microsoft SQL Server 2012 Bible

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 Profiler.

• 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 Profiler 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 file, 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 Query Optimizer can estimate the number of rows for each operator based on statistics and use ...