How to Generate an Execution Plan in SQL Server

SQL query execution plans are generated when a query optimizer determines the most efficient way to deliver the data requested by a query. These plans are a representation of  the database operations a query is running, showing which objects the query uses, the types of uses, and how they are being used.

It’s common knowledge that poorly performing queries are a key indicator that something is amiss within a database. So looking “under the hood” at a query’s execution plan is one of the best ways to determine the cause of the performance issue. 

The information available in a SQL execution plan is invaluable for DBAs troubleshooting SQL Server query performance problems because it allows them to get to the root cause of the issue and tune the query as needed.

Examining SQL execution plans is one of the first steps in performance tuning because the plan clearly highlights the most likely sources of trouble within a query, including expensive operators, an unusual number of records flowing between operators, and extra operators.  

DBAs also benefit from execution plan warnings that alert users about issues such as tempdb spills and missing indexes.

Types of SQL Execution Plans

There are two main types of SQL execution plans: estimated execution plans and actual estimation plans.

Estimated execution plans are best described as a sort of weather forecast. As the name implies, estimated execution plans use approximate calculations, statistics, and other parameters to estimate steps that may be followed by the SQL Server Engine to execute the submitted query after parsing the query. 

You don’t need to execute the query to generate this type of execution plan, so it is good for complex queries for which generating an actual execution plan would take a long time.

Actual execution plans execute the submitted query and then display the steps that occurred during execution. This type of plan displays real calculations and the actual (not estimated) steps followed by the SQL Server Engine. 

Because this type of execution plan actually runs the query, it is a good choice for troubleshooting query performance issues.

How to Generate a SQL Execution Plan

Hackernoon provides a succinct description of how to generate estimated and actual execution plans. The process can be boiled down to the following steps:

Generate an estimated execution plan

• Go to the database
• Highlight the query
• Click “Query”
• Click “Display Estimated Execution Plan,” or use the shortcut Ctrl + L

Generate an actual execution plan

• Go to the database
• Highlight the query
• Click “Query”
• Click “Include Actual Execution Plan,” or use the shortcut Ctrl + M

You will find a detailed explanation of how to generate estimated execution plans and actual execution plans in the official Microsoft documentation.

Why Estimated and Actual SQL Execution Plans May be Different

Generally, estimated and actual execution plans will be similar. However, there are occasions when they are different. This is normal and occurs because the database statistics and the actual data are different. These differences may be introduced through several channels:

Parallelism

Plan cost can cause two execution plans to be created for a submitted query. The SQL Server Engine will choose whether or not to use the parallel plan, so it’s possible for one plan to use the parallel plan and the other to use the serial plan.

Stale statistics

When you insert data into and delete data from tables and indexes or alter the table or the index schema, statistics will change. If the statistics aren’t updated regularly, the actual execution plan will be different from the estimated execution plan.

Invalid estimated plan

If a query contains a statement that requires a temp table that only exists if the query is executed, generating an estimated execution plan will create an error because no query has been executed. The same query would run fine in the actual execution plan because the query was executed.

SQL execution plans are a valuable resource for troubleshooting SQL Server performance issues. Knowing how to generate both estimated and actual SQL execution plans is an essential tool for any DBA tasked with tuning queries for optimal performance.