SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan

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 (http://blog.SQLAuthority.com)

About these ads

41 thoughts on “SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan

  1. 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.

    Like

  2. 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.

    Like

  3. 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 ( http://www.SQLAuthority.com )

    Like

    • 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.

      Like

  4. 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.

    Like

  5. 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.

    Like

  6. 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.

    Like

  7. 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.

    Like

  8. Pingback: SQL SERVER - Stored Procedure WITH ENCRYPTION and Execution Plan Journey to SQL Authority with Pinal Dave

  9. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  10. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 2 Journey to SQL Authority with Pinal Dave

  11. 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

    Like

  12. 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.

    Like

  13. 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?

    Like

  14. 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.

    Like

  15. Hi Dave,

    I like your forum and I have been referring it for the past 2 years.
    My question to you is..
    How can we find in an execution plan if a where clause is non-sargable(functions on columns) and it is not making use of the indexes.

    Thanks in advance.

    Like

  16. How can use “display estimated query plan” and “Include actual execution plan” in MySQL?

    Could anyone guide me how to achieve these two in MySQL?

    Like

  17. Pingback: SQL SERVER – Identify Columnstore Index Usage from Execution Plan « SQL Server Journey with SQL Authority

  18. Pingback: SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 2 « SQL Server Journey with SQL Authority

  19. Pingback: SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 3 « SQL Server Journey with SQL Authority

  20. Pingback: SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 4 « SQL Server Journey with SQL Authority

  21. Pingback: SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 5 « SQL Server Journey with SQL Authority

  22. Pingback: SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1 and Included Columns Col2 and Col3 – Part 5 | SQL Server Journey with SQL Authority

  23. Pingback: SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 6 | SQL Server Journey with SQL Authority

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

  25. Pingback: SQL SERVER – Simple Puzzle with UNION – Part 2 | Journey to SQL Authority with Pinal Dave

  26. Hi Pinal sir, I want to fire a small query frequently, that returns 1-2 rows only, individually not using any Execution plan.
    How can I prevent a query from re-using existing ExecutionPlan or recompiling ExecutionPlan or creating new ExecutionPlan during it’s excution.
    Thanks,
    Shovan Mukherjee

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s