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.

Several times a day, SQL Server 2008 takes my server's CPU usage to around 50% constantly and everything starts timing out, which brings my web service to a standstill during that period. Simple queries like select top 10 * from [table_name] time out and even trying to retrieve a table list in the object explorer in Management Studio times out. I can query the sys tables but not being a dba, I'm not sure what to look for. Restarting the SQL Server service solves the problem (obviously?)...

I want to find out what is causing these massive chunks of server-choking usage so that I can correct them and resolve this several-times-daily downtime I have as a result of these timeouts. Any ideas would be much appreciated.

share|improve this question
add comment

2 Answers

The Activity Monitor tool may give you all you need. Connect to your server instance using SQL Server Management Studio, Right-Click on the server and select Activity Monitor while the problem is occurring. Expand the Processes tab in the main window; this should help you isolate the application and source of the connection in a similar way that the Windows Task Manager is used.

More info is located on MSDN here.

share|improve this answer
    
Ah, thanks I'll try that as soon as the problem occurs again. –  Nathan Ridley May 18 '11 at 15:18
add comment

So, what about just running a provfiler and finding out? ;) It is not like SQL Server does not come with a lot of nice tools.

share|improve this answer
    
The problem is that I'm not an expert dba and am not really sure what I'm looking for. –  Nathan Ridley May 18 '11 at 15:18
add comment

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.