SQL Server Execution Plans

To be able to execute queries, the SQL Server Database Engine must analyze the statement to determine the most efficient way to access the required data. This analysis is handled by a component called the Query Optimizer. The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan, or execution plan.

A query execution plan is a definition of the following:

  • The sequence in which the source tables are accessed.
    Typically, there are many sequences in which the database server can access the base tables to build the result set. For example, if a SELECT statement references three tables, the database server could first access TableA, use the data from TableA to extract matching rows from TableB, and then use the data from TableB to extract data from TableC. The other sequences in which the database server could access the tables are:
    TableC, TableB, TableA, or
    TableB, TableA, TableC, or
    TableB, TableC, TableA, or
    TableC, TableA, TableB
  • The methods used to extract data from each table.
    Generally, there are different methods for accessing the data in each table. If only a few rows with specific key values are required, the database server can use an index. If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. If all the rows in a table are required but there is an index whose key columns are in an ORDER BY, performing an index scan instead of a table scan may save a separate sort of the result set. If a table is very small, table scans may be the most efficient method for almost all access to the table.
  • The methods used to compute calculations, and how to filter, aggregate, and sort data from each table.
    As data is accessed from tables, there are different methods to perform calculations over data such as computing scalar values, and to aggregate and sort data as defined in the query text, for example when using a GROUP BY or ORDER BY clause, and how to filter data, for example when using a WHERE or HAVING clause.

How to read SQL Server graphical query execution plans?

This is a very simple SELECT statement against the Northwind sample database, with a filter and sort operation thrown in for good measure.

SELECT [CustomerID], [CompanyName], [City], [Region]
FROM [Northwind].[dbo].[Customers] 
WHERE [Country] = 'Germany'
ORDER BY [CompanyName]

In the above estimated execution plan, I have added numbers 1 through 5 to help better explain each operation.

Let’s look at the tooltips for each of the operations in this simple execution plan, working our way from right-to-left just as you would read the plan itself.  You will see similarities between the tooltips for each operation, but you should note that there are quite a few differences as well.  You also may be surprised to learn that even the arrows denoting data flow between the operator icons have associated tooltips.

So, if you hover over each item in the execution plan you will get different information.  The following shows each set of information for these 5 parts of this execution plan.

1 – Clustered Index Scan Operator

I want to take some time here to review each of the metrics offered in this, the first tooltip we’ve looked at so far.  From here we will only focus on the new / different line items in subsequent tooltips for each operation.  You’ll see that the tooltip presents the type of operation as well as a standardized description of the operation initially.  This is followed by (in the case of estimated execution plans) estimated operational metrics.  If this was an actual execution plan you would have also seen the Actual Number of Rows involved in the operation after the Physical Operation and Logical Operation metrics.

  • Physical Operation – the physical operation for this part of the execution plan, such as joins, seeks, scans…
  • Logical Operation – the logical operation for this part of the execution plan
  • Estimated I/O Cost – these are relative values used for presenting whether a given operation is I/O intensive.  The Query Optimizer assigns these values during parsing and they serve only as a comparative tool to aid in determining where the costs of a given operation lie.  The larger the value, the more cost-intensive the process.
  • Estimated CPU Cost – these are relative values used for presenting whether a given operation is CPU intensive.  The Query Optimizer assigns these values during parsing and they serve only as a comparative tool to aid in determining where the costs of a given operation lie.  The larger the value, the more cost-intensive the process.
  • Estimated Operator Cost – This is the summation of the I/O and CPU estimated costs.  This is the value that is also presented under each operation icon in the graphical execution plan.
  • Estimated Subtree Cost – This is the total of this operation’s cost as well as all other operations that preceded it in the query to this point.
  • Estimated Number of Rows – This value is derived based upon the statistics available to the Query Optimizer at the time the execution plan is drafted.  The more current (and the larger the sampling size of the statistics) the more accurate this metric and others will be when compared to the actual data.
  • Estimated Row Size – Also based upon the statistics available at the time of parsing, this value corresponds to how wide the Query Optimizer believes the affected rows to be.  The same rule applies to statistics here as well as with the Estimated Number of Rows – the more current and descriptive the data set used to generate the statistics – the more accurate this value will be in comparison to the actual data.  Good stats also lead to better (more accurate) decisions made by the Query Optimizer when actually processing your queries.
  • Ordered – is a Boolean value signifying whether the rows are ordered in the operation.
  • NodeID – Is the ordinal value associated with this particular operation in the query execution plan.  Oh, and thank you for the confusion Microsoft for numbering the operations in a left-to-right fashion, even though we are to read the execution plans right-to-left.  Greatly appreciated!

Other items available in actual execution plans

  • Actual Number of Rows – The actual number of rows if the query was run.
  • Actual Rebinds and Actual Rewinds – The topic of rebinds and rewinds is outside the scope of this tip.  Suffice to say the value is incremented in different manners when a given operation process is initialized.  A change in correlated parameters within a join would be noted as a rebind – the join would need to be re-evaluated, whereas a rewind count is incremented if these parameters are not changed and the existing join can be used for subsequent processing.  What compounds the complexity of this subtopic is that not all operations present rebinds and rewinds to the Query Optimizer. 

