What Queries Are Currently Running on SQL Server

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  :

 KILL xxx 

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s