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

We run an SQL Server 2008 R2 Standard install on a 4core xeon with 8gb ram (of which sqlserver is allowed to use 6gb). I have a VB6-app that runs queries on the data in this database to generate reports on survey-data. These reports usually take 7-10 minutes to make (~60 questions, ~17 aggregated scores, 50-500 respondents per survey).

Suddenly, however, the reports have started taking upto 30 minutes. And it's not just one report now and then. It's consequent across the whole. It just slowed down. I have not changed anything.

I have tried to look for locks, but I can't find any.

Here are my wait-stats

wait_type                                                    waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
REQUEST_FOR_DEADLOCK_SEARCH                                  121287               606436183            5107                 606436183
SQLTRACE_INCREMENTAL_FLUSH_SLEEP                             151607               606430095            4094                 0
LAZYWRITER_SLEEP                                             606582               606429625            1202                 320482
XE_TIMER_EVENT                                               20216                606420962            30058                606420698
LOGMGR_QUEUE                                                 568269               606373068            127733               63394

Don't know if it helps..

Anyone have any idea of where I should begin looking?

share|improve this question
1  
Have you tried restarting the SQL service? What troubleshooting steps have you already performed? –  jftuga May 4 '12 at 19:49
    
I have restarted the service, rebooted the server, looked at execution plans until my brain started lazy spooling on it's own... –  Christian Wattengård May 4 '12 at 20:09

2 Answers 2

The next step is to figure out which sql queries are taking so long, and then to work on optimizing them (or optimizing the db itself).

share|improve this answer

SQL 2008 Performance Troubleshooting Guide. It is rather lengthy, but if you have no idea where to begin looking, this will help you narrow it down in a methodical way.

share|improve this answer

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.