These line items are then followed by the Predicate, Object, and Output List of columns for the operator.  Predicate is the term used to describe the portion of a query used to filter, describe, or compare sets of data.  In this case it the portion of the query that filters the results for just those rows where the country of interest is equivalent to ‘Germany’.  The object used as a tool to accomplish this task for this specific operator was the primary key on the Customers table.  The output list is just that, the list of columns that are output from the process being described:  CustomerID, CompanyName, City, and Region (the columns that are to be returned in our SELECT statement.

2- Data Flow Arrow: Clustered Index Scan Operator to Sort Operator

Can you tell by just this tooltip whether we’re reviewing an actual or an estimated graphical execution plan?  It’s not as simple as you would expect.  Both types of graphical execution plans provide these rows in their tooltip.  However, the actual query plan also includes the Actual Number of Rows:

The tooltips associated with the data flow arrows are quite simple, they provide information relating to the estimated (or actual) data moving between operations in a query.

3 -Sort Operator

The tooltip for the Sort operator and Clustered Index Scan operator are identical in the metrics they capture.  Obviously the values for these metrics differ.  You’ll see that the Estimated Subtree Cost has now been incremented by the cost of this operation as well as all preceding operations.

4 – Data Flow Arrow: Sort Operator to SELECT Operator

Identical in content (but not necessarily values) we next encounter the data flow arrow between the Sort and SELECT operations.  We’re almost done!

5- SELECT Operator

Take note that the SELECT operator’s tooltip is drastically different from the other operator tooltips we’ve seen so far – the DML (Data Modification Language actions such as INSERT UPDATE DELETE) will also differ compared to the other operators we’ve seen to date as well as the SELECT operator presented here.  We will look at the other DML operator’s tooltips as they come into the discussion in future tips.  In the case of the SELECT operator we have a new line item: Cached plan size.  Cached plan size denotes the amount of memory this query plan is consuming in the stored procedure cache.  This value is useful if you are troubleshooting memory issues specifically-related to cache performance.

There you have it.  Our initial dive into reading the wealth of information presented in the tooltips for the graphical query execution plans in Microsoft SQL Server.  These tooltips provide us with substantially more information than the graphic portion of the execution plan.  From costing information to amount of actual data affected at each step of the process, the tooltips give us a greater insight into why and how the Query Optimizer responds to our T/SQL code.  Our next tip in the series will focus on the Properties window available to us in SQL Server Management Studio as it pertains to execution plans. 

Maximize View of SQL Server Query Plans with Full Screen Option

A feature of SSMS is the ability to save the graphical query plan for future use.  Whether you display the actual or estimated query plan you have the ability to save the plan to a *.sqlplan file

To save the query plan, right click in the Execution Plan tab and select “Save Execution Plan As…” or choose this option from the File menu list.

Below is an example of an estimated query plan for the stored procedure “HumanResources.uspUpdateEmployeePersonalInfo” found in the AdventureWorks database.  If we select Ctrl+L or use the menu Query > Display Estimated Execution Plan we can see the estimated query plan as shown below.  This stored procedure actually has 4 different queries displayed. In this view it is hard to get a good idea of what is going on.  You can drag the sections within in each query to get a bigger view, but it still is quite cumbersome.  So to make viewing a bit easier we going to save the plan for Query 2, so we can see this information easier. 

At this point right click anywhere within the Query 2 plan and select “Save Execution Plan As…” and save the file.

One thing to note is that depending on what you are looking at (one statement or a stored procedure with multiple statements), there may be one or more execution plans, so you will need to save each one of these individually to a separate query plan file. The “Save Execution Plan As…” option does not save all of the plans in one step.

Once you save the file you can then open the file to view the execution plan using SQL Server Management Studio and also have additional real estate on your screen to look at the query plan.  If we open up our saved query plan you should get a screen such as the following.  In this view we can now see just the one query and we also get a lot more space to maneuver through the query plan.  In addition, all of the features that exist when you initially look at the query plan still exist in this saved version, such as the pop-up window when hovering over an object and also the properties information for each object in the plan.

To get even more real estate on your screen you can use the Full Screen mode.  To do this use the following or the menu View > Full Screen.

  • Shift + Alt +Enter

This will remove all of the toolbars and some of the menu items on the screen to give you the most real estate possible when viewing query plans or just about anything with Management Studio.

Execution Plans in SQL Server Management Studio

The graphical execution plan is a tool that presents in images and text, the process by which SQL Server steps through the query, parsing, hashing, and building the results set using the information it has available (statistics, indexes, and the raw data).  There are actually two other flavors of execution plans that we will not be discussing at this time: text execution plan and an xml execution plan.  We will save those for another tip as it is necessary to have a good understanding of  the graphical execution plan before moving on to those others that afford more detail. 

There are two types of graphical execution plans:  the estimated execution plan and the actual execution plan.  They are precisely what the names imply (yes, a rarity in technology).  The estimated execution plan is an estimate based upon the query optimizer on what it expects to occur when executing the query whereas the actual execution plan show what did actually occur when generating the results.  For the purpose of this tip I’ll be showing you how to run either graphical execution plan, but once that is presented the discussion will turn towards reading the basic graphical execution plan and will be agnostic as to whether it is the actual or estimated plan. 

Presenting An Execution Plan

For the purpose of this tip we will be using the Northwind database.  I’ve established a connection to my test instance and have opened a new query to the Northwind database.  The query is below and we will use this throughout this tip:

SELECT [CustomerID], [CompanyName], [City], [Region]

FROM [Northwind].[dbo].[Customers]

WHERE [Country] = ‘Germany’

ORDER BY [CompanyName]

The estimated execution plan is engaged from the standard toolbar in SQL Server Management Studio as is highlighted below:

You can also use the Ctrl+L hotkey, right click the query window and select ‘Display Estimated Execution Plan’, or select ‘Query/Display Estimated Execution Plan’ from the SSMS menu bar to accomplish the same task. 

Presenting the Actual Execution Plans is slightly different, more so in behavior than in the functionality that triggers presentation of the query plan.  Just as with Estimated Execution Plans there is a button in the SSMS application, you can right click the query and select ‘Include Actual Execution Plan’ from the pop-up window or do likewise from the menu bar; there is also a hotkey for Actual Execution Plans (in this case Ctrl+K).  Enabling the Actual Execution Plan is a behavioral toggle for the SSMS application.  This means that once you click the associated button, each time you execute any query you create in this or any query tab within SSMS the Actual Execution Plan for that query will be displayed.  Clicking the button a second time turns off the behavior.  The assigned button is shown below:

In the case of such a simple query as this one, both the actual and estimated query plans are identical.  As a matter of fact, from this point, we will ignore whether the query plan we’re observing is the Actual Execution Plan or the Estimated Execution Plan.

Reading the Graphical Execution Plan

Let’s look at the execution plan for the aforementioned query and we’ll begin the discussion on how to read such a plan.  It is slightly nonsensical to those of us in Western culture and you’ll see why in a second.

So why would I call this nonsensical?  Take a look at the direction the arrows point within the execution plan and you’ll see why.  You read a graphical execution plan right-to-left.  In future articles in this series you’ll also see that in truth we read them right-to-left and top-to-bottom.  For those of you who like to jump ahead to the good stuff in books I give you the following example of what you’ll observe in terms of a more-complex execution plan similar to what we will digest in detail later in the series:

SELECT C.[CompanyName], OD.[ProductID], OD.[Discount],

  OD.[Quantity], OD.[UnitPrice],

  O.[OrderDate], O.[RequiredDate], O.[ShippedDate]

FROM Orders O

  INNER JOIN [dbo].[Order Details] OD

     ON O.[OrderID] = OD.[OrderID]

  INNER JOIN [dbo].[Products] P

     ON OD.[ProductID] = P.[ProductID]

  INNER JOIN [dbo].[Customers] C

     ON C.[CustomerID] = O.[CustomerID]

WHERE O.[ShippedDate] > O.[RequiredDate]

The specifics of this more advanced query are unimportant at this time; all that is important is that you understand that the general reading of the query plan is, as I have stated right-to-left and top-to-bottom.  This more advanced query also highlights another notable display behavior and that is the size of the arrow used to denote the flow of data from one process to the next.  The thickness of the arrow correlates to the number of rows flowing between the steps.  But I digress…Let’s get back to the first execution plan, shall we?  Besides the flow of information and arrow symbolism you’ll also notice that each step/process has an associated cost.  This is a percentage of cost for the step compared to the total cost of all steps in the query plan.  Rounding is obviously involved, so when you do see 0% for a cost you need to understand that some time was incurred to complete the step.  Nothing, I repeat NOTHING is free in a SQL query.  There is yet another cost presented initially in the query plan and that is located in the header for the graphical query plan.  This cost is a factor if you run more than a single T-SQL statement in a batch.  Each statement will have an associated query plan, and this metric displays the cost for each statement when compared to the total for all statements run in the batch.  As a final task in this tip let’s take a quick look at this behavior.  What happens when we execute the following two statements in the same execution batch?

SELECT [CustomerID], [CompanyName], [City], [Region]

FROM [Northwind].[dbo].[Customers]

WHERE [Country] = ‘Germany’

ORDER BY [CompanyName]

SELECT [CustomerID], [CompanyName], [City], [Region]

FROM [Northwind].[dbo].[Customers]

WHERE [Country] = ‘Germany’ 

The statements were identical, other than the ORDER BY clause was omitted from the second statement.  When you compare the two statements, the metrics presented show that the first query consumes 75% of the total execution time for the batch, whereas the second query consumes only 25% of the batch’s total cost.  This is a relative cost metric, as labeled appropriately.

To summarize, graphical execution plans:

  • There are a variety of methods for displaying or triggering graphical execution plans
  • You read them right-to-left and top-to-bottom
  • The arrows denote not just the direction of data travel, but also (comparatively) amount of data rows being transferred from step-to-step in the execution process
  • Costs are displayed for each step, relative to the total cost of the query plan
  • The cost for the statement can be compared to other statements run in the same batch

In subsequent tips we will explore many of the various types of processes you’ll see presented as individual steps in the query execution plan.  We will also explore the additional wealth of information presented as pop-ups and properties of each step within the SQL Server Management Studio.

Capturing Query Plans with SQL Server Profiler

When tuning their SQL statements look at the query plan or execution plan.  This can be done in both a graphical tree format as well as a text based format.  For most people this information is confusing at best and does not explicitly tell you what the issue is and how to fix the problem.  For this reason, people have probably become more use to using the graphical plan output versus the text based output. 

When troubleshooting a performance problem, the first tool of choice is to either run a server side trace or a trace using Profiler.  In addition to getting the query stats it is sometimes helpful to also get the query plan, so you can see if a different plan is being used when the query runs in the application versus running the same query in a query window. When running a trace in SQL Server you have the ability to collect a text based query plan, but unless you are use to reading this output it is difficult to decipher what is going on especially for complex queries.  So how can you get useful query plan information in a graphical format when running a trace?

The events that we are interested in are the Showplan All and Showplan XML.  To see the output from each of these events we need to start a new profiler session for a SQL Server database and select these two events:

Once these events have been select start the trace.

To get an idea of how this works and the output for these two different events we can run the following stored procedure against the AdventureWorks database.

USE AdventureWorks
GO
EXEC dbo.uspGetManagerEmployees 185

After this statement is run, we can stop the trace and we will get the following output from our trace:

If we take a closer look at the Showplan XML output we get the following graphical query plan for this query.  This output works just like the output from a query window, so when you hover over one of the operations it gives you the details for each operation as shown below.

If we take a closer look at the Showplan All output we get a lot more output in a different format.  Here are three screenshots from various parts of the output from the Showplan All event.

So as you can see the output from these two events is extremely helpful in trying to troubleshoot a performance problem. 

From a first glance you would think this would be a great idea to just turn these events on for all activity.  That would be great, but be aware of the space needs to store all of this data.  From this simple example above the output files differ quite greatly.  For just this one statement here is the difference in file sizes.

  • 2K file – No query plan info
  • 18K file – Showplan All
  • 48K file – Showplan XML
  • 64K file – Both Showplan All and Showplan XML

Use a SQL Server Plan to Tune Queries

Plan guides allow you to add hints to specific queries without the invasiveness of directly changing any queries themselves. In situations where you cannot modify source code (either because it’s not available or because it’s contractually prohibited), this can be a very useful tool. However, it should be noted that this is an advanced feature and should be used with care.

There are 3 types of plan guides that you can create. The following excerpt from the SQL Server Books Online summarizes them:

  • OBJECT plan guides match queries that execute in the context of Transact-SQL stored procedures, scalar functions, multistatement table-valued functions, and DML triggers.
  • SQL plan guides match queries that execute in the context of stand-alone Transact-SQL statements and batches that are not part of a database object. SQL-based plan guides can also be used to match queries that parameterize to a specified form.
  • TEMPLATE plan guides match stand-alone queries that parameterize to a specified form. These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

Defining a plan guide is done through system stored procedure sp_create_plan_guide:

sp_create_plan_guide parameters

EXEC sp_create_plan_guide @name, @stmt, @type, @module_or_batch, @params, @hints

Here is an explanation of the parameters:

  • @name – name of the plan guide
  • @stmt – a T-SQL statement or batch
  • @type – indicates the type of guide (OBJECT, SQL, or TEMPLATE)
  • @module_or_batch – the name of a module (i.e. a stored procedure)
  • @params – for SQL and TEMPLATE guides, a string of all parameters for a T-SQL batch to be matched by this plan guide
  • @hints – OPTION clause hint to attach to a query as defined in the @stmt parameter

Let’s move on to an example that mimics a vendor application executing a prepared query embedded within their application code. The following parameterized dynamic SQL statements retrieve SalesOrderDetail information for a ProductID:

USE AdventureWorks
GO
EXEC sp_executesql
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 870
GO
 
EXEC sp_executesql 
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 897
GO

Initially running this query for ProductID = 870 produces a table scan. Since this ProductID appears in the table in 4,688 rows out of 121,317 total rows, the optimizer has determined that this is the best plan. The plan will be cached and prepared for use for all subsequent executions of this query.

However, the 2nd execution for ProductID = 897 also used a table scan though only 2 rows exist in the Sales.SalesOrderDetail for this ProductID. An index seek would be a better option in this case but we’re now tied to the execution plan that was generated the first time the prepared query was executed. In cases where this would become a problematic performance issue, a DBA could add a RECOMPILE option to the query which would force the optimizer to recompile the statement and optimize it every time it is executed.

Creating a SQL plan guide gets around the issue of not being able to or allowed to add hints to these database queries. The following plan guide is created and named GETSALESPRODUCTS_RECOMPILE_Fix and it applies a RECOMPILE to the SELECT statement that’s causing us grief.

USE AdventureWorks
GO
EXEC sp_create_plan_guide
@name = N'GETSALESPRODUCTS_RECOMPILE_Fix',
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@ProductID int',
@hints = N'OPTION (RECOMPILE)'
GO

Re-running the queries, the plan guide in place forces the SQL statement to recompile generating the most optimal query plan for a passed in ProductID. This comes without having to change a stitch of code. Examining the execution plans, we now see that a table scan is used for ProductID = 870 but an index seek is used on ProductID = 897. A profiler trace also shows that the statements are re-compiled prior to execution:

As you can see, this is really powerful way to tune queries that you may not have access to. Prior to SQL Server 2005, queries similar to these that were buried deep in application code that you don’t have access to were practically impossible to tune.

You can view a listing of all plan guides stored in the database using the following query:

SELECT * FROM sys.plan_guides
GO

You may have been thinking: how do we get rid of a plan guide if it’s determined that we no longer need it or want it? This can be controlled with another system stored procedure named sp_control_plan_guide. Using this procedure, you can DROP the plan guide, DISABLE it, or re-ENABLE it if it had been previously disabled.

sp_control_plan_guide parameters

EXEC sp_control_plan_guide @operation, @name

Explanation of its parameters:

  • @operation – a control option; one of DROP, DROP ALL, DISABLE, DISABLE ALL, ENABLE, ENABLE ALL
  • @name – name of the plan guide to control

We can then issue the following statement to DROP the plan guide, if necessary:

EXEC sp_control_plan_guide N'DROP', N'GETSALESPRODUCTS_RECOMPILE_Fix'
GO

Since plan guides affect the optimizer’s choice of execution plan, they should be carefully leveraged (and documented!) in very specific situations where access to database queries is limited.

SQL Server Query Optimization with Database Engine Tuning Advisor

Most of the times that we need to optimize a SQL Server query, a lot of questions and uncertainties come to our minds, just like if we could use any tool that would help with this type of improvement not only regarding the performance itself, but also in structural terms like with indexes, partitioning, DDL and DML, etc. Imagine you, being a SQL Server developer that needs to deal with lots of queries every day and still must consult the SQL Server DBA to check each of them, having such kind of optimizer in hands. That’s when the Database Engine Tuning Advisor, present in SQL Server, comes to the table, providing a lot of great analysis and recommendations based on our queries and workloads. In this tip we will cover the use of this tool, especially improving the performance of our queries based on structural changes.

The famous DTA (Database Engine Tuning Advisor), basically analyzes databases and gives some recommendations, you can find a series of things it helps with:

  • Troubleshoot the performance of a specific problem query
  • Tune a large set of queries across one or more databases
  • Perform an exploratory what-if analysis of potential physical design changes
  • Manage storage space

For this example, we’re going to use the AdventureWorks Databases and Scripts for SQL Server, which is great as a data sample for test purposes. So, first, open SQL Server Management Studio and import the database following the official steps. Then, let’s define the query we want to optimize

-- sales for group of customers by year
SELECT
    year = YEAR(header.OrderDate),
    sum = SUM(detail.LineTotal) 
FROM 
    Sales.SalesOrderDetail detail 
INNER JOIN 
    Sales.SalesOrderHeader header ON detail.SalesOrderID = header.SalesOrderID
INNER JOIN 
    Sales.Customer cust ON header.CustomerID = cust.CustomerID
WHERE
    cust.CustomerID > 10000 and cust.CustomerID < 10000000 
GROUP BY
    YEAR(header.OrderDate)

Make sure to have the proper database selected in order to run the query and get the following results

What we need to do now is to open the “SQL Server Database Engine Tuning Advisor” going to the menu Tools > Database Engine Tuning Advisor. Authenticate and create a new session name with the same options we have below.

Then, get back to the query, right click it and click on the option “Analyze Query in Database Engine Tuning Advisor“:

The Analyzer is going to run and give us an estimated improvement over the performance of our queries:

DTA includes a lot of different reports, from the performance and how costly the query is, to the way each entity relates to the other, the balance between the keys (indexes, etc.), and much more:

With them, we can check, for example, how much improvement we’ll have when we decide to apply one of the suggested improvements. Let’s look at the “Statement cost report”, e.g., which says that our query can be improved by 33.07% if we apply the suggestions:

In order to check each improvement, go to the “Definition” column and click in one of them. Let’s try the first one, related to an index recommendation:

Then, go back to SQL Server Management Studio and run the recommended script that’ll create a new non-clustered index to the CustomerID column, just like:

When finished, let’s run the Tuning Advisor once again, go to the Reports tab and check the “Statement cost report” again:

The index creation allowed our select to execute in a more optimized way. If you follow the other recommendations, you can further improve the query and the percent improvement should get smaller with each change you make.

This concludes this article, where we covered a small fraction of the range of information we will have regarding the Database Engine Tuning Advisor and how it helps us to get recommendations based on the workloads we present for performance optimization.

SQL Server Database Engine Tuning Advisor for Performance Tuning

The Database Engine Tuning Advisor (DTA) is a performance tuning tool.  DTA replaced the Index Tuning Wizard in previous versions of SQL Server.  In a nutshell DTA analyzes a workload and makes recommendations that will enhance query performance; e.g.:

  • Adding indexes (clustered, nonclustered, and indexed views)
  • Adding partitioning
  • Adding statistics including multi-column statistics which are not created automatically even when you have the AUTO_CREATE_STATISTICS database option set to ON

In addition to identifying all sorts of opportunities for improvement, DTA will create a T-SQL script that you can run to actually implement its recommendations.

There are several ways that you can launch DTA:

  • Use the Start menu and navigate to the Performance Tools folder in the SQL Server program group
  • Select DTA from the Tools menu in SQL Server Management Studio (SSMS)
  • Select DTA from the Tools menu in SQL Server Profiler
  • Select Analyze Query in Database Engine Tuning Advisor on the SSMS Query menu from a query window, allowing you to pass a T-SQL batch to DTA for analysis
  • Type DTA from a Command Prompt; add -? to the command line to see the available options

You typically create a workload by putting together a group of queries in a text file or by running SQL Server Profiler.  The key point to consider is that the workload you pass to DTA for analysis should be representative of the typical workload.  The recommendations are based on what DTA sees in the workload.  If the workload is very limited the recommendations may not be optimal.  Usually the best bet is to capture the workload by running SQL Server Profiler, save the trace to a file, and pass that file on to the DTA.  I will demonstrate this approach.

Launch SQL Server Profiler from the Performance Tools folder of the SQL Server program group in the Start menu or from the Tools menu in SSMS.  Create a new trace and fill in the dialog as shown below:

The following are the main points about the general properties of the new trace:

  • Select Tuning from the dropdown list for the trace template; this template collects all of the events that are required by DTA.
  • Check Save to file and specify a file name; you can pass this file to DTA when the trace is done.
  • The workload used for this tip is to build the Adventure Works 2008 cube using the sample SQL Server Analysis Services project that you can download from this page.  To generate a lot of recommendations I removed the indexes from the AdventureWorksDW database then processed the cube.

Click on the Events Selection tab to review the events and columns selected:

You may want to add a filter to the trace to limit the information that is collected.  For instance you may want to collect trace information from a single database.  To do that click the column filters button on the Events Selection dialog (not show in the above screen shot) and enter your filter(s).  In my case I want to filter on the DatabaseId column as shown below (the AdventureWorksDW database that I’m using has a DatabaseId = 26):

Start the profiler trace by clicking the Run button on the Trace Properties dialog and let it run for a period where you will capture a representative workload.  Stop the trace by selecting Stop trace from the File menu in SQL Server Profiler.  Launch DTA from SQL Server Profiler by selecting Database Engine Tuning Advisor from the Tools menu; fill in the dialog as follows:

The following are the main points about the DTA General properties dialog:

  • Click the File radio button and specify the trace file created by running the SQL Server Profiler
  • Select the database for workload analysis
  • Select the database to tune; you can select more than one and you can also drill in and specify individual tables; I just selected one database and all tables
  • Click the Save tuning log checkbox; this will save the results automatically in the MSDB database and they can be recalled within DTA at a later time

Click the Tuning Options tab and fill in the dialog as follows:

The Tuning Options allow you to specify your choices for physical design structures, partitioning, and whether to keep the existing physical design structures.  These options are mutually exclusive so you may want to run the analysis more than once if necessary to specify different options.  If you have a limited amount of time that you want to allow DTA to run, click the Limit tuning time checkbox and specify a stop time.  Click Start Analysis on the DTA top-level menu to begin the analysis.  When DTA completes the analysis, you will be presented with recommendations and their estimated improvement percentage, as well as a number of reports that you can review.  For the recommendations you can choose from the following options on the DTA Actions menu:

  • Apply Recommendations will implement the recommendations; you can apply immediately or schedule a time
  • Save Recommendations will save the recommendations to a file; you can review and apply as you see fit
  • Evaluate Recommendations allows you to pick and choose from among the recommendations and evaluate their impact

Saving the recommendations is probably your best bet.  You should review them and apply them as you see fit.  DTA creates the T-SQL commands to implement the recommendations.  The following is a sample of the recommendations, showing creating a multi-column statistic, a nonclustered index, and an indexed view:

CREATE STATISTICS [_dta_stat_325576198_10_2] 
ON [dbo].[FactResellerSales] (
    [SalesOrderNumber]
  , [OrderDateKey]
)
go
CREATE NONCLUSTERED INDEX [_dta_index_DimCustomer_8_37575172__K1_K20] 
ON [dbo].[DimCustomer] (
  [CustomerKey] ASC
, [EnglishOccupation] ASC
)
WITH (
  SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF
, DROP_EXISTING = OFF
, ONLINE = OFF
) ON [PRIMARY]
go
CREATE VIEW [dbo].[_dta_mv_530] WITH SCHEMABINDING
 AS 
SELECT  
  [dbo].[DimCustomer].[Phone] as _col_1
, count_big(*) as _col_2 
FROM  [dbo].[DimCustomer]  
GROUP BY  [dbo].[DimCustomer].[Phone]  
go
CREATE UNIQUE CLUSTERED INDEX [_dta_index__dta_mv_530_c_8_1639676889__K1] 
ON [dbo].[_dta_mv_530] (
 [_col_1] ASC
)WITH (
  SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF
, DROP_EXISTING = OFF
, ONLINE = OFF
) ON [PRIMARY]
go

One of the things you probably want to change is the object names.  DTA generates these in a generic way; you would be better off to follow your own naming conventions.

DTA generates a number of reports based on its analysis of your workload as shown below:

The following are some highlights of these reports:

  • Statement Cost Report – shows the percent improvement of each statement in the workload if the recommendations are implemented
  • Statement Detail Report – shows the current cost of the statement and the cost if the recommendations are implemented
  • Statement-Index Relations Report (Recommended) – shows the statements in the workload and the index recommendations
  • Index Usage Reports – show the current and recommended indexes and how often they are utilized
  • Index Detail Reports – show the size (in MB) and number of rows in the current and recommended indexes
  • Workload Analysis Report – shows the number of statements that have a lower cost, an increased cost or no change if the recommendations are implemented
  • Table Access Report – shows the number of times and percentage that a table is used in the workload
  • Column Access Report – shows the number of times and percentage that a table column is used in the workload

Query Profiling Infrastructure

The SQL Server Database Engine provides the ability to access runtime information on query execution plans. One of the most important actions when a performance issue occurs, is to get precise understanding on the workload that is executing and how resource usage is being driven. For this, access to the actual execution plan is important.

While query completion is a prerequisite for the availability of an actual query plan, live query statistics can provide real-time insights into the query execution process as the data flows from one query plan operator to another. The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc. Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues, such as long running queries, and queries that run indefinitely and never finish.

The standard query execution statistics profiling infrastructure

The query execution statistics profile infrastructure, or standard profiling, must be enabled to collect information about execution plans, namely row count, CPU and I/O usage.

The following methods of collecting execution plan information globally for all sessions leverage the standard profiling infrastructure:

  • The query_post_execution_showplan extended event.
  • The Showplan XML trace event in SQL Trace and SQL Server Profiler. For more information on this trace event, see Showplan XML Event Class.

When running an extended event session that uses the query_post_execution_showplan event, then the sys.dm_exec_query_profiles DMV is also populated, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV.

The lightweight query execution statistics profiling infrastructure

Starting with SQL Server 2016 (13.x), a new lightweight query execution statistics profiling infrastructure, or lightweight profiling was introduced.

Lightweight query execution statistics profiling infrastructure v1

The performance overhead to collect information about execution plans was reduced with the introduction of lightweight profiling. Unlike standard profiling, lightweight profiling does not collect CPU runtime information. However, lightweight profiling still collects row count and I/O usage information.

A new query_thread_profile extended event was also introduced that leverages lightweight profiling. This extended event exposes per-operator execution statistics allowing more insight on the performance of each node and thread. A sample session using this extended event can be configured as in the below example:

SQL

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

When running an extended event session that uses the query_thread_profile event, then the sys.dm_exec_query_profiles DMV is also populated using lightweight profiling, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV.

Lightweight query execution statistics profiling infrastructure v2

SQL Server 2016 includes a revised version of lightweight profiling with minimal overhead. Lightweight profiling can also be enabled globally using trace flag 7412 for the versions stated above in Applies to. A new DMF sys.dm_exec_query_statistics_xml is introduced to return the query execution plan for in-flight requests.

Starting with SQL Server 2016, if lightweight profiling is not enabled globally then the new USE HINT query hint argument QUERY_PLAN_PROFILE can be used to enable lightweight profiling at the query level, for any session. When a query that contains this new hint finishes, a new query_plan_profile extended event is also output that provides an actual execution plan XML similar to the query_post_execution_showplan extended event.

The query_plan_profile extended event also leverages lightweight profiling even if the query hint is not used.

A sample session using the query_plan_profile extended event can be configured like the example below:

SQL

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Lightweight query execution statistics profiling infrastructure v3

starting with SQL Server 2019 and Azure SQL Database. SQL Server 2019 and Azure SQL Database include a newly revised version of lightweight profiling collecting row count information for all executions. Lightweight profiling is enabled by default on SQL Server 2019 and Azure SQL Database. Starting with SQL Server 2019, trace flag 7412 has no effect. Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: 

ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

A new DMF sys.dm_exec_query_plan_stats is introduced to return the equivalent of the last known actual execution plan for most queries, and is called last query plan statistics. The last query plan statistics can be enabled at the database level using the LAST_QUERY_PLAN_STATS database scoped configuration: 

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

A new query_post_execution_plan_profile extended event collects the equivalent of an actual execution plan based on lightweight profiling, unlike query_post_execution_showplan which uses standard profiling. SQL Server 2017 (14.x) also offers this event starting with CU14. A sample session using the query_post_execution_plan_profile extended event can be configured like the example below:

SQL

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);
 

