Getting Started On Execution Plans
By Aakash Patel, Database Engineer, Rocket Mortgage
This article focuses on preliminary knowledge of SQL Server execution plans. Often, we come across topics on which you can easily find lots of detailed information but not much on how to get started. Here’s my attempt to provide that kickstart for execution plans. For SQL Server beginners, my aim is to help you be comfortable reading execution plans, provide you strong foundation and empower you to reach the next level.
What Is An Execution Plan?
In a traditional programming world, the developer of the code decides the flow of the program (aka algorithm). In SQL Server, this process is little different: the developer has very little (query hints) to no control over the flow of execution.
SQL Server has a declarative language called “t-sql”, through which the developer defines what data is needed, but they don’t decide how to retrieve it. SQL Server has a component called “Query Optimizer,” which decides how to retrieve the required data and also designs the flow of retrieval steps for each query submitted by the user or application. An execution plan is a graphical presentation of this execution flow.
The execution plan gives users insight on what SQL Server is doing “under the hood” to serve the request. So, when you need to figure out why data retrieval is slow or resource intensive, you’ll need to understand the execution plan to understand how SQL Server ran your query. This is an essential skill for performance tuning and troubleshooting process.
Behind the scenes, execution plan information is stored in XML, and it contains the complete information about execution. SQL Server Management Studio (SSMS) and other third-party tools turn this information into a graphical plan, which makes it easy to read and understand. While there are other ways to display execution plan information, for this article, I’ll focus on the most commonly used graphical presentation here.
Where Can You Find An Execution Plan?
There are multiple ways and places you can capture or find an execution plan as briefly described below.
- SSMS — In this article, I’ll demonstrate how to capture and read execution plans using SSMS. I highly recommend that you use the latest release of SSMS to follow along.
- Plan cache — SQL Server reserves a portion of the memory (RAM) to store execution plans. This memory segment is called a plan cache. When any query is submitted, a query optimizer generates an execution plan and saves them in a plan cache (in most cases) for the purpose of reuse.
- Query Store — In SQL Server 2016 and above, every database comes with a Query Store option. Once enabled, every execution along with its execution plan belonging to the database is saved inside the Query Store of that database for a certain number of days depending on the configuration.
- Extended Events or a trace — We can also capture an execution plan using a trace or Extended Events (SQL Server 2008 and higher). This method puts an overhead on the server, so avoid this if you can, and be little cautious if you must.
- Third-party monitoring tools — If you’re using any third-party monitoring tools, there’s a good chance these tools are capturing execution plans for you.
How To Capture Execution Plan Using SSMS
To view an execution plan for the query, you’ll need to have SHOWPLAN permission for each database referenced by the query, as shown in Figure 1. Assuming you’re learning this on your local instance, you shouldn’t run into any permission issues. Always use a test server or your local machine for learning purposes. SQL Server developer edition has always been free — so take advantage of it!
Figure 1 — Permission To View An Execution Plan
SSMS includes three buttons, which can be used to capture the execution plan, as shown in Figure 2.
Figure 2 — Execution Plan Buttons
Display Estimated Execution Plan (Ctrl+L)
When you click on this toggle button, the SQL Server optimizer compiles the execution plan it will use if the query in the current session (or highlighted) was to run. At this point, the query has not been submitted, but it basically gives you a roadmap of the execution steps.
I find this estimated execution plan very useful when trying to understand optimizer behavior on a production server, where running a query may not be feasible.
Include Actual Execution Plan (Ctrl+M)
When this toggle button is on, SQL Server will produce the execution plan along with run-time statistics at the end of the query execution. Run time statistics include information such as how many rows were produced, how much memory and CPU were consumed, how long it took, etc.
Through an actual execution plan, we get the complete picture of why a query ran slow and where it’s spending most of the resources.
Include Live Query Statistics
When this action button is on, SSMS will show a new tab in the result pane, which will show a live execution flow while a query is running. This feature is available in SQL Server 2014 and higher, assuming you are using the latest SSMS. This feature can be useful when dealing with long-running queries to understand how long each step is taking while it’s running.
This feature puts an additional overhead on query performance as it continuously refreshes run-time statistics on the execution plan and, therefore, should be used with extra caution. It also requires server-level permission
(VIEW SERVER STATE)on top of database-level SHOWPLAN permission.
Execution Plan Examples Using SSMS
Now, let’s demo each of the above methods to capture the execution plan. First, we’ll create some data sets, as shown in Listing 1.
Listing 1 — Create Dataset For Execution Plan Demo
Next, select the query in the script shown in Listing 2. Don’t run it yet. Click on “Display Estimated Execution plan” or hit Ctrl+L on the keyboard. You’ll see the execution plan that will be used if the query is executed.
Listing 2 — Capture Estimated Execution Plan And Actual Execution Plan
Next, click on that “Include Actual Execution Plan” button or use the keyboard shortcut Ctrl+M and then, this time, you’ll want to run the query. You’ll see results of the query, execution plan optimizer used, along with run-time statistics.
Next, to see the live query statistics, we will use queries shown in Listing 3. As this option is used to see live query progress, we are first populating the table with 50,000 dummy records using the first query in Listing 3. Now click on “Include Live Query Statistics” and run the second query shown in Listing 3.
Listing 3 — View Execution Plan While It’s Running
Turn off Live Query Statistics on SSMS and drop the table we created for the demo, shown in Listing 4.
Listing 4 — Drop The Demo Table
Execution Plan Components
The execution plan is a graphical presentation of execution steps in the form of a tree, as shown in Figure 3.
Think of an execution plan like a complex recipe. It displays a multistep process (as shown in Figures 3 and 4), in which each step (or node) represents one discrete part of the process. Each step passes on food (or data) to next step, which leads to the dish (or data ) the client ordered.
Figure 3 — Execution Plan Example
Figure 4 — Cooking Process Analogy
An execution plan represents a multistep computer program, which produces the dataset the user requested. In an execution plan, each node represents a mathematical function or an input/output (I/O) operation.
The execution plan has three major components:
- The top left node — This icon represents the end user who’s requesting the data operation.
- Operators or nodes — Each icon on this tree is called an operator or a node. Each operator is designed to perform a specific compute or input/output (I/O) operation built on an algorithm designed by Microsoft engineers, such as sorting, aggregating, reading a data set, etc. It’s a small computer program that consumes data from its right-hand side operator(s), processes and outputs the data to its parent (left-hand side) operator.
- Arrows — The arrows between the operators show the direction of data movement.
How To Read An Execution Plan
In this section, I’ll share some important details to pay attention to in reading the execution plan.
Execution plans should be read from right to left and from top to bottom. Data flows from right operators to left operators, but control flows from left to right. This means the demand for data is always initiated by left-side parent operator, and a right-side operator(s) always serves the demand of the parent operator to its left.
The cost percentage associated with each operator (shown in Figure 5) is an estimated cost, even on actual execution plans and therefore it may not be ALWAYS right. This cost percentage is mostly reliable only if the cardinality estimates are correct, which more or less depends on having accurate statistics.
Figure 5 — Operator Cost
When looking into the problematic part of the execution plan, you should generally start with the operator that has the highest cost percentage, assuming the cardinality estimates are correct.
Actual Versus Estimate
In the actual execution plan, you’ll see an annotation of “xx of yy” on a bunch of operators (on the latest SSMS), as shown in Figure 6. The first number in that annotation represents the actual number of rows read/processed by that operator, and the other number represents how many rows were estimated to be read/processed by the optimizer. Ideally, these numbers should be as close as possible. A large gap between them could lead to an inefficient execution plan.
Figure 6 — Rows Estimation
In the actual execution plan, you’ll notice an elapsed time associated with most of the operators, as shown in Figure 7. This denotes the actual time consumed (excluding wait stats) by that operator in second(s).
Figure 7 — Time Consumed By Operator
Operators are the building blocks of the execution plan. Unfortunately, there’s no trick to learning about all the operators other than comprehensive study. You need to read and learn about the most common operators before you can effectively read the whole execution plan and associate t-sql script with the operators.
When it comes to decoding an operator, in addition to checking the Microsoft docs, I usually check if any of these SQL Server MVPs have written anything about it:
- Hugo Kornelis — SQLServerFast Operator List
- Paul White — Page Free Space or SQLPerformance.com
- Fabiano Amorim — Microsoft MVP profile
Operator properties are the predominant part of an execution plan. They make execution plans so worthy and important. When you hover over any operator, you’ll see a tool tip pop-up showing associated properties, as shown in Figure 8.
Figure 8 — Operator Tool Tip
For more detailed information on properties for each operator, you can right-click on the operator to view the properties, as shown in Figure 9.
Figure 9 — Operator Properties
Properties of each operator, including the arrows between operators, have the most important information. Based on this, we can understand and analyze the execution plan and decide how we can improve the query performance. It takes a lot of patience and experience to read these data in an effective manner.
My recommendation is to experiment in a non-production environment in order to understand how a small change in code and/or an index structure can change the shape of the execution plan and properties associated with the operator(s).
At a high level, the size of the arrow simply represents the amount of data flowing.
In an estimated execution plan, arrow size is based on the estimated number of rows to be produced by the operator.
In an actual execution plan, the arrow size is based on the number of rows produced by the operator. However, it’s a little different for scan, seek and lookup operators, in which arrow size is based on the number of rows read by the operator.
The top left operator on the execution plan represents an end user, which is an operator with no real operation associated. That being said, as shown in Figure 10, properties on this operator contain very rich information about the plan, including memory grant, compile time, CPU time, duration of the execution, wait stats, trace flags accounted, ANSI setting information and more.
Figure 10 — Plan Properties
Sometimes, you may see a yellow warning sign on the operator, as shown in Figure 11. These signs are meant to quickly draw your attention to a problematic part of the execution plan. When you hover over these operators, you’ll also see a detailed message in the pop-up window. (This can also be seen in the properties window.)
Figure 11 — Warning
You may notice two little arrows on top of the execution plan operator(s), as shown in Figure 12. This indicates more than one thread will be or was used in parallel by that operator to perform the given task (but this is one exception).
For a big task, parallelism can speed up a process. In the example of a cooking recipe, this would mean that two cooks could be working together on the same task at the same time to speed up the task. Parallelism is an in-depth topic, but my goal here is to show how to identify it through the execution plan.
Figure 12 — Parallelism
Starting with SQL Server 2016 SP1, in the actual execution plan, wait stats (shown in Figure 13) are exposed through the properties of the top left node. These properties include the type of wait, wait count and time spent waiting during the entire execution. They provide the insight into query slowness and potential fixes. More often than not, associated wait types are harmless, but it’s good to know where to look during performance tuning and troubleshooting.
Figure 13 — Wait Stats
Estimated Subtree Cost
Every operator on the execution plan has the estimated subtree cost property, as shown in Figure 14. This is a cumulative estimated cost for all operators on the right-hand side of the subtree, including its own estimated cost. So, for the right most operator, estimated subtree cost is equal to estimated operator cost. This cost is just a number without any unit (it used to represent time but doesn’t anymore — long story). This property can be quite useful during the optimization process and when comparing two execution plans.
Figure 14 — Estimated Subtree Cost
Execution plans are vital for understanding SQL Server optimizer. Through this lens, you can decode optimizer behavior.
In this article, I covered demos on how to capture execution plan through SSMS, where to begin when reading execution plans and the components of execution plans. Hopefully this article provided you a jump-start and boosted your curiosity about execution plans. To learn more, check out these resources:
- “Execution Plan Basics” by Grant Fritchey
- “SQL Server Execution Plans, Third Edition” by Grant Fritchey
- Solar Winds SentryOne (Free tool for effectively reading execution plans)
Aakash has more than 10 years of experience working with SQL Server. He holds a Master of Science in computer science from New Jersey Institute of Technology. His passion is tuning SQL Server to get the best out of the assigned resources and providing a flawless experience for the end user. He loves researching and designing best practices for set ups and configurations for SQL Server. Lately, he has been expanding his knowledge on other database technologies on AWS.