up vote 1 down vote favorite
share [g+] share [fb]

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.

link|improve this question
50% accept rate
feedback

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.

link|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
feedback
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.

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

Your Answer

 
or
required, but never shown
discard

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