Extended Event session using standard profiling

SQL

CREATE EVENT SESSION [QueryPlanOld] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename = N'C:\Temp\QueryPlanStd.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);
 

Extended Event session using lightweight profiling

SQL

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPlanLWP.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

Query Profiling Infrastruture usage guidance

The following table summarizes the actions to enable either standard profiling or lightweight profiling, both globally (at the server level) or in a single session. Also includes the earliest version for which the action is available.

QUERY PROFILING INFRASTRUTURE USAGE GUIDANCE
ScopeStandard ProfilingLightweight Profiling
GlobalxEvent session with the query_post_execution_showplan XE; Starting with SQL Server 2012 (11.x)Trace Flag 7412; Starting with SQL Server 2016 (13.x) SP1
GlobalSQL Trace and SQL Server Profiler with the Showplan XML trace event; Starting with SQL Server 2000xEvent session with the query_thread_profile XE; Starting with SQL Server 2014 (12.x) SP2
GlobalxEvent session with the query_post_execution_plan_profile XE; Starting with SQL Server 2017 (14.x) CU14 and SQL Server 2019 (15.x)
SessionUse SET STATISTICS XML ON; Starting with SQL Server 2000Use the QUERY_PLAN_PROFILE query hint together with an xEvent session with the query_plan_profile XE; Starting with SQL Server 2016 (13.x) SP2 CU3 and SQL Server 2017 (14.x) CU11
SessionUse SET STATISTICS PROFILE ON; Starting with SQL Server 2000

Starting with lightweight profiling v2 and its low overhead, any server that is not already CPU bound can run lightweight profiling continuously, and allow database professionals to tap into any running execution at any time, for example using Activity Monitor or directly querying sys.dm_exec_query_profiles, and get the query plan with runtime statistics.

Showplan Logical and Physical Operators Reference

Operators describe how SQL Server executes a query or a Data Manipulation Language (DML) statement. The query optimizer uses operators to build a query plan to create the result specified in the query, or to perform the operation specified in the DML statement. The query plan is a tree consisting of physical operators. You can view the query plan by using the SET SHOWPLAN statements, the graphical execution plan options in SQL Server Management Studio, or the SQL Server Profiler Showplan event classes.

Operators are classified as logical and physical operators.

Logical Operators
Logical operators describe the relational algebraic operation used to process a statement. In other words, logical operators describe conceptually what operation needs to be performed.

Physical Operators
Physical operators implement the operation described by logical operators. Each physical operator is an object or routine that performs an operation. For example, some physical operators access columns or rows from a table, index or view. Other physical operators perform other operations such as calculations, aggregations, data integrity checks or joins. Physical operators have costs associated with them.

The physical operators initialize, collect data, and close. Specifically, the physical operator can answer the following three method calls:

  • Init(): The Init() method causes a physical operator to initialize itself and set up any required data structures. The physical operator may receive many Init() calls, though typically a physical operator receives only one.
  • GetNext(): The GetNext() method causes a physical operator to get the first, or subsequent row of data. The physical operator may receive zero or many GetNext() calls.
  • Close(): The Close() method causes a physical operator to perform some clean-up operations and shut itself down. A physical operator only receives one Close() call.

