0

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.

Thara Unnithan
  • 15
  • 3
  • Could you show at least the routine body? – gotqn Oct 13 '20 at 06:10
  • 1
    Imagine for a minute you have no knowledge whatsoever about the database this is running on. Not the code that's running, not the tables involved, nothing at all. Now read the question. Could you answer it? – Zohar Peled Oct 13 '20 at 06:13

1 Answers1

0

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.

seanb
  • 6,272
  • 2
  • 4
  • 22