Actual vs Estimated Execution Plans

July 30, 2017 Execution Plans 441 Views


There are two types of Execution Plans; Estimated and Actual Execution Plans. The biggest difference between the two types of execution plans is the time SQL Server generates the plan.

Estimated Execution Plan

Estimated Execution Plan is a plan that SQL Server generates without executing the query, hence the name Estimated. It is similar to when you need to build a house and ask the builder for an estimated price. The builder does not build the house, however, comes up with an approimate price. SQL Server is also similar, you can ask SQL Server for an estimated execution plan. Then, SQL Server tries to build an estimate of the execution without executing the query.

Actual Execution Plan

On the other hand, Actual Execution Plan is the plan that is generated when SQL Server really executes a query. Actual Execution Plan specifies the route that SQL Server actually went through to execute the query.
When you ask SQL Server for the Actual Execution Plan, it is similar to ask the builder to build the house and come up with the actual price. SQL Server, in this case, actually executes the query and outputs the path it went throught to execute the query.

Another difference between the two types of Execution Plans

So, another big difference between the Estimated and Actual Execution Plans is about query execution. In case of Estimated Execution Plan, the query is not run at all and is just SQL Server's estimation of the query. However, in case of Actual Execution Plan, the query is really run and SQL Server provides the details about the query execution.
The two types of execution plans above have a lot in common in terms of the structure and appearance. However, it IS possible that the two types of Execution Plans differ.

What is Estimated Execution Plan good for?

Estimated Execution plan is very useful for optimizing queries that you really cannot run on the database. Imagine you need to check an UPDATE statement for performance. You obviously cannot run the UPDATE statement on production server just for diagnosis purposes. In that case, you can diagnose using the Estimated Execution Plan, as it does not execute and consequently does not touch the data.

How do you rate this topic?

Further reading:
What are the components of an Execution Plan?
Blocking vs Non Blocking operators
Some queries do not appear in Estimated Execution Plans.
Actual Execution Plan hides some queries!!!
What is an Execution Plan ?