The GetNext() method returns one row of data, and the number of times it is called appears as ActualRows in the Showplan output that is produced by using SET STATISTICS PROFILE ON or SET STATISTICS XML ON.

The ActualRebinds and ActualRewinds counts that appear in Showplan output refer to the number of times that the Init() method is called. Unless an operator is on the inner side of a nested loops join, ActualRebinds equals one and ActualRewinds equals zero. If an operator is on the inner side of a loop join, the sum of the number of rebinds and rewinds should equal the number of rows processed on the outer side of the join. A rebind means that one or more of the correlated parameters of the join changed and the inner side must be reevaluated. A rewind means that none of the correlated parameters changed and the prior inner result set may be reused.

ActualRebinds and ActualRewinds are present in XML Showplan output produced by using SET STATISTICS XML ON. They are only populated for the Nonclustered Index SpoolRemote QueryRow Count SpoolSortTable Spool, and Table-valued Function operators. ActualRebinds and ActualRewinds may also be populated for the Assert and Filter operators when the StartupExpression attribute is set to TRUE.

When ActualRebinds and ActualRewinds are present in an XML Showplan, they are comparable to EstimateRebinds and EstimateRewinds. When they are absent, the estimated number of rows (EstimateRows) is comparable to the actual number of rows (ActualRows). Note that actual graphical Showplan output displays zeros for the actual rebinds and actual rewinds when they are absent.

