Post reply

How reliable execuion plan estimates are!!

  • April 5, 2012 at 7:46 am

    #256029

    Hi

    I have a proc with 3 sql statements. Executing this proc takes around 5 minutes. The plan shows that one of select statement takes 99% relative to batch. This should mean that almost all time should be taken by this statement. But that is not true. If i put getdate() in between statements, then I see that actually more time is being taken by another statement.

    So question is how reliable is the estimate given by actual execution plan is? Are there situations when we should rely on them or is it that we should never trust this estimate if we want an accurate time estimate.

    PS: I am only talking about the "relative to batch" estimate only and not the plan diagram.

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • April 5, 2012 at 7:50 am

    #1469521

    esitmates are estimates and should be taken as such.

  • April 5, 2012 at 10:45 am

    #1469702

    They are just calculations. Nothing more. They're based on statistics, so the more accurate your statistics are, the more accurate those numbers are likely to be. However, because some things have no statistics (table variables, multi-statement table valued functions) or stats can be out of date making those numbers meaningless.

    Fact is, you can't count on these numbers as means to compare two queries. Not ever. They're just too meaningless.

    You can use them within a query, but, you need to always remember that they're just estimated values and subject to all sorts of issues.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 5, 2012 at 6:48 pm

    #1469933

    S_Kumar_S (4/5/2012)

    Hi

    I have a proc with 3 sql statements. Executing this proc takes around 5 minutes. The plan shows that one of select statement takes 99% relative to batch. This should mean that almost all time should be taken by this statement. But that is not true. If i put getdate() in between statements, then I see that actually more time is being taken by another statement.

    So question is how reliable is the estimate given by actual execution plan is? Are there situations when we should rely on them or is it that we should never trust this estimate if we want an accurate time estimate.

    PS: I am only talking about the "relative to batch" estimate only and not the plan diagram.

    I wrote a posting a while back on this very subject. Even the Actual Execution plan reports "relative to batch" incorrectly. Please see the following link for one example. I wish I could show you some of the things that I've seen at work that have nothing to do with recursion but I can't post code from work (NDA, etc).

    http://ask.sqlservercentral.com/questions/5574/execution-plan-bug.html

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 9, 2012 at 3:02 am

    #1470580

    Well , I understand that Estimated execution plan might be giving just "estimates" but actual execution plans should have more accuracy.

    In my question, I was referring to atual execution plan only.

    Anyway...I'll be calculating time taken by each query manually (using statistics TIME ON and using getdate() in between statements)....

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • April 9, 2012 at 3:12 am

    #1470583

    S_Kumar_S (4/9/2012)

    Well , I understand that Estimated execution plan might be giving just "estimates" but actual execution plans should have more accuracy.

    Nope. Common misconception.

    The only difference between an estimated plan and an actual plan is that the actual plan contains (in addition to all the info from the estimated plan) the actual row count, actual executions and actual data size. Everything else is absolutely identical between the 'estimated' plan and the actual.

    Those two names are very misleading. It's not the case that the optimiser does a quick and dirty job when asked for an execution plan and a good job when asked for an actual plan. It doesn't. It does exactly the same thing - optimise the query and produce the best plan it can fine.

    What's probably a better and easier understood term for the plans would be 'Execution plan without runtime information' and 'Execution plan with runtime information', because that's all they are.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2012 at 4:04 am

    #1470591

    Gail

    You mean to say even if there are objects with no stats e.g. table variables ni proc, still both estimated and actual plan will be same?

    I remember somewhere I saw significant difference between actual and estimated plan. I'll try to figure that out and paste here...

    Thanks

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • April 9, 2012 at 4:06 am

    #1470592

    And Gail, when you say "actual executions ", doesn't it mean that I might see different objects(e.g. indexes) being used in actual execution plan. Whereas they were not used in estimated execution plan.

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • April 9, 2012 at 4:42 am

    #1470594

    S_Kumar_S (4/9/2012)

    And Gail, when you say "actual executions ", doesn't it mean that I might see different objects(e.g. indexes) being used in actual execution plan. Whereas they were not used in estimated execution plan.

    That can happen, but it's generally due to recompiles and stats being updated (again causing a recompile). The primary difference between estimated and actual is that the actual includes row counts and execution parameter values. Other than that, they are basically the same, especially in regards to the cost values. Those are all generated the same way.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 9, 2012 at 5:06 am

    #1470595

    S_Kumar_S (4/9/2012)

    Gail

    You mean to say even if there are objects with no stats e.g. table variables ni proc, still both estimated and actual plan will be same?

    I remember somewhere I saw significant difference between actual and estimated plan. I'll try to figure that out and paste here...

    Yes, they will be. Only time they might differ is if there's a recompile part way through execution. Table variables don't cause recompiles, so they won't cause a change in the plan

    Even then, the estimates are still estimates, are calculated at compile (or recompile) time purely for the optimiser and can be inaccurate.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2012 at 5:09 am

    #1470596

    S_Kumar_S (4/9/2012)

    And Gail, when you say "actual executions ", doesn't it mean that I might see different objects(e.g. indexes) being used in actual execution plan. Whereas they were not used in estimated execution plan.

    No. Not unless there was a recompile that forced the optimiser to go back and rethink. But even then, after the recompile has occurred, any further requests for the 'estimated' plan will pull the plan (with the recompiled portions) from cache.

    By 'actual executions' I mean the property on the operators 'actual number of executions', which only appears in an actual plan. An estimated plan has no runtime information and hence can't contain 'actual' anything.

    Actual and estimated plans are not two different things. They're one thing with two levels of detail.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2012 at 6:20 am

    #1470612

    S_Kumar_S (4/9/2012)

    Well , I understand that Estimated execution plan might be giving just "estimates" but actual execution plans should have more accuracy.

    In my question, I was referring to atual execution plan only.

    Anyway...I'll be calculating time taken by each query manually (using statistics TIME ON and using getdate() in between statements)....

    Be a bit careful when using STATISTICS TIME ON. I've actually seen it cause one query or the other take a lot of extra time just because it's turned on.

    The absolute best way to measure these types of things is with SQL Server Profiler. The absolute best way to use SQL Server Profiler is to setup a "server side trace".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 9, 2012 at 7:45 am

    #1470656

    Jeff Moden (4/9/2012)

    The absolute best way to measure these types of things is with SQL Server Profiler. The absolute best way to use SQL Server Profiler is to setup a "server side trace".

    In SQL 2008+ Extended Events are even better.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2012 at 4:34 pm

    #1470975

    GilaMonster (4/9/2012)

    Jeff Moden (4/9/2012)

    The absolute best way to measure these types of things is with SQL Server Profiler. The absolute best way to use SQL Server Profiler is to setup a "server side trace".

    In SQL 2008+ Extended Events are even better.

    I can't wait for them to update to 2012 at work so I can stop thinking within the limits of a 2k5 world. :blush: Thanks for the reminder, Gail.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 10, 2012 at 2:06 am

    #1471100

    Thanks Jeff and Gail for your valuable inputs. You make me learn new things even in simple things...almost everytime 🙂

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply