How to Track and Kill Long-Running Queries in SQL Server

Long-running queries can consume server resources, block other sessions, and degrade performance. Here’s how to track and safely kill them using built-in SQL Server tools.


—

🔍 Step 1: Identify Long-Running Queries

SELECT
    session_id,
    start_time,
    status,
    blocking_session_id,
    wait_type,
    wait_time,
    cpu_time,
    total_elapsed_time,
    reads,
    writes,
    text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE r.status = ‘running’
ORDER BY total_elapsed_time DESC;


—

What to Look For

total_elapsed_time: Queries running for unusually long durations

cpu_time or reads/writes: Resource-heavy operations

blocking_session_id: Check if it’s blocking others



—

Step 2: Find Session Details (Optional)

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.cpu_time,
    r.start_time,
    r.command,
    t.text AS query_text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY r.start_time;


—

Step 3: Kill the Problem Query

Once you identify the session_id, use:

KILL <session_id>;

> Example:



KILL 64;

This ends the query and rolls back the transaction.


—

🛡️ Safety Tips

Always confirm with the app/dev team before killing queries in production.

Be careful not to kill system processes or critical jobs.

Monitor blocking chains using tools like sp_who2, sp_whoisactive, or Activity Monitor.



—

🎯 Keep in mind

Monitoring long-running queries should be part of your daily DBA checklist. Automate alerts using SQL Agent, PowerShell, or third-party tools.


—