Same execution plan but different run time

  • Is it possible to have a same execution plan for query but different run time?

    We have an ongoing issue with a stored procedure that runs in 1 second sometimes and sometimes takes 2 minutes.

    I was able to capture the actual execution plan for both the run times.

    on comparing the execution plans both look the same.

    Am I missing something?

  • Nah, the plan can be the same for lots of different executions. If it doesn't recompile, regardless of parameters passed, you'll see the same plan used over & over. It's pretty common. So, this means, that in a scenario where, just for example, an index scan is the better choice, it's using a seek and running slowly. This is very common behavior.

    Plus, you could see blocking one time and not another. Resource contention one time and not another. All sorts of things could affect the exact runtime, but leave the plan in place.

    "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

  • If that is the case..how do I know the exact reason for the slowness?

     

    Thank you

  • You have to look to multiple metrics. The plan is one, certainly, but after that you look to I/O, CPU, memory, blocked processes, waits.

    "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

  • SQLAddict01 wrote:

    If that is the case..how do I know the exact reason for the slowness?

    Thank you

    How many records does the procedure return during both executions? Is the count same?

    Are you changing the parameters  values for each run of the procedure ? If yes this can be due to parameter sniffing.

    What exactly the procedure does?

    As Grant mentioned check whether you facing any blocking at that moment, resource contention (CPU & Memory)

    • This reply was modified 4 years, 1 month ago by  VastSQL.
  • Hi, I am running into similar situation, except that my procedure consistently runs slow compared to other environment, when in comparison both environments have similar execution plan and statistics, attached are snapshots(slowstatistics and faststatistics). Any suggestion on what other areas to look at?

    Attachments:
    You must be logged in to view attached files.
  • paphilly wrote:

    Hi, I am running into similar situation, except that my procedure consistently runs slow compared to other environment, when in comparison both environments have similar execution plan and statistics, attached are snapshots(slowstatistics and faststatistics). Any suggestion on what other areas to look at?

     

    There's just not enough information in your post or your pictures to really know.  For example, do the two machines have the same number and type of CPUs at the same clock speed?  Same question for memory and disk (especially since your slow one hit the disk for read aheads).  Have statistics been updated?  Are the tables very close to the same size and have roughly the same page density due to possible fragmentation?  Are they under the same workload?  And "Similar execution plan and "Same execution plan" could be a world of difference.  There's just to many unanswered areas to help here.

    I recommend you attach both ACTUAL execution plans (and not the pictures of them) as actual saved execution plans as a starting point and only after you've answered the question I asked about configuration and workload.

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

  • In addition to what Jeff says, be carefule when you say things like "similar execution plans". Similar is not the same as identical. Small differences in an execution plan can absolutely point to the differences in performance.

    "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

Viewing 8 posts - 1 through 7 (of 7 total)

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