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

Anyone who works with SQL Server long enough eventually faces the same problem: a query that just keeps running.

Maybe it's blocking other sessions, maybe CPU usage spikes, or maybe an application suddenly becomes slow. Long-running queries are one of the most common causes of SQL Server performance issues.

In this article, we'll walk through how to:

• Identify long-running queries

• Detect blocking sessions

• Investigate the query details

• Safely terminate problematic queries

Let’s get started.

Why Long-Running Queries Are a Problem

A query running longer than expected can cause several issues:

• Block other queries

• Consume excessive CPU or memory

• Lock critical database resources

• Slow down applications

In high-traffic environments, even a single poorly optimized query can impact multiple users.

That's why knowing how to quickly identify and stop problematic queries is essential.

1. Finding Long-Running Queries

SQL Server provides several Dynamic Management Views (DMVs) that help monitor currently executing queries.

One of the most useful views is sys.dm_exec_requests.

Run the following query to find active sessions and their execution time:

SELECT
    session_id,
    status,
    start_time,
    command,
    cpu_time,
    total_elapsed_time,
    blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id > 50
ORDER BY total_elapsed_time DESC;

What this shows

• session_id → the connection ID

• total_elapsed_time → how long the query has been running

• blocking_session_id → identifies blocking sessions

• status → running, suspended, etc.

Queries with high elapsed time may require investigation.

2. Viewing the Actual Query Text

Once you identify a suspicious session, you’ll probably want to see the SQL statement being executed.

You can retrieve the query text using sys.dm_exec_sql_text.

SELECT
    r.session_id,
    r.status,
    r.start_time,
    r.cpu_time,
    r.total_elapsed_time,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

3. Detecting Blocking Queries

Sometimes the problem isn’t just a slow query, it's a blocking query.

Blocking occurs when one query holds a lock that prevents other queries from accessing the same data.

To find blocking sessions:

SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

If you see a session ID in the blocking_session_id column, it means another process is waiting on it.

In these cases, resolving the blocking query becomes a priority.

4. Using sp_who2 for Quick Monitoring

SQL Server also includes a helpful system stored procedure called sp_who2.

EXEC sp_who2;

This shows:

• Active sessions

• CPU usage

• Disk IO

• Blocking sessions

Look at the BlkBy column to identify blocking queries.

Although it’s an older tool, it’s still very useful for quick troubleshooting.

5. Killing a Long-Running Query

If a query is causing serious issues, you may need to terminate it.

To stop a session, use the KILL command.

Example:

KILL 78;

Where 78 is the session ID.

Important

When you kill a query, SQL Server rolls back the transaction. If the transaction was large, the rollback may take some time.

You can monitor rollback progress with:

KILL 78 WITH STATUSONLY;

6. Preventing Long-Running Queries

Stopping a query is only a short-term fix. Ideally, you should prevent performance issues before they occur.

Some common strategies include:

Add proper indexes

Missing indexes often lead to table scans and slow queries.

*Avoid SELECT **

Only retrieve the columns you actually need.

Monitor execution plans

Execution plans reveal inefficient query operations.

Use query timeouts

Applications should have limits to prevent runaway queries.

Conclusion

Long-running queries are inevitable in most SQL Server environments, but knowing how to quickly identify and resolve them can prevent serious performance issues.

The key steps are:

1. Identify the running query

2. Investigate the SQL statement

3. Detect blocking sessions

4. Kill the query if necessary

5. Optimize the query to prevent recurrence

SQL Server’s DMVs provide powerful insight into what's happening inside your database, and learning to use them effectively can make troubleshooting much easier.