Getting rid of SQL Server long-running queries

There are situations when the SQL server is getting wild – mostly because of our own actions or poorly written queries. From time to time we notice that the SQL server CPU is working at 100% and the website is running slowly, sometimes the CPU is almost not used, but the queries are not working at all…

On the Microsoft SQL Server, there is an easy way to find the queries that are taking a long time to execute. We are using the following statement:

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 cpu_time DESC;

As you can see, the above query is returning the list of running queries sorted by the cpu_time. This is useful if you see that the CPU is heavily loaded. Let’s take a look at the following result of this query:

The top query above took 444 ms of the CPU time but it took over 1.6 seconds to execute from start to end.

In real life, you may notice that there is a query which is still running, and the CPU time is rather large. You can get rid of such a query by using the “kill” statement and the session_id visible on the results above. So, for instance, you can execute:

KILL 72;

This way the session of id 72 will be terminated. Please note that this may trigger a rollback on the transactions, so the kill operation itself may take a while.

but my CPU is not overloaded…

There are situations when the CPU is almost not in use, but the queries are not working. This can be caused by the blocking query. The not committed transaction, update, or insert in progress… In such a case the cpu_time will be rather small, so we can use the total_elpased_time instead:

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 total_elapsed_time DESC;

If on the results list you will notice the query with the large total_elpased_time but almost not cpu_time, it could mean that there is a blocking query somewhere. There is one more statement that will show you the blocked and blocking session:

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

This time, you should get rid of the blocking session, so you can now execute the same “kill” statement but provide the blocking session ID.

Note:

The “Kill” statement should not be used as the silver bullet. It is a temporary solution if you have to fix something immediately, but you should also be advised that it may cause problems with your application or the database itself. In case of issues with queries, you should get to the root of the problems instead of “killing” everything that is queued in the database 🙂