By: Tibor Nagy
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
- How to: Display the Estimated Execution Plan article on MSDN
- How to: Display the Actual Execution Plan article on MSDN
Last Update: 8/15/2011
