The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

Re-thinking Types of Execution Plans

A recent discussion on Twitter about the different types of execution plans - specifically estimated vs. actual - got me thinking about how we “define” plans in SQL Server, and I realized we really need one more type of plan. The estimated plan definition needs to be further refined. Rather than two types of plans, I propose we think about plans as one of three types:

  • The estimated plan 
  • The used plan
  • The actual plan

Let me explain…

Estimated Plan

If we look at the Microsoft documentation for an estimated plan, it specifically references generating “…graphical estimated execution plans by using SQL Server Management Studio.”  This estimated plan is the one you see in SSMS when you select Query | Display Estimated Execution Plan or use SET SHOWPLAN_XML ON. With that option, the Query Processor (QP) goes through compilation and optimization and generates a plan, but does not execute the query and does not put the plan in the plan cache (or in Query Store, if enabled). It just displays the plan and includes attributes like estimated row count, etc. based on the input parameter(s) used.  This is what the QP thinks it will do. Further, if there is already a plan in the plan cache, the QP doesn’t care, it still goes through optimization and compilation.

I liken the estimated plan to the vision you get in your head when you ask the waiter to describe what a dish looks like, or its size.  The estimated plan is often the same as the actual plan, but not always, and nowhere does Microsoft guarantee it will be. 

DEMO: Estimated Plan

The demos will all use the same two queries, executed against a copy of the WideWorldImporters database running on SQL Server 2017 CU16 in compatibility mode 140.  One query references a User Defined Function, which is created first.

IMAGE 1

The T-SQL below will generate estimated plans for both queries – this is the same as highlighting the queries and selecting Query | Display Estimated Execution Plan.

Looking at the estimated plans individually, if we look at the properties for the Index Seek in the first query’s plan, you’ll notice the attributes are only estimates (e.g. Estimated I/O Cost, Estimated Number of Rows).

When looking at the second query’s plan, notice that it has two parts.  The first (Query 1) accesses the Application.People table, and the second (Query 2) accesses Sales.Orders and Sales.OrderLines, which are referenced in the Sales.CountProductsSold function.

Used Plan

The used plan is the execution plan that you retrieve from the plan cache (sys.dm_exec_query_plan) or Query Store (sys.query_store_plan) and it is the plan that was generated by the QP and was used the first time the query was executed, and then placed in the plan cache and used for subsequent executions. The used plan contains attributes such as estimated rows, just as the estimated plan did, and those are based on the input value(s) used for that initial execution.

I equate the used plan to a picture of a dish you would see on a menu at a restaurant. The used plan – whether retrieved from the plan cache or Query Store – is very often the same as the estimated plan, but examples of where it’s not the same include different SET OPTIONS between an application and SSMS (notably SET_ARITHABORT), and changes in statistics between when the estimated and actual plans are generated.  

DEMO: Used Plan

The queries first need to be executed to get the plan into cache, and the T-SQL below includes a GO 5 to run the queries multiple times.

The output confirms that each query executed 5 times.  Clicking on the link for the first plan, and examining the properties for the index seek, we see that we only have estimates as we did for the estimated plan.  This plan also looks exactly like the estimated plan.

Looking at the used plan for the second query, it looks similar to the first part of the estimated plan, but there is no second part.  In fact, there are no operators that access Sales.Orders and Sales.OrderLines – that access is hidden in the Compute Scalar operator. 

This is a classic example where the estimated plan is different than the used plan because a Scalar User Defined Function (UDF) is referenced.  In SQL Server 2019 these Scalar UDFs can be inlined, which creates a different used plan than shown here, and the estimated and used plans are similar, unlike what is seen in this example.

One note regarding queries with RECOMPILE: If you’ve ever gone hunting for a plan in cache and not found it, understand that can occur if the query has the WITH RECOMPILE hint. Those queries are not added to the plan cache, but you can find them in Query Store.  The query below is one you can use to find used plans from Query Store (assuming you have it enabled):

Actual Plan

The actual plan is the one generated when you execute a query in SSMS and enable the actual plan via Query | Include Actual Execution Plan.  The actual plan can also be captured using Extended Events with the sqlserver.query_post_execution_showplan event, but this is not recommended due to performance overhead. The actual plan contains attributes about estimates and actuals, and I associate the actual plan with the plate of food that shows up in front of you from the waiter or waitress. It may not quite match the vision in your head (estimated) but it usually matches the picture on the menu (used plan). 

DEMO: Actual Plan

The T-SQL below will generate actual plans for both queries and display a link in the Results pane that can be clicked to open the plan.  This is the same as selecting Query | Include Actual Execution Plan, and then running the queries.

The actual plan for the first query has actual information in the operator properties, as well as the estimated information.  When troubleshooting performance, discrepancies between the two is often indicative of a problem.

 

The actual plan for the second query is the same as the used plan, and note that even in the actual plan, there are no details in the properties of the Compute Scalar operator that give any information about the UDF used in the query.  Again, this improves in SQL Server 2019 with compatibility mode 150 or the TSQL_SCALAR_UDF_INLINING database scoped option.

Additional Options

There are additional options for plan viewing available in the latest versions of SQL Server.  With SQL Server 2014 and higher you can view Live Query Statistics for a query, which I think of as the actual plan in action.  When you enable it in SSMS (Query | Include Live Query Statistics) you can see the flow of data across the operators, and see what’s taking the longest.  With SQL Server 2016 SP1 and higher you have additional capabilities for looking at query information in-flight using sys.dm_exec_query_profiles (note this also requires trace flag 7412 or enabling the query_thread_profiler event with Extended Events).  Finally, in SQL Server 2019 we will have the option to look at the most recent actual execution plan for a query using the sys.dm_exec_query_plan_stats DMV.  This is an actual plan that you can view without enabling anything extra in SSMS or setting up an event session, and it also falls into the Actual Plan category.

Summary

While we’ve been referring to plans as estimated vs. actual for as long as I can remember, I think it’s time to separate out those “types” a bit more.  Specifically:

  • Estimated Plan – the plan you generate in SSMS using Display Estimated Execution Plan
  • Used Plan – the plan you retrieve after a query was executed, from the plan cache or from Query Store
  • Actual Plan – the plan you generate in SSMS using Display Actual Execution Plan, capture with Extended Events (though not recommended), view with Live Query Statistics, or view in sys.dm_exec_query_plan_stats starting with SQL Server 2019 

Adopting this terminology is a slight shift in the way we have used the terms historically, but I think it helps better explain to colleagues what plan you’re actually viewing.

Want to learn more about execution plans?! Attend Erin's session "Understanding Execution Plans" at the PASS Summit, part of the Developer Learning Pathway.

Erin Stellato
About the author

Erin Stellato is a Data Platform MVP and lives outside Cleveland, OH. She has almost 20 years of technology experience and has worked with SQL Server since 2003. Her areas of interest include performance tuning, Query Store, Extended Events, and helping accidental/involuntary DBAs understand how SQL Server works. Erin is an active member of the SQL Server community, has volunteered for PASS at the local and national level, and is a regular speaker at conferences including the PASS Summit, SQLIntersection, and SQLBits. Outside of time with family and friends, she enjoys running, spinning, movies, and all things chocolate.

Please login or register to post comments.

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids