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
My only guesses here are:
Check for blocking in other ways. Perhaps the method you used isn't able to detect it?
MAXDOP is either 0 or equal to the max number of logical cores available to SQL Server. Each proc is going parallel and using all of the cores. The other calls to the proc are effectively waiting for CPU availability. Maybe test one of those procs with a maxdop at half the available cores (OPTION (MAXDOP 4) in the proc, for example) and see if it lowers avg duration.
Good luck!
If you do learn what caused or fixed this issue, please report back. I like learning from other people's issues.