Estimated vs. Actual Query Plan


By:

Overview

There are two types of Graphical Execution Plans: estimated and actual.

Explanation

Estimated vs. Actual Query Execution Plans

The Estimated Query Plans are created without execution and contain an approximate Execution Plan. This can be used on any T-SQL code without actually running the query.  So for example, if you had an UPDATE query you could get the Estimated Query Plan without actually running the UPDATE.

The Actual Query Plans are created after we sent the query for processing and it contains the steps that were performed.

Usually the Estimated and the Actual Plans have similar graphical representation, but they can differ in cases where the statistics are outdated or the query involves parallelism, etc...  Additionally you cannot create Estimated Plans for queries that create objects and work with them (i.e. a query using a temp table).

It is better to use the Estimated Execution Plan when the query execution time is very long or it may be difficult to restore the database to the original state after the query run.

You can display the Estimated Execution Plan in SQL Management Studio by pressing CTRL + L in the query window or by clicking the Display Estimated Execution Plan button in the SSMS menu icons as shown below.

You can display the Actual Execution Plan in the results set by pressing CTRL + M or by clicking the Include Actual Execution Plan button in the SSMS menu icons as shown below.

Both of these options are also accessible from the Query menu in SSMS as shown below.

Once you turn on one of these options it will stay in affect for all queries that are run in that query window.  Also, you can only select one of these options, it is not possible to have both turned on.

Additional Information


Last Update: 8/15/2011




Comments For This Article




Monday, January 25, 2016 - 3:46:45 PM - Chris Back To Top (40498)

Definitely a big help!!



download














get free sql tips
agree to terms