Sometimes, your SQL Server seems to grind to a halt. The first thing you’ll probably want to do in this situation is try and see if there are any queries eating all the available resources.
This can be found by running this Query :
SELECT sqltext.TEXTSELECT sqltext.TEXT , req.session_id , req.status , req.command , req.cpu_time , req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
Once you have the list of running queries, you can kill that task using this :
And if you want to do some finger pointing, you can find out the offending user with this:
EXEC sp_who 'xxx'
Since finding this out (all credit goes to Pinal Dave) it has proved hugely helpful for me on a number of occasions!