Retrieving SQL Server Query Execution Plans

Execution plans explain all you need to know about query performance, and how to fine-tune. Sure, you can see them in SSMS, but what if you need to drill into to the important details? What about using DMVs, Extended Events or SET statements to get at the execution plans? To get the best use of execution plans you need to be able to get right information from the right plan at the right time. Robert Sheldon explains how.

One of the most useful tools in SQL Server for analyzing and troubleshooting a query’s performance is the execution plan. An execution plan can tell you how the database engine intends to execute a query, as determined by the query optimizer, or how the database engine actually executed the query.

In most cases, the estimated plan and actual plan are one in the same. The optimizer generates the plan and hands the plan off to the engine, which uses it executes the query. When you view the actual plan, you’re essentially looking at the estimated plan generated by the optimizer, with a few runtime statistics added in for good measure.

The actual plan will differ from estimated plan only if something happens in between the estimating and processing operations that forces SQL Server to recompile the associated query and the optimizer to produce a completely new plan. For example, someone might modify an underlying table or recompile a stored procedure.

The execution plan breaks the execution process down into hierarchical steps that show each operation needed to access the target data or perform computations against that data. For each operation, the execution plan provides detailed information such as the operation type, output columns, number of rows, and estimated CPU and I/O usage. The details also include the estimated cost of an operation, as determined by the optimizer, relative to the rest of the query’s operations.

SQL Server provides a number of methods for accessing a query’s execution plan, ranging from clicking a button in SQL Server Management Studio (SSMS) to setting up extended events that capture execution plans based on specific criteria. In this article, we look at the various methods for accessing execution plans and provide some of the necessary basics for how to find the information they provide.

Using SSMS to access execution plans

The simplest and quickest way to access a query’s execution plan is to use SSMS to view a graphical representation of either the estimated or actual plan. To demonstrate how this works, let’s start with the following SELECT statement, which joins two tables in the AdventureWorks2014 database (installed on a local instance of SQL Server 2014):

The SELECT statement simply joins the SalesOrderHeader table with the SalesOrderDetail table and returns a range of values based on the LineTotal column in the SalesOrderDetail table. The LineTotal column is a computed column that derives its values from the UnitPrice, UnitPriceDiscount, and OrderQty columns in the same table.

To display a query’s execution plan, you must first type (or copy-and-paste) the query into a new query tab in SSMS or open a T-SQL script file that contains the query. You can also type the query into an existing tab that contains other queries, but if you do, you must highlight the query before trying to view the plan, just as you would if running a single T-SQL statement from a group of statements.

With the query in place, and selected if necessary, click the Display Estimated Execution Plan button on the SSMS toolbar to view its estimated plan, or click the Include Actual Execution Plan button to view the actual plan.

The Display Estimated Execution Plan button is a one-time event, which means you must click it each time you want to view the estimated plan for a selected query. If you select this option, the database engine generates the plan without running the query.

The Include Actual Execution Plan button is a toggle that is either on or off. If on, the database engine executes the query and generates an actual plan. SSMS will continue to return the execution plan for each executed query until you explicitly toggle the button off.

Regardless of which option you choose, SSMS displays the plan on its own results tab, separate from any query results or messages. The following figure shows the execution plan after turning the Include Actual Execution Plan option on.

%20screenshots/stExecPlans_fig01.png

In this case, the actual plan displayed is the same as the estimated plan, which indicates that the optimizer likely did not generate a new plan when it came time to processing the query. More often than not, this will be the situation you’ll run into, particularly for simple queries such as our example. That said, there will be times when they differ. For example, if a table’s statistics are not current as a result of inserting data into an IDENTITY column, SQL Server will update the statistics next time you run a query against that table and then recompile the plan.

Whether you’re working with the estimated plan or the actual plan, SSMS presents the information in a similar manner, displaying a specific icon, or operator, for each operation type. The execution plan for our sample SELECT query shows five operations, but only three different types:

  • Merge Join: Performs a type of join, such as an inner join, left outer join, or right outer join. In this case, the operation is performing an inner join on the two data sets from the SalesOrderHeader and SalesOrderDetail tables.
  • Clustered Index Scan: Scans a clustered index and returns all rows or only those rows specified in the WHERE clause. For the SalesOrderHeader table, the operation returns all rows. For the SalesOrderDetail table, the operation returns only those row with a LineTotal value between 1000 and 1500.
  • Compute Scalar: Evaluates an expression and returns a computed scalar value. In our example, both Compute Scalar operations are related to calculating the LineTotal value for each row returned by the bottom Clustered Index Scan operation.

