I have a database that is constantly slowing to a crawl. The hardware for it is more than enough so I'm confident that's not the issue. When looking at resource waits through activity monitor, Buffer I/O is the highest by far. It's in the hundreds of thousands for cumulative wait times when the other waits are still in the hundreds. This has naturally made me start looking towards my Buffer I/O as the issue.

I set up a performance monitor to record full scans/sec and buffer cache hit ratio. The buffer cache hit ratio is consistently in the 98-100 range so everything seems okay there. My full scans/sec ranges from < 1 to just over 3. This seems high to me especially since it's not uncommon for it to be hitting 2+.

Does this seem like the most likely candidate for causing the slowness issues? Any other advice on what might be good to monitor if I'm seeing such high Buffer I/O wait times?

1 Answer 1


I am guessing you've exhausted the possibility of the hardware being an issue ("The hardware for it is more than enough"). If the application is grinding to a halt from a client perspective, don't forget network latency - easy to overlook.

Have a look at the Perfmon counter Page Life Expectancy at the same time as Page Lookups/sec, Page Reads/sec, and Full Scans/sec.

I've never used the profiler, but have you identified whether the problem occurs with a particular SP or query? Do you need to review your indexes?

  • Well the application has no stored procedures, all the SQL code is handled through the application that serves the data. I believe the indexes are lacking but I'm currently not allowed to make any modifications to the database. What kind of numbers should set off alarms for Page Life Expectancy and Page Lookups/sec?
    – Shane
    Nov 24, 2010 at 18:00
  • I don't think there are any hard and fast figures to go by, which is why MS bang on about bench-marking your server when it's working OK, so that you can compare when things go awry. Having said that, there are a number of good TechNet docs, e.g.: technet.microsoft.com/en-us/library/cc966401.aspx. I've use Quest's Spotlight for SQL Server before when trying to pinpoint a performance issue - it's very good. You could download the free trial, and put it through its paces - for evaluation purposers, of course ;-) Nov 24, 2010 at 19:58
  • I've been taking logs of this system ever since I got it but unfortunately they've been the same results and issues the whole time. I'm checking out the doc you linked now. Thanks.
    – Shane
    Nov 24, 2010 at 20:12
  • @Dynamo: Strictly speaking: Adding indexes is not "changing the database" (provided its not a unique index) Jan 15, 2011 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.