I was recently invited to participate in big discussion on one of the online forum, the topic was Actual Execution Plan vs. Estimated Execution Plan. I refused to participate in that particular discussion as I have very simple but strong opinion about this topic. I always use Actual Execution Plan as it is accurate.
Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running query. I just run query and have correct and accurate Execution Plan.
Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M
You can use SSMS to include the execution plan as well.
Reference : Pinal Dave (https://blog.sqlauthority.com)
30 Comments. Leave new
Thanks Dave. I didn’t know that there are two options but I have always used actual execution plan.
I am wondering if you could write about how to use query plan to tweak a query for better performance?.
Thanks in advance.
The problem with using Actual Execution Plan
and SET STATISTICS PROFILE ON is you don’t get the results until the query has completed.
This can be a problem when troubleshooting performance problems on long running queries. If the query you are trying to analyze doesn’t return the result set for 2 hours, then you have to wait 2 hours to see the actual execution plan.
Using show estimated execution plan give you the opportunity to see a best guess at what the SQL Engine is going to do when it executes the query, and allows the developer to see if what they have written is the best it can be.
So, while I agree that the Actual Execution Plan is ideal, it is not fair to tell others in the community that the Estimated Execution plan should not be used.
It is a valuable resource in the tool kit of SQL Developers and should not be overlooked when information about query processes is needed on long running queries.
Dave,
I find this post very useful. I now realize that I should have used Actual plan. I was using estimated plan, bad very bad.
thz.
Estimated execution plans will be useful when we are facing any query time out issues from the application.
As we all know that if we set showplan_all on we will get the estimated execution plan and which will be very useful some times.
Hi,
Is there any way to get execution time for stored procedure in sql server 2005.
My requirement was i have display that execution time as waiting time..so tht user will wait till tht time.
HI Dave,
I was not aware of the 2 Execution Plans. After seeing yours only i got to know that there are 2 types.
Thanks
Dave,
Thanks for the useful information..
–Regards,
Krishna
Hi,
Can i see execution plan for Stored Procedure and triggers.
Thanks
Could you please tell me how we could recognize that the statistics are not updated by looking in to the actual execution plan of the query.
What is Bookmark Lookup and RID Lookup.What are the difference between them.Are the performance booster or performance Killer.How we can remove those from our query plan.
Gud one
Dear Dave,
Thanks for the useful information..
Regards,
ShyamSundar.S
You are great!
Very Good.
Lot of information provided.
Thank u friend for given usefull information provided abt Exec plan..
Hello Dave,
i am admire the way you are gving your effort here.
I use to go through your article and always found it usefull.
i will keep you writting on any topic of SQL Server
Ranjeet
Hi Dave
I like your articles and the way you share with us.
Adding examples also will be helpful for the readers.
Prasad
Is it possible to get query plan by query?
for example in postgres it look like
explain select now();
but in ms sql i can’t find such a thing like explain.
thx.
Hi All,
Please can any one tell how to transfer the jobs through the script ?
Can someone please tell me if I want to figure out difference the costs of actual and estimate executions around how much data records do I need to have? I have about 30 records but it does not show any difference?
hi dave,
thanks alot for sharing a lot of information.
after the query is executed . it will show that 1 row(s) is effected (this is after the actual number of rows effected).
what for this is coming?
what is happening there?
can u please explain this?
this is when i am using actual execution plan in ssms.
Thanks in advance.