June 10, 2025 at 12:50 pm
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
June 10, 2025 at 9:16 pm
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.
June 12, 2025 at 2:07 pm
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply