This is the script which I always had in my archive. Following script find out which are the queries running currently on your server. I use it whenever I need to find a currently running query that is taking too long.
SELECT 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
While running above query if you find any query which is running for long time it can be killed using following command.
KILL [session_id]
What to Check Before You Kill a Currently Running Query
This script shows only requests that are active right now. A session that is connected but idle will not appear, and you will also see your own query in the list, along with some background system work. The list is a snapshot, so run it two or three times before deciding anything. To hide your own row, add WHERE req.session_id != @@SPID to the script.
Before you stop anything, add a few more columns to the list. blocking_session_id tells you if the request is stuck behind another session, wait_type tells you what it is waiting on, and start_time shows when it began. Both cpu_time and total_elapsed_time are in milliseconds. Many times the long query is only the victim, and the real problem is the session blocking it.
Killing a session is not free. SQL Server has to roll back all the work that the query did in its open transaction, and a rollback can take as long as the work itself, sometimes longer. You can watch its progress with KILL 53 WITH STATUSONLY, using the real session number instead of 53.
A few rules I follow:
- Never kill a backup, restore or index rebuild without knowing what it is doing and how far it has gone.
- Talk to the owner of the session first when you can. The host name and login name are in
sys.dm_exec_sessions. - Save the query text before killing it, so you can tune it later and stop the same problem from coming back.
One more note: you need VIEW SERVER STATE permission to see the requests of other sessions. Without it, the script shows only your own work, which can make a busy server look strangely quiet.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
60 Comments. Leave new
Thank you, the query was really helpful!
Thank you :)
Unfortunately, if a stored proc is currently running, it shows the full test of the SP, not which command within the proc is executing.
You need to use offset to find that. Refer https://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/
To get all running query, but your own add
where req.session_id @@SPID
Thank you! That was really helpfull.
Hope you are doing well. Is it possible to get record drop count at each where clause level in a single query. I want to avoid writing different queries for it and creating intermediate tables to do the same.
Madhu
Thank’s, it’s helpful
thanks you
I am running a long query (which will take long time to complete) and i want to know when it is completed and also want see the progress … Is that possible..?
Need to know that, too!
Thanks! This has been helpful.
Is this still completely valid in 2021?
Added to this slightly:
SELECT sqltext.TEXT,
req.session_id,
req.user_id,
req.status,
sess.host_name,
sess.login_name,
sess.program_name,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess on sess.session_id = req.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext