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

Anirudh Duggal
1 min readApr 3, 2021

--

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

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

--

--

Anirudh Duggal
Anirudh Duggal

No responses yet

Write a response