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.

SQL SERVER - Actual Execution Plan vs. Estimated Execution Plan ExecutionPlan

Reference : Pinal Dave (https://blog.sqlauthority.com)

DBA, Execution Plan, SQL Scripts
Previous Post
SQL SERVER – 2005 – Use Always Outer Join Clause instead of (*= and =*)
Next Post
SQL SERVER – Difference and Explanation among DECIMAL, FLOAT and NUMERIC

Related Posts

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.

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

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

    Reply
  • Naga Sai Krishna
    November 13, 2007 7:49 pm

    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.

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

    Reply
  • jerome gerald
    March 28, 2008 3:20 pm

    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

    Reply
  • Dave,

    Thanks for the useful information..

    –Regards,
    Krishna

    Reply
  • Hi,
    Can i see execution plan for Stored Procedure and triggers.
    Thanks

    Reply
  • Alkesh khedle
    August 6, 2008 1:57 pm

    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.

    Reply
  • Gud one

    Reply
  • Dear Dave,
    Thanks for the useful information..

    Regards,
    ShyamSundar.S

    Reply
  • You are great!

    Reply
  • Muralikrishna Reddy
    March 23, 2009 11:19 am

    Very Good.

    Lot of information provided.

    Reply
  • Thank u friend for given usefull information provided abt Exec plan..

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

    Reply
  • Hi Dave

    I like your articles and the way you share with us.

    Adding examples also will be helpful for the readers.

    Prasad

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

    Reply
  • Hi All,

    Please can any one tell how to transfer the jobs through the script ?

    Reply
  • 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?

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

    Reply

Leave a Reply