Milena Petrovic

SQL Server query execution plans – Viewing the plans

March 15, 2014 by
In the SQL Server query execution plans – Basics, we described the query execution plans in SQL Server and why they are important for performance analysis. In this article, we will focus on the methods for opening the plans, both actual and estimated ones

If you look at the Query item in the SQL Server Management Studio menu, you’ll see two options related to query plans – Display Estimated Execution plan and Include Actual Execution plan

An estimated execution plan is a SQL Server query plan that is generated without actually running the query (or stored procedure) the plan is created for. It’s based on estimation of expected behavior. It’s useful for analyzing how a query would behave, without actually running it. This is very useful for testing purposes in environments where performance should not be affected by running actual code (e.g. running a SELECT statement with complex joins against huge tables) , or when running code is not possible due to data changes it makes (e.g. executing an UPDATE). Its downside is that it might be inaccurate in some scenarios

An actual execution plan is the SQL Server query plan that is generated after a query was executed. It’s more reliable, as it’s based on the actual execution, not estimates. It also provides more information and statistics, therefore it’s much useful in troubleshooting

There are several methods available for opening a query plan in SQL Server

Include Actual Execution Plan

The Include Actual Execution Plan option is available in the in SQL Server Management Studio menu

  1. Select Query in the SQL Server Management Studio menu
  2. Select the Include Actual Execution Plan option, or press Ctrl+M on the keyboard

Now, every time you execute an ad hoc query or stored procedure, an additional tab will appear in the results pane, next to the Results and Messages tab

Dialog showing an additional Execution plan tab appearing in the results pane

If multiple SQL queries are executed, their plans will be listed in the same tab, one below another. Each item in the query plan shows a tooltip with additional information

Dialog showing a tooltip with additional information for each item in the query plan

SQL Server execution plans can be saved as XML or sqlplan files for later analysis. To open a sqlplan file, double-click it in file explorer and it will be automatically opened in SQL Server Management Studio

The steps are similar for using the Display Estimated Execution Plan option, except the query doesn’t have to be executed

SQL Server query plans can also be shown in Query Editor using some of the following options:

SHOWPLAN_XML

The SHOWPLAN option has to be set using T-SQL and it shows the estimated execution plan. This is the same plan as shown when the Display Estimated Execution Plan option is selected, when the query is not actually executed

  1. Execute

    Note that this is the only statement in the batch that can be executed

  2. Execute a query. The Results tab will show a link to the query plan. Note that the query results are not shown, as the query is not really executed

    Dialog showing the Results tab with a link to the query plan

  3. Click the link is the grid

    A new query tab will be opened showing the query plan

    Dialog showing a new query tab with the query plan in it

  4. To stop the query plan from showing, run

Use the query cache

As mentioned in the SQL Server query execution plans – Basics article, query plans in SQL Server are saved in the query plan cache, so they can be reused later in order to execute queries faster. One of the options to see query plans is to query the content of the plan cache using Dynamic Management Views (DMVs)

The sys.dm_exec_cached_plans view shows one row for every query plan stored in the plan cache. The view shows query text, memory used, and how many times the plan was reused

The sys.dm_exec_sql_text view shows the SQL batch text, identified by sql_handle

To see the plans for ad hoc queries cached in the plan cache:

Dialog showing one row for every query plan stored in the plan cache

To open a plan, click the link in the query_plan results column and the plan will be shown in the new Query window

Use the STATISTICS and SHOWPLAN options

The STATISTICS XML option shows the same query plan as shown when the Include Actual Execution Plan option is selected. Unlike with the SHOWPLAN options that don’t actually execute the queries, the STATISTICS options execute it and show the results

Note that besides the link to the query plan, the query results are also shown

Dialog showing the Results tab using the STATISTICS XML option

To turn the option off, execute:

Other useful options are:

SHOWPLAN_XML – doesn’t execute the query, so no results are shown. Shows the link the same as the STATISTICS XML option

SHOWPLAN_TEXT – doesn’t execute the query, shows the text of the estimated query plan

SHOWPLAN_TEXT results – shows the text of the estimated query plan

SHOWPLAN_ALL – doesn’t execute the query, shows the text of the estimated query plan along with the cost estimation

SHOWPLAN_ALL results - shows the text of the estimated query plan along with the cost estimation

STATISTICS PROFILE – executes the query, shows the results and text of the actual query plan

STATISTICS PROFILE results – shows the results and text of the actual query plan

Use SQL Server Profiler

A query execution plan can also be captured in a SQL Server trace and opened in SQL Server Profiler

  1. Start SQL Server Profiler
  2. In the File menu, select New Trace
  3. In the Events Section tab, check Show all events
  4. Expand the Performance node
  5. Select Showplan XML

    Selecting the Showplan XML using SQL Server Profiler

  6. Execute the query you want to see the query plan for
  7. Stop the trace. This is recommended due to practical reasons – in busy databases, it’s difficult to filter by the event you want to trace
  8. Select the query plan in the grid

    The SQL Server query plan is shown in the lower pane. It’s the same plan as shown when the Include Actual Execution Plan option is selected. You can see its details in the tooltip that appears on mouse over or save the whole trace as an XML file for later analysis

    Dialog showing details for the SQL Server query plan in the tooltip that appears on mouse over

This method is not recommended due to several downsides. SQL Server Profiler adds some overhead that affects query performance. Another reason is that filtering the events and finding the specific one among thousands of records in not easy in SQL Server Profiler

In this article, we showed how to open a SQL Server query execution plan using various methods. In the next article, we will show how to read the plans, what the objects presented with icons represent, and how to use these plans in performance analysis and troubleshooting

Resources:
Display an Actual Execution Plan
Graphical Execution Plan Icons (SQL Server Management Studio)
sys.dm_exec_query_stats (Transact-SQL)

See more

To learn more about query execution plans and how to use them to troubleshoot slow running queries, see How to identify and troubleshoot slow-running queries in SQL Server

Milena Petrovic

Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University.

She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.

Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.

View all posts by Milena "Millie" Petrovic
Milena Petrovic
56,558 Views