Not surprisingly, SSMS supports numerous other icons to represent the various operation types, nearly 90 at last count. You can find a list of icons in the TechNet topic Graphical Execution Plan Icons (SQL Server Management Studio). The topic also provides links to individual descriptions of each operation.

One icon I have not mentioned and one you will not find on the TechNet list is SELECT. This is because it is not considered an operation. Rather, the icon represents the final results of the query. It sits at the top of the plan hierarchy and provides information about the query as a whole.

We’ll get into how to view more details about the SELECT icon and the other nodes shortly, but first let’s take a step back to look at the plan as a whole because this is where things can sometimes get confusing, especially if you’re new to execution plans.

When reading an execution plan, you generally move from right-to-left and from top-to-bottom, so from this perspective, the first operation is the Clustered Index Scan against the SalesOrderHeader table, the second operation is the Clustered Index Scan against the SalesOrderDetail table, the third operation is the Compute Scalar calculation on the LineTotal column, and so on. In this way, the execution plan shows the order in which operations occur. The arrows between the operators indicate the direction that the data flows, sized proportionately based on the amount of data moving from one operation to the next.

SSMS also shows the execution plan as a hierarchy, based on the execution order, with the SELECT node representing the top level of that hierarchy (as well as the query’s final output). From the SELECT node, you go from left-to-right to move down the hierarchy. So the first level down the hierarchy is the Merge Join operation that joins the data from the two data flow streams.

The next level down includes both the top Clustered Index Scan operation and the left Compute Scalar operation, both of which act as children to the Merge Join parent. The left Compute Scalar operation also serves as the parent of the right Compute Scalar operation, and the right Compute Scalar operation serves as the parent for the bottom Clustered Index Scan operation.

Consistent with the execution hierarchy, SSMS numbers the operations (referred to as the node IDs) from left-to-right, using a 0-based system:

  • The Merge Join operation is node 0.
  • The top Cluster Index Scan operation is node 1.
  • The left Compute Scalar operation is node 2.
  • The right Compute Scalar operation is node 3.
  • The bottom Cluster Index Scan operation is node 4.

Along with each operator icon, SSMS also provides the Cost percentage, which is the estimated cost of the operation compared to the other operations. If you were to add the percentages together for all the operations, they would total 100%.

The percentages provide a quick way to see which operations the optimizer thinks will eat up the bulk of your workload. For example, this execution plan indicates that the bottom Cluster Index Scan operation (node 4) has an estimated operator cost of 58%, representing a significant portion of the query’s processing. If you were troubleshooting a query, that would be a good place to start. Keep in mind, however, that these costs can sometimes be misleading. For example, if you call a user-defined function (UDF) within a query, the cost of executing the UDF is hidden in a Compute Scalar value of 0%.

You can find additional information about each operation by hovering over the icon until a pop-up window appears that displays the specific details. For example, the following figure shows the pop-up window that appears when hovering over the bottom Clustered Index Scan operation.

%20screenshots/stExecPlans_fig02.png

As you can see, we now have a significant amount of information about the operation. For example, the Physical Operation property shows the operation that the database engine carried out in performing the query, and the Logical Operation property shows the operation that the optimizer calculated as the best operation to use. Often these two will be the same, but sometimes they will differ, depending on the type of operation. For example, the Merge Join shows the Physical Operation property as Merge Join, but shows the Logical Operation property as Inner Join, a more precise description of the type of join.

The pop-up window also provides information about how the operation was processed and about the data itself. For a description of each one, see the TechNet article Displaying Graphical Execution Plans (SQL Server Management Studio).

You can view even more information about each operation by accessing the operation’s properties. To do so, right click the specific icon, and then click Properties. This opens the Properties pane, where you can dig into an assortment of information about the operation, as shown in the following figure.

%20screenshots/stExecPlans_fig04.png

