SQL Server: Estimated vs. Actual Execution Plans

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Let’s assume you are constructing a new house, and you have a contract with a builder. After thoughtful examination of the area, height, and materials needed, he estimates it will take 120 days to build a new home. After constructing the home, it actually took 200 days to built it.

There is a difference between his estimation and the actual result. This applies to query execution plans in SQL server as well, specifically Estimated vs. Actual execution plans.

This tutorial contains an Estimated execution plan as well as an Actual execution plan in SQL Server.

Estimated vs. Actual Execution Plans: What is an execution plan?

The execution plan is a query optimizer’s attempt to find the least cost-effective and efficient way to execute a T-SQL query. To produce an execution plan, SQL server uses different algorithms, statistics, permutations, and combinations of joins and indexes to generate an efficient execution plan for a query.

The query optimizer always tries to generate an execution plan which will help to execute the query faster with less CPU and I/O utilization.

It can show that a table is performing a table scan because of a missing index. To start with performance problems, most of the time we need to look at the execution plan to get a general idea about where the query is lagging behind.

Estimated Execution Plan

1.An estimated execution plan is generated without actually executing the query. It is based on available statistics on indexes.

2.Estimated execution plans are output from the query optimizer. Hence, it is considered as a logical plan.

3.The estimated execution plan is always the same as the actual execution plan, but it can differ due to statistic that are not updated, changes in underlying table structure, huge insert/delete/update operations, missing indexes, and other factors.

4.It may fail to generate an estimated execution plan if a procedure involves creating temp tables or table variables. Because an estimated plan query is not executed, temp tables are not considered when producing an estimated execution plan.

Actual Execution Plan

1.An actual execution plan is generated after executing the query. It is more informative and reliable as it is based on actual execution not on estimated statistics.

2.An Actual execution plan may take more time to generate if your query is complex as it has to execute the query to generate a plan. If you have got a complex query, it is better to check the estimated plan first.

3.Working with an actual execution plan is reliable while troubleshooting performance related problems, as it gives us the actual, correct information.

Getting an Estimated Execution Plan

Various options are available to generate an estimated execution plan.

1.Select a query. Right-click on a query window, and select Display Estimated Execution Plan.

2.Click on the Display estimated execution plan option in the main toolbar.

3.Select a query, and press the CTRL + L keyboard key combination.

Getting an Actual Execution Plan

1.Click on the Actual estimated execution plan option in the main toolbar.

2.Select a query, and press the CTRL + M keyboard key combination.

Example:

An Estimated plan cannot be obtained for queries involving temporary tables, as a query is actually executed by query engine to get the estimated execution plan.

Select the query. Click on Display Estimated execution plan, and you will get an error.

Invalid object name ‘#TempTable’.

USE master

SELECT *
INTO   #TempTable
FROM   master..spt_values;

select * from #TempTable;