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.
Simon,
What you have said is correct. And as I said Estimated Execution plan may not be accurate and I do not use it.
Community is smart and I have explained them both the method and explained my preferred method. I think it is totally fair and appropriate to tell community what my opinion is. I will continue to do so.
As I said earlier, I do not want to end up here in big discussion and I have strong opinion about Actual Execution Plan.
Regards,
Pinal Dave ( https://blog.sqlauthority.com/ )
Pinal Dave,
I have a different opinion
When everything goes awfully wrong (which it does), comparing the estimated plan vs. the actual plan usually gives me useful hints of where the query optimizer is screwing things up. I find this to be more useful (and more disturbing) when the estimated executions differ between the estimated and the actual plan. I give you that this situation doesn’t happen a lot (as far as I know it shouldn’t happen at all) but – believe it or not – it does.
That’s why checking BOTH plans may be necessary, and – humble opinion – that’s the reason they are BOTH there.
In any case, I use computers mostly to watch anime, and DB administration, Web Mastering and SOA programming are just an incidental hobby for me, so I don´t recommend anybody to take my opinions in account. In fact, I think nobody should read this.
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
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 ?