A related counter, ActualEndOfScans, is available only when Showplan output is produced by using SET STATISTICS XML ON. Whenever a physical operator reaches the end of its data stream, this counter is incremented by one. A physical operator can reach the end of its data stream zero, one, or multiple times. As with rebinds and rewinds, the number of end of scans can be more than one only if the operator is on the inner side of a loop join. The number of end of scans should be less than or equal to the sum of the number of rebinds and rewinds.

Mapping Physical and Logical Operators

The Query Optimizer creates a query plan as a tree consisting of logical operators. After the query optimizer creates the plan, the Query Optimizer chooses the most efficient physical operator for each logical operator. The query optimizer uses a cost-based approach to determine which physical operator will implement a logical operator.

Usually, a logical operation can be implemented by multiple physical operators. However, in rare cases, a physical operator can implement multiple logical operations as well.

Display and Save Execution Plans

This section explains how to display execution plans and how to save execution plans to a file in XML format by using Microsoft SQL Server Management Studio.

Execution plans graphically display the data retrieval methods chosen by the SQL Server Query Optimizer. Execution plans represent the execution cost of specific statements and queries in SQL Server using icons rather than the tabular representation produced by the SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT statements. This graphical approach is useful for understanding the performance characteristics of a query.  

While the SQL Server Query Optimizer produces only one execution plan, there is the concept of estimated execution plan and actual execution plan.

  • An estimated execution plan returns the execution plan as produced by the Query Optimizer at compile time. Producing the estimated execution plan does not actually execute the query or batch, and therefore does not contain any runtime information, such as actual resource usage metrics or runtime warnings.
  • An actual execution plan returns the execution plan as produced by the Query Optimizer, and after queries or batches finish execution. This includes runtime information about resource usage metrics and any runtime warnings.  

