Performance tuning is an incredibly important task within any given day of a Data Professional. Knowing how to use and leverage the tools that SQL Server provides can give your troublesome query a much-needed boost in performance.
What is an Execution Plan?
Simply put, an execution plan is the Query Optimizer’s attempt to calculate the most efficient way to execute the query that you have submitted. Instead of using trial and error by submitting query after query in order to determine the bit of SQL code that is causing the performance bottleneck, an execution plan can tell you how it will be (or was) executed. There are two distinct types of Execution Plans, Actual and Estimated. The Estimated Execution Plan is the best guess plan by the Query Optimizer. The Actual Execution Plan will show you what actually happened during execution of the query.
The Query Optimizer
The Query Optimizer is a software that models how the Database Engine works. The optimizer reads through your request and determines a cost-based plan which is based off on indexes, JOINs, WHERE clauses, and other operations that you are trying to perform. The optimizer can distinguish between a trivial plan (simple select statement) and a complex plan basted on your request. Because building out execution plans is taxing to the Optimizer, it will try to save plans for re-use later by caching them. When caching certain plans, the Actual and the Estimated Execution Plans may differ, but generally, this doesn’t happen.
Estimated Execution Plans
There are several ways to get to the Estimated Execution Plan
- Click on the ‘Display Estimated Execution Plan’ icon on the tool bar (Next to the Parse Query Check Mark)
- Right click the query window and choose ‘Display Estimated Execution Plan’ option
- Hit CTRL+L
Actual Execution Plan
To get to the Actual Plan:
- Click the ‘Include Actual Execution Plan’ icon on the toolbar
- Right click the query window and choose ‘Include Actual Execution Plan’ option
- Hit CTRL+M
Interpreting the Plan
While the query plan is helpful, it’s also slightly difficult to read. There are roughly 80 operations that will represent the different actions making up a query plan. The SELECT icon is very common and usually can be ignored. It’s the final result and has to do with formatting the output. A Table scan is when the Optimizer must walk through an entire table and either return everything it finds, or identify the appropriate rows to return to the output. The more the table grows, the more taxing this operation becomes.
Query Execution Plans are typically read right to left top to bottom. There is also arrows between operations which represent the data flowing between the objects. The thickness of the arrow also indicates how much data is being processed.
The percentage displayed below each of the operations is the estimated execution time that will take for the operation to complete. The cost associated to the operation is a ration so 100% cost is relative to the batch. The table scan is doing nearly all the work for the plan.
Each operation will allow you to hover over it and display a ToolTip. This ToolTip contains information about how the optimizer generated the following. When taking a look at the Estimated Execution Plan of the Select operation, you see the following image.
Looking at the plan we see several rows:
- Cached Plan Size – This indicates the amount of memory the plan believes the query will take up in the cache. This number can help in troubleshooting performance as high memory loads can indicate a poorly indexed table.
- Estimated Operator Cost – This is the Cost relative to the bath. Remember this is a ratio so it can be 0 and still actually have a cost to it.
- Estimated Subtree Cost – This number represents the amount of time that the optimizer thinks SQL Server will take to populate a result set.
- Estimated Number of Rows – Calculated based on the statistics available to the optimizer. This is just a best guess at this point.
Below this information we see what query the estimation is basing the cost off of. Take a look at the below image for the Estimated Execution Plan for the Table Scan.
Each operator will have a different set of data based on what the query will do.
- Physical Operation – This indicates the operation the execution plan would perform.
- Logical Operation – Like the above, the execution plan is demonstrating what the operation will behave like.
- Estimated Execution Mode – This operation specifies how the optimizer should execute the operation. It’s similar to the Logical Operation.
- Storage – This shows how the optimizer will store the information that you are extracting.
- Estimated Operator Cost – This is not an actual cost, but it indicates what it will cost to run relative to the other operations.
- Estimated I/O Cost – Cost against the input/output of the result set. Like Operator and CPU costs this is relative to the other operations and can be used to determine which operation is causing a bottleneck.
- Estimated CPU Cost – Cost associated with the CPU that it will take to execute the operation.
- Estimated Subtree Cost – the section of the execution tree that is currently being processed (read right to left, and top to bottom)
- Estimated number of Executions – How many executions the optimizer can perform in a single batch.
- Estimated Number of Rows – The number of rows that the optimizer believes will be returned.
- Estimated Row Size – the size of storage each row will take.
- Ordered – Determines if the data needs to work in an ordered or not ordered state. This could possible cause slowness if the result set is large.
- Node ID – This designates the order in which this operation was executed. It is read left to right instead of right to left.
Then you see the Object and Output list. These are the table and query that you are executing.
You can right click any icon and select properties to get a detailed list of each operation. Most of the information is like what you’ve already seen in the ToolTip, but some of it can be new. We won’t go into each row here, but know that you can get an extended look if necessary.
Hopefully this provided you with a baseline on how to start using Execution Plans to improve your query performance. When used correctly they can be vital tool for any Data Developer or DBA, and once you get past the initial information overload you can see that they are quite helpful.