Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

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

2 Answers

up vote 1 down vote

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
up vote 1 down vote

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

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.