Estimated Execution Plan VS Actual Execution Plan

I my previous post i have explained some basic details of execution
plans and its types.Well in this post we are drilling deep into the
topic of execution plans.

1.Execution plans can help us in troubleshooting queries which are not performing well as per our requirement.

2.Two types of execution plans are there. They are

a)Estimated Execution Plan.
b)Actual Execution Plan.

3.Estimated Execution Plan is the plan that represents the output
from the optimizer.The operators, or steps, within the plan will be
labeled as logical, because they’re representative of the optimizer’s
view of the plan.

4.Actual execution plan is the plan that shows what actually happened when the query executed.

5.How to get these plans : Go to MS SQL SERVER management studio.
-> Click Query menu -> Select Display Estimated Execution Plan,
-> Include Actual Execution Plan.

6.Short cuts for the same are CTRL + L for Display Estimated Execution Plan and CTRL + M for Include Actual Execution Plan.

7.You must be wondering why can’t we have only one plan : Well please read below to clear your thoughts.
The difference between the plans is differences between the statistics
and the actual data. This generally occurs over time as data is added
and deleted.
This causes the key values that define the index to change, or their
distribution (how many of what type) to change. The automatic update of
statistics that occurs, assuming it’s turned on, only samples a subset
of the data in order to reduce the cost of the operation. This means
that, over time, the statistics become a less-and-less accurate
reflection of the actual data. Not only can this cause differences
between the plans, but you can get bad execution plans because the
statistical data is not up to date.

8.If you are using actual execution plan and you have set statistics
profile on then you dont get the results till the query has
completed.This will create problems when troubleshooting performance
problems on long running queries.
If the query you are trying to analyze doesn’t return the result set
for say x minutes ,then you have to wait for x minutes to see the
actual execution plan.

9.If you are using estimated execution plan , it gives us the plan
what the SQL Engine is going to do ( How it will execute the query )
when it executes the query and allows the developer to see if what they
have written is proper as per their needs.

10.We can use estimated execution plan in case of long running queries.

11.One should always use actual execution plans as they are accurate.Well this is my personal opinion.

Bye friends for now.Cheers,

Pawan Kumar

Pawankkmr@hotmail.com