You can also retrieve information about the data flow itself by hovering over the data flow paths (arrows). For example, the following figure shows the pop-up window that appears when hovering over the data flow path between the bottom Clustered Index Scan operation and the right Compute Scalar operation.

%20screenshots/stExecPlans_fig03.png

Here you can get a quick glimpse of the amount of data moving from one operation to the next and what the estimates were for those rows. In this way, you can see whether the actual row count differs significantly from the estimates. You can also see whether the number of rows is very different from what you expected, which might in itself point to a problem.

Verifying these row counts can be the biggest reason to reviewing the actual plan. If the optimizer thought 10 rows would be returned, but it was actually 100,000 rows, you have a very inefficient plan, and you need to find out why the optimizer got confused (for example, as a result bad statistics) or what other reason might have caused the disparity, such as parameter-sniffing problems.

SSMS also adjusts the sizes of the data path arrows themselves to indicate the amounts of data moving from one operation to the next. In this way, you might be able to get a quick hint that there is a problem with the related operations. For example, if the plan shows fat arrows for most of the data flow and then suddenly is passing along only a few rows, you have a way to pinpoint where there might be an issue with your query.

At the top of the execution plan, you’ll also see a partial note about a missing index. To view specifics about the index, right-click the execution plan and then click Missing Index Details. SSMS opens a new tab and displays the following information:

The message is related to the SalesOrderDetail table, which is the target of the bottom Clustered Index Scan operation. We already had a clue that there might be an issue with this operation because of the associated Cost value of 58%. However, this message takes it a step further and recommends creating a nonclustered index on the LineTotal column (something you can do for only certain computed columns).

From this information, you might decide that such an index is a good idea, or you might determine that the query is not used often enough or on large enough data sets to warrant the overhead of an extra index. For example, if the scan shows a table cardinality of 12,000 rows and the query return 7,500 rows, the extra index is probably not worth it. However, if the cardinality shows a million rows, but the query still returns only 7,500 rows, you might consider the index, depending on the frequency and importance of the query.

On the other hand, you might choose to create a covering index that is based on the SalesOrderID column but includes the LineTotal column.

When working with execution plans, you might also find it useful to review the SELECT node, the node at the top of the query hierarchy. In addition to showing the original query on which the plan is based, the SELECT node includes the following properties:

  • Cached plan size Amount of memory the plan uses is in the plan cache.
  • Degree of Parallelism Number of processors used to run the query.
  • Estimated Operator Cost Estimated optimizer cost for running this specific operator.
  • Estimated Subtree Cost Accumulated estimated optimizer cost for all steps in the query.
  • Estimated Number of Rows Estimated number of rows affected by the query.

As with any type of optimization, it depends on your particular circumstances. The execution plan is there to point out where problems might exist, and in this case actually offers a possible solution, but ultimately you must decide how best to address any issues that the execution plan exposes.Retrieving an execution plan as XML.

As handy as the graphical execution plan is for examining how a query is being executed, there might be times when you want the information in a format that you can use with other tools, easily save for later analysis, or share with other administrators and developers.

To this end, SSMS lets you view—and subsequently save—the execution plan as an XML file. To do so, right-click the execution plan and then click Show Execution Plan XML. SSMS opens a new tab and displays the XML. The following figure shows a small portion of the XML, which, although not readily apparent, conforms to the same operational hierarchy we saw in the graphical execution plan.

%20screenshots/stExecPlans_fig06.png

The XML provides all the information available about the query plan in a single document, which you can save and then analyze at your convenience, using your tools of choice. Although the XML structure is much more complex than we can go into here, you can get a good sense of how the pieces fit together by generating the XML and examining it. You can also reference the schema that the XML is written against by going to http://schemas.microsoft.com/sqlserver/2004/07/showplan.

To help you get started with the XML, there a couple items worth noting. First, the element that primarily concerns us in our example plan is <StmtSimple>, which specifies the statement text, the applicable SET options, and a few other details about the statement. Within this element, you’ll also find the <QueryPlan> element, which is where we get into the meat of the execution plan.

The <QueryPlan> element contains a series of <RelOp> elements. Each <RelOp> element represents one of the plan’s operations, adhering to the same hierarchical structure as that of the graphical execution plan. The following XML shows an abbreviated overview of the <RelOp> elements that define the plan:

