SQL Server 2012 : Interpreting Query Execution Plans - Viewing Query Execution Plans

10/1/2013 3:13:55 AM

You have several options for viewing execution plans. 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 that estimate in determining the plan.

This is a very important concept when it comes to performance tuning. The Query Optimizer relies heavily on statistics, and if the statistics are skewed, then a non-optimal plan may be chosen.

After the plan is executed, the query processor adds to the plan the actual number of rows processed by each operation.

The estimated query execution plan may be viewed by selecting the query in the Query Editor and either clicking the Display Estimated Execution Plan button on the toolbar, selecting Query ? Display Estimated Execution Plan. The actual plan may be viewed in a similar fashion. To enable viewing the actual execution plan, choose Query ? Include Actual Execution Plan.

Because the query isn't actually executed when viewing the estimated execution plan, the resulting plan should display in the Execution Plan tab rather quickly.

Reading the Execution Plan

The data flow of an execution plan is typically from right to left, top to bottom, as shown in Figure 1. Each operator is presented as an icon, otherwise known as a plan operator or iterator. The graphical execution plan is an interactive display, which enables you to hover the mouse over the operators and connections to discover the following:

Figure 1 Execution plans show the operators SQL Server uses to satisfy a query.

  • Mousing over the plan operators causes a dialog box to appear containing detailed information about the operator, including an operator description, different cost figures, number of estimated or actual rows involved, and the portion of the query handled by the operation.
  • Mousing over a connector line presents detailed information about how much data is transferred between operators.
  • The Property window also presents detailed information about any operator or connection between operators.

The display may be zoomed or sized to fit using the right-click context menu.

To walk through the query execution plan shown in the following Figure 1, follow these steps:

1. In the upper-right corner of the plan, the index seek operation finds every row with ProductID = 757 using an index seek operation against the WorkOrder.IX_WorkOrder_ProductID nonclustered index.
2. The nested-loop operation receives every row from the index seek and asks for those same rows from the clustered index, calling the key lookup operation. You can ignore the compute scalar operation because it handles only a type conversion.
3. The nested-loop assembles, or joins, the data from the index seek and key lookup and passes the data to the select operation, which returns the correct columns to the client.

Key pieces of information on the query plan follow:

  • The type of operation. Key operations are listed later in Table 1.
  • The object, listed below the operator and in the pop-up information box, is the actual index hit by the operation.
  • The estimated number of rows, because the query optimizer uses the estimated number of rows to choose the best query execution plan.
  • The estimated operator cost and the estimated subtree cost are relative values used by the query optimizer. When tuning a query, these are critical values to watch. You can read the cost as cost times 1,000 — For example, .0051234 as 5, or .3255786 as 325, to make it easier to think through the plans.

You can also save plans to a plan file (.sqlplan) to be reexamined later. Re-opening a plan opens the graphical execution plan. The context menu has a new option to edit the SQL query, which opens the original SQL statement in a new Query Editor tab.


In addition to the graphical execution plan, the Showplan and STATISTICS PROFILE directives reveal the execution plan with some additional detail. Similar to how you can view Estimated and Actual Execution plans graphically in Management Studio, these directives enable you to view the execution plans in different formats. Showplan is the estimated plan, whereas STATISTICS PROFILE is the actual plan.

Set Showplan must be the only statement in the batch.

Set Showplan comes is three flavors: all, text, and XML:

  • Showplan_all displays the operators as a result set. It exposes the same information as the graphical execution plan. The executing statement is returned in the first row, and every operator is returned as subsequent rows. (This is a deprecated feature and will be eliminated in a future version.)
  • Showplan_text is similar to showplan_all except that the executing statement and the operations are in separate result sets and only the stmt text (first column) displays.
  • The showplan_text option, along with the set statistics options, may also be toggled graphically within Query Editor. Use the context menu's Query Options command to open the Query Properties, and you can find the Showplan options by selecting Execution ? Advanced.
  • Showplan_xml displays more detail than any other method of viewing the execution plan, and it offers the benefit of storing and displaying unstructured data, so it can display additional information that may not pertain to all execution plans. For example, in the <Statement> element, Showplan_xml displays the Query Optimizer optimization level, or the reason why the Query Optimizer returned this execution plan.
  • For the XML version of Showplan, the Include Actual Execution Query Editor option must be off. In addition, if the query results are set to grid, then the grid offers a link to open the XML using the browser.

SQL Profiler's Execution Plans

Within the Performance event category, SQL Server Profiler includes several Showplan events. The Showplan XML event includes the XML for the query execution plan, which SQL Profiler displays in a graphical form. It includes the same features as the Query Editor to mouse over the operation to see more properties and zoom the display.

You can save the plan with SQL Profiler, but it's well hidden: If you right-click anywhere in the upper pane on the line of a Showplan XML or Showplan XML Statistics Profile event, you can choose to Extract Event Data. This enables you to save the plan as a .sqlplan file. Cool add!

Examining Plans using Dynamic Management Views (DMVs) previously introduced with SQL Server 2005, provide an excellent window into SQL Server's internals. Three of the DMVs expose the query execution plans currently in the cache:

  • sys.dm_exec_cached_plans: Returns the plan type, memory size, and usecounts.
  • sys.dm_exec_query_stats: Returns several aggregate execution statistics (for example, last_execution_time, max_elapsed_time).
  • sys.dm_exec_requests: Returns plans that are currently executing.
  • sys.dm_exec_procedure_stats: Returns aggregated execution statistics for stored procedures.

Each of these previous DMVs returns a plan handle (binary identifier of the query execution plan in memory) that can be passed to one of the following dynamic management functions with a cross apply to extract the query text or the query execution plan:

  • sys.dm_exec_query_plan(plan_handle): Returns the query execution plan in XML. For some complex queries, if the XML nesting level is greater than 128, this method of extracting the query plan fails. Use the next method instead.
  • sys.dm_exec_text_query_plan(plan_handle): Returns the query execution plan as a text Showplan.
  • sys.dm_exec_sql_text(plan_handle): Returns the query SQL statement.

The code example in Figure 2 pulls together data from the DMVs to view the original SQL statements and query execution plans from the cache.

Figure 2 Using DMVs, you can view the SQL code and the query execution plan in the procedure cache. Clicking the XML in the right-most column would open another tab with the graphical view of the selected query execution plan.


  •  SQL Server 2012 : SQL Server Management and Development Tools - Using the Query Editor
  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 2)
  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 1)
  •  SQL Server 2012 : SQL Server Management and Development Tools - Registered Servers
  •  SQL Server 2012 : SQL Server Management and Development Tools - Organizing the Interface
  •  SQL Server 2012 : SQL Server Private Cloud - Upgrading SQL Server
  •  SQL Server 2012 : SQL Server Private Cloud - Discovering SQL Server Sprawl
  •  SQL Server 2012 : Storage Systems (part 7) - Measuring Performance - Storage Performance Testing
  •  SQL Server 2012 : Storage Systems (part 6) - Measuring Performance - Sequential Disk Access, File Layout, Flash Storage
  •  SQL Server 2012 : Storage Systems (part 5) - Measuring Performance - Storage Performance Counters, Disk Drive Performance
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone