How To Get A List Of Top CPU Consuming T-SQL Queries

Photo by Caspar Camille Rubin on Unsplash

In this post, I’ll be referring to MS SQL Server as SQL Server

A lot of times the SQL Server uses a lot of resources causing strange CPU spikes.

Though these are meant to be there so that SQL server can provide more optimized performance, they can be a bottleneck if we see a long spike or continuous spikes.

We therefore need to figure out what query is responsible so that we can then optimize it.

To figure this out, you need to follow the following steps

• Connect to the SQL Server via SSMS
• Run the following SQL Query there

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

• You can now see the most CPU consuming SQL Query on the top
• Either kill it or work on optimizing it in the future