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