Performance issue - max_elapsed_time

Question

I have an sp running in production with max_elapsed_time = 87269.399 and avg_elapsed_time (ms) = 10.24. Also max_logical_reads : 8180303 avg_logical_read = 3803.83795958974

Can anyone please help to understand why we have these much times difference in max time and average time. what all could be the reasons.

Answer 1

Without any more information, my coin-flip answer (e.g., I'm guessing a 50% chance of being correct) is parameter sniffing.

Here's an excellent hour-long video by Brent Ozar on identifying and approaches to fixes.

---

Edit: Of course, there are myriad other possibilities.

• You have different branches of code in the stored procedure (e.g., in some circumstances, run these extra expensive queries).
• Poor statistics (not up-to-date) may also increase the effect of other issues.
• Spilling to disk (e.g., when the results are much larger than the memory grant) can also slow down things a lot.
• Loops that can vary a lot in size/number of runs
• Other long queries and/or maintenance tasks blocking the SP continuing
• SQL Server using VIEWs within the Stored procedure in ways you don't expect (e.g., when JOINing a view to other tables, SQL Server may execute the whole view once for every row rather than as a single virtual table).

On the other hand, most people will be able to find and fix (or at least understand) a lot of the above when reviewing the stored procedure. However, if you haven't heard of parameter sniffing, then it's unlikely you'd be able to discover it yourself.