SQL Server CPU Usage by Session

Troubleshooting high CPU usage on a SQL Server Database is an art, but there is a defined methodology to follow to find the root cause of high CPU. This can involve breaking down the overall server CPU usage to a more granular level, first discovering that it’s SQL Server that’s the problem (because way too often it’s something else!), down to exploring specific plan operators in a particular problematic query. Finding that problematic query, identifying the high CPU consumer, means identifying the CPU usage by session.

sys.dm_exec_requests shows the CPU time, but it’s cumulative – it doesn’t give the CPU consumption by each session at the current time. You can see how much CPU usage a session has had since it started, but it doesn’t show you what’s going on right now. To explore that, we need to query sys.dm_exec_requests repeatedly, and look for the differences. We need to collect the CPU usage for a time interval to identify the high CPU consumers.

Here is a query which does that across a one second interval. You can go as small or as large as you like by changing the ‘WAITFOR’ value – but the closer you want to what’s going on “right now”, the smaller you want that interval to be. I find one second to be a good starting point. The query populates snapshots of sys.dm_exec_requests into a table variable called @sessionsCPU, which I then query to do some analysis. I could do this with LAG, but my code will work on pre-2012 instances too, and I can’t see a reason to change it.

Here’s an example of what I get. Obviously, this isn’t from a client machine, it’s just on my local environment. But you’ll see that I can tell that right now, the query that’s causing me most difficulty is the session 71.

I can see the query, and this lets me delve further into the problem. I don’t have an answer yet, but I’m an awful lot closer to finding the culprit and solving the high CPU issue than I was before I ran this query.

Hope this query helps you in troubleshooting high CPU issue and get you a step closer to resolve the issue.

Happy Learning,
Manu

Leave a Reply

Your email address will not be published. Required fields are marked *