Display the Estimated Execution Plan

This topic describes how to generate graphical estimated execution plans by using SQL Server Management Studio. When estimated execution plans are generated, the Transact-SQL queries or batches do not execute. Because of this, an estimated execution plan does not contain any runtime information, such as actual resource usage metrics or runtime warnings. Instead, the execution plan that is generated displays the query execution plan that SQL Server Database Engine would most probably use if the queries were actually executed, and displays the estimated rows flowing through the several operators in the plan.

To use this feature, users must have the appropriate permissions to execute the Transact-SQL query for which a graphical execution plan is being generated, and they must be granted the SHOWPLAN permission for all databases referenced by the query.

To display the estimated execution plan for a query

  1. On the toolbar, click Database Engine Query. You can also open an existing query and display the estimated execution plan by clicking the Open File toolbar button and locating the existing query.
  2. Enter the query for which you would like to display the estimated execution plan.
  3. On the Query menu, click Display Estimated Execution Plan or click the Display Estimated Execution Plan toolbar button. The estimated execution plan is displayed on the Execution Plan tab in the results pane.

To view additional information, pause the mouse over the logical and physical operator icons and view the description and properties of the operator in the displayed ToolTip. Alternatively, you can view operator properties in the Properties window. If Properties is not visible, right-click an operator and click Properties. Select an operator to view its properties.

  • To alter the display of the execution plan, right-click the execution plan and select Zoom InZoom OutCustom Zoom, or Zoom to FitZoom In and Zoom Out allow you to magnify or reduce the execution plan by fixed amounts. Custom Zoom allows you to define your own display magnification, such as zooming at 80 percent. Zoom to Fit magnifies the execution plan to fit the result pane. Alternatively, use a combination of the CTRL key and your mouse wheel to activate dynamic zoom.
  • To navigate the display of the execution plan, use the vertical and horizontal scroll bars, or click and hold on any blank area of the execution plan, and drag your mouse. Alternatively, click and hold the plus (+) sign in the right lower corner of the execution plan window, to display a miniature map of the entire execution plan.

Display an Actual Execution Plan

This topic describes how to generate actual graphical execution plans by using SQL Server Management Studio. Actual execution plans are generated after the Transact-SQL queries or batches execute. Because of this, an actual execution plan contains runtime information, such as actual resource usage metrics and runtime warnings (if any). The execution plan that is generated displays the actual query execution plan that the SQL Server Database Engine used to execute the queries.

To use this feature, users must have the appropriate permissions to execute the Transact-SQL queries for which a graphical execution plan is being generated, and they must be granted the SHOWPLAN permission for all databases referenced by the query.

To include an execution plan for a query during execution

  1. On the SQL Server Management Studio toolbar, click Database Engine Query. You can also open an existing query and display the estimated execution plan by clicking the Open File toolbar button and locating the existing query.
  2. Enter the query for which you would like to display the actual execution plan.
  3. On the Query menu, click Include Actual Execution Plan or click the Include Actual Execution Plan toolbar button.
  • Execute the query by clicking the Execute toolbar button. The plan used by the query optimizer is displayed on the Execution Plan tab in the results pane.
  • Pause the mouse over the logical and physical operators to view the description and properties of the operators in the displayed ToolTip, including properties of the overall execution plan, by selecting the root node operator (the SELECT node in the picture above).

Alternatively, you can view operator properties in the Properties window. If Properties is not visible, right-click an operator and click Properties. Select an operator to view its properties.

  • You can alter the display of the execution plan by right-clicking the execution plan and selecting Zoom InZoom OutCustom Zoom, or Zoom to FitZoom In and Zoom Out allow you to zoom in or out on the execution plan, while Custom Zoom allows you to define your own zoom, such as zooming at 80 percent. Zoom to Fit magnifies the execution plan to fit the result pane. Alternatively, use a combination of the CTRL key and your mouse wheel to activate dynamic zoom.
  • To navigate the display of the execution plan, use the vertical and horizontal scroll bars, or click and hold on any blank area of the execution plan, and drag your mouse. Alternatively, click and hold the plus (+) sign in the right lower corner of the execution plan window, to display a miniature map of the entire execution plan.

Save an Execution Plan in XML Format

Use SQL Server Management Studio to save execution plans as an XML file, and to open them for viewing.

To use the execution plan feature in Management Studio, or to use the XML Showplan SET options, users must have the appropriate permissions to execute the Transact-SQL query for which an execution plan is being generated, and they must be granted the SHOWPLAN permission for all databases referenced by the query.

To save a query plan by using the XML Showplan SET options

  1. In SQL Server Management Studio open a query editor and connect to Database Engine.
  2. Turn SHOWPLAN_XML on with the following statement:

SQL

SET SHOWPLAN_XML ON; 

GO 

To turn STATISTICS XML on, use the following statement:

SQL

SET STATISTICS XML ON; 

GO 

  • Execute a query. Example:

SQL

USE AdventureWorks2012; 

GO 

SET SHOWPLAN_XML ON; 

GO 

— Execute a query. 

