How to tell if there are currently any long running queries executing?

Posted on

Question :

(Related to this question)

If I want to see if there are currently any long-running queries executing on my database, what’s the easiest way of checking? (I expect the answer will be some query on master..sysprocesses?)

Answer :

WhoIsActive from Adam Machanic is an excellent tool/sp to see what’s currently happening on your server.

The below tutorial from Brent Ozar shows how to periodically log sp_whoisactive to a table for later analysis.

https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

I use the following query to get information about currently running queries. It has the advantage of not requiring any additional packages or addons:

SELECT * 
FROM sys.dm_exec_requests 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

You would probably be most interested in the [start_time], [total_elapsed_time], and [text] fields.

Leave a Reply

Your email address will not be published.