Timely reminder to avoid early filtering on resource usage when profiling SQL

I'm back in Melbourne doing some performance-tuning work this week.

Yesterday's issue ended up being a caching problem in middle-tier code. These issues are surprisingly common.

The symptoms were hundreds of thousands of calls to a particular stored proc over a period of half an hour. It's a timely reminder that when you're tracing using SQL Trace calls or Profiler, it's important to avoid filtering out calls that aren't using too many resources, until you've looked at the bigger picture. For example, the logical reads, CPU, duration, etc. on each call were close to zero. No call on its own was a problem but the overall effect of the calls was staggering.

In the end, the problem was a cache timeout value set to 60 instead of 3600. The cache was meant to be flushed each hour, not each minute and the developer responsible thought the value was meant to be in minutes, not seconds.

5 thoughts on “Timely reminder to avoid early filtering on resource usage when profiling SQL”

  1. Greg
    Very good point. Last month I saw the almost same situation at my client's site, but the difference was that one of the table had a trigger which slows down performance. But the logical reads, CPU, duration as you mentioned on each call were close to zero…

  2. Dear Greg
    point of my view create a job which shuld be mail to you that about Io stattitic of sql server that mail will contain logical and physical reads of query

  3. Certainly a great point and one that a lot of people forget.  sys.dm_exec_query_stats has been especially useful here:
    SELECT TOP(10) *
    FROM sys.dm_exec_query_stats
    ORDER BY execution_count DESC

Leave a Reply

Your email address will not be published. Required fields are marked *