Each <RelOp> element provides a complete break-down of the associated operation, including such details as the defined values, output list, estimated I/O and CPU usage, average row size, and numerous other bits of information.

From SSMS, you can save the XML to a file, just like you can any T-SQL code in SSMS, and use the file for further analysis, using whatever tools you have at your disposal.

As a simple example, let’s look at how this might work in T-SQL. Suppose we save the XML to the file execplan.xml. We can then use the OPENROWSET function to retrieve the XML from the file and insert it into a temporary table, as shown in the following example:

The table includes one column, ExecPlan, which is configured with the XML data type. We can now use the methods available to that data type to retrieve specific information from the XML. For example, the following SELECT statement uses the value method to return the statement’s estimated number of rows:

In this case, the statement returns 7532.55, but we can just as easily return other types of information. For example, we can retrieve the original query on which the plan is based:

You can, of course, dig into the XML a lot deeper than what we’ve looked at here and peel apart the individual operations to whatever degree is necessary. You can even parse multiple XML documents in order to compare execution plans from a set of queries or to compare the same query against different SQL Server instances. The XML makes it easy to persist and port execution plans across different environments, opening up a wide range of possibilities for query plan analysis.

If you want to learn more about working with the XML query plans, take a look at Dennes Torres’ article Checking the Plan Cache Warnings for a SQL Server Database.

Using SET statements to access execution plans

SQL Server also supports the ability to use SET statements to retrieve a query’s execution plan. Each statement requires that you set the specified option to ON before capturing one or more plans, and then setting the option to OFF when you’re finished.

The two options we’re most concerned with are SHOWPLAN_XML and STATISTICS XML. The SHOWPLAN_XML option returns the estimated plan as XML, without running the query. The STATISTICS XML option returns the actual plan, which means it must first run the query.

SQL Server supports several other options related to generating execution plans, such as SHOWPLAN_TEXT or STATISTICS PROFILE, but those are slated for deprecation, so we’ll give them a pass here.

Let’s start with the SHOWPLAN_XML option. To demonstrate how it works, we’ll use the same SELECT statement we used for the previous examples:

As you can see, we set the option to ON, specify the SELECT statement, and then set the option to OFF. If we did not set it to OFF, the database engine will continue to return the execution plan for each statement we run within the current session, without returning any query results.

When you run a query with the SHOWPLAN_XML option is set to ON, the database engine returns a single value that contains the plan’s XML. You can copy the value and paste it into a text editor, but when I tried it in a couple different editors, everything came over on a single line. However, you can also get at the XML in SSMS by clicking the value, which serves as a link that opens the graphical version of execution plan, just like the graphical plan we saw in our earlier examples. This means you can get the XML by right-clicking the graphical execution plan and then clicking Show Execution Plan XML.

The STATISTICS XML option works much the same way as the SHOWPLAN_XML option, except that, as already noted, the database engine also executes the query, which makes it possible to get the actual execution plan. Here’s what it looks like in action:

Once again, click the results to view the graphical execution plan and subsequently access the XML, where you can save it to a file or do anything else with it that you like.

Using DMVs to access execution plans

Another method available for getting a query’s execution plan is the sys.dm_exec_query_plan dynamic management view, which allows you to retrieve a cached execution plan or retrieve the plan for a currently running query.

To use the view to retrieve an execution plan, you must provide the plan handle, an identifier that uniquely identifies the plan. Unfortunately, this can make using the view a bit tricky. Not many of us will know the plan handle off hand, which means we’ll need to enlist one or more other views or methods to get the information we need, such as the sys.dm_exec_cached_plans, sys.dm_exec_query_stats, or sys.dm_exec_requests dynamic management views.

For example, we can use the sys.dm_exec_query_plan view in conjunction with the sys.dm_exec_cached_plans and sys.dm_exec_sql_text dynamic management views to return a list of T-SQL statements and their query plans. This involves using the CROSS APPLY operator to provide the plan handles we need to return the execution plans.

For example, suppose we run the same SELECT query we used in the previous examples and then run the following SELECT statement:

Notice that the WHERE clause limits the results to the database with the dbid value of 9, which on my system is the AdventureWorks2014 database. From the results, I can then locate the statement in the text column to come up with its associated plan.

