Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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?

share|improve this answer
    
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? –  Sean Howat Nov 24 '10 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 ;-) –  Simon Catlin Nov 24 '10 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. –  Sean Howat Nov 24 '10 at 20:12
    
@Dynamo: Strictly speaking: Adding indexes is not "changing the database" (provided its not a unique index) –  Mitch Wheat Jan 15 '11 at 0:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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