average duration increasing inline with exec counts

  • As the title says , I've got a handful of stored procs where the avg duration rises and falls pretty much mirroring the exec count. I've looked to see if there's any blocking chains they're involved in and cant seen anything that would account for the behaviour. Whilst Id obviously expect total duration to do this I cant explain why the avg is following suit . Any ideas ?

    many thanks Simon

  • Are you able to reproduce this on a test system? If so, I would do so and review the execution plans after each execution.

    One question for you though - how many runs are being used in the calculation for the average? What I mean is if you are only looking at the last 2 executions, the average is likely going to fluctuate a lot. If you are looking at the last 10000 executions, you should see very little change in the average.

    Averages can be very misleading. A better metric would be to look at the min and max execution times and (if possible) the most common execution times. Min/Max should be pretty close unless there is some problem OR there is a lot of complexity in the SP (at least in my experience, YMMV). The most common execution times (aka mean execution times) will help you determine if there is a problem OR if the metric you are looking at is wrong.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • How are you measuring the duration and getting the average? DMVs, Query Store, Extended Events?

    Is it linear?

    "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

  • simon_s wrote:

    As the title says , I've got a handful of stored procs where the avg duration rises and falls pretty much mirroring the exec count. I've looked to see if there's any blocking chains they're involved in and cant seen anything that would account for the behaviour. Whilst Id obviously expect total duration to do this I cant explain why the avg is following suit . Any ideas ?

    Perhaps the bad form of parameter sniffing on code that is looking at indexes that are of the "ever increasing key" type and a fair bit of data is being added to the underlying tables/indexes between runs.

    Since you're using SQL Server 2022, it might be something like Query Store trying to help with execution plans but failing to do so.

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

Viewing 4 posts - 1 through 4 (of 4 total)

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