SELECT BusinessEntityID  

FROM HumanResources.Employee 

WHERE NationalIDNumber = ‘509647174’; 

GO 

SET SHOWPLAN_XML OFF; 

  • In the Results pane, right-click the Microsoft SQL Server XML Showplan that contains the query plan, and then click Save Results As.
  • In the Save <Grid or Text> Results dialog box, in the Save as type box, click All files (*.*).
  • In the File name box provide a name, in the format <name>.sqlplan, and then click Save.

To save an execution plan by using SQL Server Management Studio options

  1. Generate either an estimated execution plan or an actual execution plan by using Management Studio.
  2. In the Execution plan tab of the results pane, right-click the graphical execution plan, and choose Save Execution Plan As.

As an alternative, you can also choose Save Execution Plan As on the File menu.

  • In the Save As dialog box, make sure that the Save as type is set to Execution Plan Files (*.sqlplan).
  • In the File name box provide a name, in the format <name>.sqlplan, and then click Save.

To open a saved XML query plan in SQL Server Management Studio

  1. In SQL Server Management Studio, on the File menu, choose Open, and then click File.
  2. In the Open File dialog box, set Files of type to Execution Plan Files (*.sqlplan) to produce a filtered list of saved XML query plan files.
  3. Select the XML query plan file that you want to view, and click Open.

As an alternative, in Windows Explorer, double-click a file with extension .sqlplan. The plan opens in Management Studio.

Compare and Analyze Execution Plans

This section explains how to compare and analyze execution plans by using Microsoft SQL Server Management Studio. This feature is available starting with SQL Server Management Studio v17.4.

Execution plans graphically display the data retrieval methods chosen by the SQL Server Query Optimizer. Execution plans represent the execution cost of specific statements and queries in SQL Server using icons rather than the tabular representation produced by the SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT statements. This graphical approach is very useful for understanding the performance characteristics of a query.

SQL Server Management Studio includes functionality that allows users compare two execution plans, for example between perceived good and bad plans for the same query, and perform root cause analysis. Also included is the functionality to perform single query plan analysis, allowing insights into scenarios that may be affecting the performance of a query through analysis of its execution plan.

Compare execution plans

This topic describes how to compare similarities and differences between actual graphical execution plans by using SQL Server Management Studio Plan Comparison feature. This feature is available starting with SQL Server Management Studio v16.

The ability to compare plans is something that database professionals may have to do for troubleshooting reasons:

  • Find why a query or batch suddenly slowed down.
  • Understand the impact of a query rewrite.
  • Observe how a specific performance-enhancing change introduced to the schema design (like a new index) has effectively changed the execution plan.

The Plan Comparison menu option allows side-by-side comparison of two different execution plans, for easier identification of similarities and changes that explain the different behaviors for all the reasons stated above. This option can compare between:

  • Two previously saved execution plan files (.sqlplan extension).
  • One active execution plan and one previously saved query execution plan.
  • Two selected query plans in Query Store.

When two execution plans are compared, regions of the plan that do essentially the same are highlighted in the same color and pattern. Clicking on a colored region in one plan will center the other plan on the matching node in that plan. You can still compare unmatched operators and nodes of the execution plans, but in that case you must manually select the operators to compare.

To compare execution plans

  1. Open a previously saved query execution plan file (.sqlplan) using the File menu and clicking on Open File, or drag a plan file to Management Studio window. Alternatively, if you just executed a query and chose to display its execution plan, move to the Execution Plan tab in the results pane.
  2. Right-click in a blank area of the execution plan and click Compare Showplan.
  • Choose the second query plan file that you would like to compare with. The second file will open so that you can compare the plans.
  • The compared plans will open a new window, by default with one on top and one on the bottom. The default selection will be the first occurrence of an operator or node that is common in the compared plans, but showing differences between plans. All highlighted operators and nodes exist in both compared plans. Selecting an highlighted operator in the top or left plans automatically selects the corresponding operator in the bottom or right plans. Selecting the root node operator in any of the compared plans (the SELECT node in the picture below) also selects the respective root node operator in the other compared plan.
  • A dual properties window also opens on the right side, in the scope of the default selection. Properties that exist in both compared operators but have differences will be preceded by the not equal sign (≠) for easier identification.
  • The Showplan Analysis comparison navigation window also opens on the bottom. Three tabs are available:
    • In the Statement Options tab, the default selection is Highlight similar operations and the same highlighted operator or node in compared plans share the same color and line pattern. Navigate between similar areas in compared plans by clicking on a lime pattern. You can also choose to highlight differences in plans rather similarities, by selecting Highlight operations not matching similar segments.
  • The Multi Statement tab is useful when comparing plans with multiple statements, by allowing the right statement pair to be compared.
  • In the Scenarios tab you can find an automated analysis of some of the most relevant aspects to look at in what relates to Cardinality Estimation differences in compared plans. For each listed operator on the left pane, the right pane shows details about the scenario in the Click here for more information about this scenario link, and possible reasons to explain that scenario are listed.

If this window is closed, right-click on a blank area of a compared plan, and select Showplan Compare Options to re-open.

To compare execution plans in Query Store

  1. In Query Store, identify a query that has more than one execution plan.
  2. Use a combination of the SHIFT key and your mouse to select two plans for the same query.
  • Use the button Compare the plans for the select query in a separate window to start plan comparison. Then steps 4 through 6 of To compare execution plans are applicable.

Analyze an Actual Execution Plan

This topic describes how you can analyze actual graphical execution plans by using SQL Server Management Studio Plan Analysis feature. This feature is available starting with SQL Server Management Studio v17.4.

Query performance troubleshooting requires significant expertise in understanding query processing and execution plans, in order to be able to actually find and fix root causes.

SQL Server Management Studio includes functionality that implements some degree of automation in the task of actual execution plan analysis, especially for large and complex plans. The goal is to make it easier to find scenarios of inaccurate Cardinality Estimation and get recommendations on which possible mitigations may be available.

To analyze an execution plan for a query

  1. Open a previously saved query execution plan file (.sqlplan) using the File menu and clicking on Open File, or drag a plan file to Management Studio window. Alternatively, if you just executed a query and chose to display its execution plan, move to the Execution Plan tab in the results pane.
  2. Right-click in a blank area of the execution plan and click Analyze Actual Execution Plan.
  • The Showplan Analysis window opens on the bottom. The Multi Statement tab is useful when analyzing plans with multiple statements, by allowing the right statement to be analyzed.
  • Select the Scenarios tab to see details on the issues found for the actual execution plan. For each listed operator on the left pane, the right pane shows details about the scenario in the Click here for more information about this scenario link, and possible reasons to explain that scenario are listed.