THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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.

Published Wednesday, April 22, 2009 12:45 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

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...

April 22, 2009 12:27 AM
 

jayant said:

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

April 22, 2009 5:03 AM
 

Adam Machanic said:

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

April 22, 2009 10:32 AM
 

Shuwei Wu said:

What's the cache timeout mean here, do you mean it's the web application configuration?

April 23, 2009 1:29 AM
 

Greg Low said:

Hi Shuwei,

Specifically it was the timeout on the "Cache" object in the web app.

Regards,

Greg

April 27, 2009 3:27 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement