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.
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…
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
Regards
Jayant
09313406257
09313406257
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
What's the cache timeout mean here, do you mean it's the web application configuration?
Hi Shuwei,
Specifically it was the timeout on the "Cache" object in the web app.
Regards,
Greg