The plan itself is presented as an XML value much like the value returned by a SET statement, as we saw above. If you click the value, it opens the graphical plan in a separate tab, and from there you can access the XML.

Not surprisingly, retrieving an execution plan in this way can get somewhat cumbersome, especially if there are a lot of cached plans. One option is to pass in the actually statement into the WHERE clause, as in the following example:

In this way, you return only a single result, just like SET SHOWPLAN_XML ON. However, you have to enter the T-SQL statement exactly how it is cached. For example, if you were to run the statement without including the trailing semi-colon, the outer SELECT statement would return no results.

As you can see, the dynamic management view approach can get fairly cumbersome and is not particularly efficient. But it some cases, it might be your best option.

Using extended events to access execution plans

As an alternative to the other options we’ve discussed, you can use SQL Server Extended Events to define events that capture execution plans. At one time, Microsoft recommended that you use SQL Server Profiler to capture execution plans, but Microsoft has since announced the deprecation of SQL Server Profiler for Trace Capture, and in its place, recommends that you use Extended Events.

To use Extended Events to capture execution plans, you must first create a session that specifies exactly the type of plans you want to create. You can use the Extended Event GUI features in SSMS to create the session, or you can use a CREATE EVENT SESSION statement. In either case, you should specify the scope of the session and how to capture the execution plans.

Let’s look at a simple example of how to use a CREATE EVENT SESSION statement. (Be aware that there is a high overhead associated with this running this event, so proceed cautiously.) In this session, we’ll specify the query_post_execution_showplan event, which will capture the actual execution plan for each query executed against the AdventureWorks2014 database (with the ID of 9):

The statement also specifies that the database name should be included in the results and that those results should be saved to the ExecPlan.xel file. There are, of course, numerous other options we can specify along with what we have here, such as capturing execution plans only if the query exceeds a certain amount of time. For a complete run-down of the CREATE EVENT SESSION statement, see the MSDN topic CREATE EVENT SESSION (Transact-SQL).

You can also use the SSMS interface to create the session. To get started, expand the Management tab in Object Explorer, and then expand the Extended Events node. Next, right-click the Sessions folder, and then click New Session or New Session Wizard to begin defining your session.

After you create the session, you can use an ALTER EVENT SESSION statement to turn the session on, run a statement, and then use an ALTER EVENT SESSION statement turn the session off. The reason you want to explicitly turn off the session is that monitoring events in this way can be resource-intensive operations, so you want to use this process only when needed.

The following T-SQL shows how you can use the Extended Events session to capture our query’s execution plan:

When you turn on the session, SQL Server captures the event data to the specified file, adding a suffix to the file name to support generating multiple files as you turn the session on and off.

After the file has been created, drag it to SSMS and click the applicable row in the upper pane to display details about the captured event in the lower pane, as shown in the following figure. In this case, there is only one row in the upper pane.

%20screenshots/stExecPlans_fig20.png

In the bottom pane, you have two tabs. The Details tab includes various types of information about the query, and the Query Plan tab shows the graphical version of the plan, similar to what we saw in earlier examples, except that it does not include the top-level SELECT node.

You can access the plan’s XML from either tab. Accessing the XML from the Query Plan tab works just like we’ve seen elsewhere. To access the XML from the Details tab, double-click the showplan_xml value. This opens the XML in a separate tab.

Mastering the execution plan

Microsoft provides plenty of options for getting at a query’s estimated or actual execution plan. Regardless of the approach you take, you can view a graphical representation of the plan in SSMS or view the plan’s XML in your tool of choice.

Not surprisingly, there is much more to understanding and analyzing an execution plan than what we’ve covered here, but getting that plan is always the first step, whether performed by you or someone else.

The best way to learn about execution plans is to try out the various methods described here to generate plans for different types of queries and then playing around with the results. The more comfortable you are working with execution plans, the better you can analyze and fine-turn your queries, a topic we’ll save for a separate discussion.

Further Reading

  1. Execution Plan Basics
  2. Why Developers Need to Understand Execution Plans
  3. Graphical Execution Plans for Simple SQL Queries
  4. The SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store
  5. Simple Query tuning with STATISTICS IO and Execution plans
  6. Controlling Execution Plans with Hints
  7. Exploring Query Plans in SQL
  8. DMVs for Query Plan Metadata