Generic information
Introduction
This site is intended to be a reference, not a beginners manual. Yet I feel the need to create a page with explanations of the basics. Not only for people who are new to execution plans, but also because even people with more experience tend to be incompletely or incorrectly informed on some of the basics.
Where to find an execution plan?
If you want to understand why a query is running slow, you need to look at its execution plan. But where do you find an execution plan? There are multiple options.
Management Studio
Most of the time when you are working on code that needs tuning, you will have the code in a query window in SQL Server Management Studio. There are two buttons on the toolbar that you can use to get an execution plan; each has a corresponding item in the query menu and a keyboard shortcut.
When you click the “Display Estimated Execution Plan” option, SSMS submits the text in the query window (or a subset if you have highlighted a part of that window’s contents) to the query optimizer to create an execution plan which will then be displayed in graphical format. The query will not actually execute. This can be a good option to quickly look at an execution plan for a very long-running query, or for queries that would modify data. However, what is returned is the execution plan only, without any additional information.
To get an execution plan plus run-time statistics (“execution plan plus” for short; also confusingly referred to as an “actual execution plan”), you click the “Include Actual Execution Plan” option to toggle this option on or off. When it is on, SSMS captures execution plans for every query executed from that window, enriches them with statistics gathered during execution, and displays them in graphical format. This option gives more information than the execution plan only, but you will have to wait for the query to end. And if the query aborts with a runtime error, no execution plan is shown. More on the differences between execution plan only and execution plan plus below.
In recent versions of Management Studio, there is also the “Include Live Query Statistics” option, which also works as a toggle to request the so-called “live execution plan”. When on, each query running from that window will immediately show an execution plan and then continuously update it with statistics from the executing query. This can be a nice option to track the progress of long-running queries, but is a less useful tool for troubleshooting slow queries.
Other methods for getting an execution plan from within Management Studio include using the SET STATISTICS_XML ON and SET STATISTICS_XML OFF commands to instruct SQL Server to capture the execution plan plus run-time statistics for the queries between those two statements, and the SET SHOWPLAN_XML ON and SET SHOWPLAN_XML OFF statements to instruct SQL Server to capture the execution plan only for the statements in between (without executing them); these options will add a result set with the XML representation of the execution plan that you can click to open the graphical representation.
Other sources
When you already know which queries you need to tune, you can simply load it in Management Studio and use the methods above to dig into the execution plan. But often you first need to find the exact culprit. For this, there are also multiple methods.
If you are running Azure SQL Database or SQL Server 2016 or higher, and you have enabled the Query Store feature, you can use the standard reports for the Query Store to find execution plans. The Management Studio user interface functions for the Query Store display the relevant plans in graphical format. You can also grab queries from the catalog view for the Query Store, for instance with a query such as this:
SELECT qsp.plan_id,
qsp.query_id,
CAST(qsp.query_plan AS xml) AS QueryPlan,
qsp.last_execution_time,
qsqt.query_sql_text
FROM sys.query_store_plan AS qsp
INNER JOIN sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
INNER JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id;
This gives a very basic overview of all execution plans stored in the Query Store. Adding more columns from the catalog views already used, or joining to other catalog views such as sys.query_store_runtime_stats and sys.query_store_wait_stats can help you find the queries you need to tune.
If you can’t use the Query Store to find problematic execution plans, you can still fall back on the Dynamic Management Views to find the same information. Here is an example query, similar to the one above but now requesting data from the DMVs. Do note, though, that data in these DMVs will be gone when the server reboots, or even when parts of the procedure cache are cleared because of memory pressure.
SELECT decp.objtype, decp.usecounts, decp.refcounts, DB_NAME(dest.dbid) AS DatabaseName, OBJECT_NAME(dest.objectid, dest.dbid) AS ObjectName, dest.text AS QueryText, deqp.query_plan FROM sys.dm_exec_cached_plans AS decp CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp;
As the query above, this is a starting point; you will probably need to join to other DMVs and retrieve additional columns to help you find the queries to tune.
A final method you can use to find execution plans is to set up an Extended Events session (or a Profiler Trace if you need to target SQL Server 2005 or older). In Extended Events, you capture either the query_pre_execution_showplan event or the query_post_execution_showplan event, to get respectively the execution plan only or the execution plan plus run-time statistics. The Profiler alternatives are the Showplan XML event respectively the Showplan XML Statistics Profile event. Regardless of whether you use Extended Events or Profiler, make sure to filter as effectively as possible to prevent the server from being slowed down by the overhead of capturing too many execution plans.
Saving execution plans
When looking at the graphical representation of an execution plan in SSMS, you can right-click in an empty section of the screen to bring up the context menu, and then choose “Save Execution Plan As…” to save a copy of the execution plan to a file on your disk. By default this file will be given the .sqlplan extension. Double-clicking a file with this extension will, by default, attempt to open the file in Management Studio and render it as a graphical execution plan.
When you save an execution plan and open the .sqlplan file with a text editor, you will see that the contents are XML. The same XML you will also see when you choose “Show Execution Plan XML” from the context menu in the execution plan pane of SSMS, or when you retrieve the execution plan XML in any of the other methods described above. This means that you can also copy the XML into a file manually, rename it to have the .sqlplan extension, and then double-click to open it in SSMS. As long as the content adheres to the XML schema restrictions that apply to execution plans, the plan will be rendered.
Saving an execution plan and then sharing it with someone or posting it on an internet forum can be a good and efficient way to get help for improving query performance. But you need to be aware of the data that is stored in a .sqlplan file to prevent sharing sensitive data. A longer article about this can be found here.
Actual plan vs Estimated plan
Historically, an execution plan plus run-time statistics used to be called an “Actual Execution Plan”, and an execution plan only was called an “Estimated Execution Plan”. These terms are currently still used in the user interface, but early 2020 a group of execution plan experts started a petition to change these terms, because the old terms cause too much confusion. On this site we follow the proposed new terminology.
The reason that an execution plan should not be called an estimated execution plan is because it is exactly that: an execution plan. The execution plan contains the instructions on how to execute a given query, along with guesses (estimates) of how many rows each section of the plan will have to process, and an estimate of the cost associated with that (represented as a single number for easy comparison). These estimates are used by SQL Server in the compilation process, for comparing alternative plans. They are exposed in the plan to the user because this can help them troubleshoot the root cause when a query is executed with a sub-optimal plan. But the plan that is compiled based on those estimates is a very real plan, and unless there is a reason to recompile and come up with a new execution plan, the run-time engine will execute the execution plan to the dot.
And since the execution plan is the plan that will actually be used (or was actually used), using the term “actual execution plan” is just confusing. It is the same execution plan. The only difference is that some extra data is added. Run-time statistics, collected during the last execution of the execution plan. The term “execution plan plus run-time statistics” is therefore a much better term, since it describes exactly what it is. The operators and properties are the same. The estimated numbers (rows to be processed, cost associated with processing those rows) are the same. But because an actual execution plan is generated after executing the query, counters of how many rows were processed are added. However, even if the actual number of rows is significantly more or less than the estimated number of rows, SQL Server does not reflect that difference in the cost estimates associated with the plan.
When SQL Server Management Studio displays multiple execution plans at the same time, it shows the “Query cost (relative to the batch)” for each plan. This is simply the estimated cost of that plan divided by the sum of all estimated costs. It is very important to remember that, even in an execution plan plus run-time statistics, these numbers are still based on the estimated costs. This can be seen by running the code below in the AdventureWorks sample database, with the option to include the execution plan plus run-time statistics enabled:
DECLARE @SO_num nvarchar(23) = 'SO78971'; SET STATISTICS IO, TIME ON; SELECT SUM(SubTotal) FROM Sales.SalesOrderHeader WHERE SalesOrderNumber <= @SO_num OPTION(OPTIMIZE FOR (@SO_num = 'SO75000')); SELECT SUM(SubTotal) FROM Sales.SalesOrderHeader WHERE SalesOrderNumber <= @SO_num OPTION(OPTIMIZE FOR (@SO_num = 'SO43650'));
Here are captures of the execution plan plus run-time statistics and the STATISTICS IO and STATISTICS TIME results after running this query on my laptop:
As you can see from the above, the second query was estimated to be the cheaper of the two, but was actually the one responsible for most of the execution time and most of the I/O. If you use the percentages in the execution plan plus run-time statistics to decide which query to tune, you will zoom in on the query that is actually running just fine and ignore the one that really needs some attention.
Elements in an execution plan
When you look at the graphical representation of an execution plan, you will see various named icons, connected by arrows. The icons represent the operators that do some work on the data flowing through them. The arrows represent data streaming from one operator to the next; these always point either to the left, or to the left and up.
Each operator, except the top-left one, has a single outgoing arrow (to its left), representing the data it returns to other operators. Operators can have multiple incoming arrows (to their right) for the incoming data. Operators in an execution plan are often called nodes, a term that comes from graph theory.
If you tilt a graphical execution plan 90 degrees, it resembles a tree-like shape. For that reason, the top left node is often called the root. Subsections of the execution plan that converge in a single node are called branches. And operators with no inputs, always on the end of a branch, are called the leaf nodes. Leaf node operators typically read data from disk (or from the buffer pool), whereas other operators get their data from other operators.
Operators and their properties
Each of the icons represents an operator. Operators can be considered as miniature computer programs that are able to do one specific task. They all share a common interface that is used when they are called by other operators. This interface consists of exactly three method calls:
- Init(): Requests that the operator initializes itself, sets up data structures it needs, and prepares to produce data. Second and later calls to Init() during execution of the same plan request the operator to prepare to re-start.
- GetNext(): Requests the operator to return a single row (or batch) of data. The operator will do whatever it needs to do in order to do to create a row and then return it to the caller. The operators use internal data structures to keep track of state, so they can continue where they left off when receiving the next GetNext() call.
- Close(): Requests the operator to clean up any memory it may have used and shut itself down. An operator never receives more than one Close() call during execution of a single plan.
What work exactly is done by each specific operator depends on the operator type (depicted by the icon used and by the name underneath the icon), and on its properties. The properties of an operator can be shown in two ways. Hovering your mouse over the icon brings up a popup window with a selection of the properties. Right-clicking the icon and selecting Properties from the context menu opens the properties window that lists all properties. Once the properties window is open, just clicking an icon changes it to display the properties of that icon.
Although using the popup often can sometimes be more convenient than using the full properties window, it can be risky. Some properties are simply not included in the popup, so you may miss important information if you only look there. Even more important is that there are some properties that may or may not be displayed in the popup. If it is not displayed, that can mean that the property is not present for the operator. But it might also mean that the property has more content than fits in the popup. If the content of a property does not fit, Management Studio will simply leave it out, without any indicator to warn you that this happened. (More recent versions of Management Studio have changed this behavior and will actually display a part of the property, with an ellipsis at the end to show that the full content is not shown in the popup).
Data flows
The flow of data from one operator to another is not explicitly represented in the XML representation of execution plans, but inferred by the relative positions of operators to each other within the XML. Management Studio decodes the XML and interprets these positions when it renders the graphical execution plan, and shows the flow of data by using arrows.
SSMS allows you to inspect the properties of an arrow in an execution plan, just as it does for operators. But because the arrow does not represent any actual object in the execution plan, the properties you see here are not actually the properties of the data flow – they are the properties of the icon to the right of that data flow (the operator where the data flows from). It can be convenient to look at the properties of data flows because they show only a small subset, directly relating to the amount of data flowing between the operators. But you should always be aware that you are actually viewing a subset of the properties of an operator, not the properties of a data flow.
The width of each arrow in a graphical execution plan is a visual indication of how much data flows between operators. Management Studio renders this based on the Actual Number of Rows property when available. If not available, then the Estimated Number of Rows is used instead. Note that recent versions of Management Studio have started to use the Number of Rows Read property, when present, instead. This is very confusing and I have asked Microsoft to revert this change.
Left-to-right, or right-to-left?
One of the questions that is bound to spark some debate in the SQL Server community is: “in what direction should an execution plan be read? Left to right, right to left, or something else?” The question may be simple, but the answer is not.
The data flow arrows in the graphical representation of execution plans always points to the left and up, which appears to be a visual clue that this is the “correct” way to read a plan. And there are indeed many cases (probably even the majority) where following the data as it flows through the operators is the best way to understand how the operators work together to achieve the desired result. But that doesn’t imply that this is the correct way.
When reading the execution plan below and starting at the right (the Clustered Index Scan), it may appear as if this will read all rows in the table, generating a lot of I/O, and then pass all these rows to the Top operator. But that is not how it actually works. As described above, every operator is programmed to produce and return a single row when called by its GetNext() method. That means that, until the GetNext() method of the Clustered Index Scan is called, it will do nothing. The calls to this method come from the other end of the data flow – the Top operator. Which, in turn, is called by the Result operator (called SELECT). So following the order of execution instead of the flow of data, SELECT calls Top, which calls Clustered Index Scan. Clustered Index Scan reads and returns a row, which Top then receives and passes on to SELECT. SELECT receives that row, returns it to the client, and then calls Top again, requesting a second row. But Top is instructed (in its properties) to only return a single row, so instead of calling Clustered Index Scan, it will return an “end of data” marker to the SELECT operator.
SELECT TOP(1) Title, FirstName, LastName FROM Person.Person;
The logic of the execution plan above can never be fully understood when reading right to left only. But many other execution plans are almost impossible to understand when reading left to right, and much easier to grasp when following the data flow instead of the execution order. For that reason, the best possible answer to the question “in what direction should an execution plan be read? Left to right, right to left, or something else?” should be “all of the above and more – whatever helps me understand the logic of this execution plan”.
That being said, the top-left operator is often a good place to start because this is where you find a lot of useful information about the plan as a whole.