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.
Courtesy : http://www.mssqltips.com/sqlservertutorial/2252/estimated-vs-actual-query-plan/
Nice one!!
ReplyDeleteI have a few doubts though.
This topic is to help developers who are using SQL on server side programming ?
If you are an client side programmer, do we get to use this.
Because we don't build static databases, right ?
OR Did I miss the entire point ?
this is to see how our SQL query can perform
ReplyDeleteEstimated plan is based on the existing statistics available for each table and index and so on
Actual is what is the original plan used by the SQL engine when it actually ran the query.
I have talked about only SQL server here.
however the concept should be similar to other RDBMS aswell.
NO SQL , I have no clue, and if someone have any idea, please do share as a new post