cat find-and-kill-long-running-ms-sql-server-queries.md
Find and kill long running MS SQL Server queries
I have to admit this is cargo cult SQL to me, but here's how to find running queries, sorted by their total elapsed time:
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
ORDER BY [req].[total_elapsed_time] DESCTo kill a given query, use:
kill <session_id>..without the brackets <>, where session_id is the corresponding column output from the query above
If you want to kill all long running queries in a single go, try something like this:
DECLARE @cmd varchar(8000) = '';
SELECT @cmd = @cmd + 'kill ' + CONVERT(varchar(5), req.session_id) + ';'
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext where req.total_elapsed_time > 15000
SELECT @cmd;
EXEC(@cmd);Adjust the '15000' (milliseconds) timeout to a reasonable time for your application.
You're welcome.
Comments