How to Kill a Query in SQL Server

sp_who2 lists every session with its status, login, what it is blocked by, and CPU and IO totals. The BlkBy column is the one to read first: when sessions are queued behind one another, killing the head of the chain frees the rest. It is an old procedure and its output cannot be filtered directly.

EXEC sp_who2;

Find long-running queries with their SQL text

This is the modern equivalent and it returns actual query text, so you can see what you are about to kill. Excluding @@SPID keeps your own session out of the results. Add r.blocking_session_id to find the root blocker, and note that total_elapsed_time is in milliseconds, hence the division.

SELECT r.session_id,
       r.status,
       r.total_elapsed_time / 1000 AS seconds,
       t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

Kill the session

KILL takes the session id, not an operating system process id. The session's open transaction is rolled back, and that rollback can take as long as the work already done, sometimes longer, during which the session shows as KILLED/ROLLBACK and cannot be killed again. WITH STATUSONLY reports percentage complete and estimated seconds remaining.

KILL 53;

-- check rollback progress
KILL 53 WITH STATUSONLY;