0

I have seen a Microsoft SQL Server with CPU stuck near 100% (RAM was using 20GB of 32GB). Task Manager showed that the SQL Server process was using the most of the CPU.

The strange thing is exec sp_who2 doesn't return anything RUNNABLE. Any ideas please?

In the meantime it is being scheduled to restart. It would be nice to know what caused it, and how to solve it without a restart.

2
  • 1
    You are speaking about RUNNABLE, what's about RUNNING? Also nothing were?
    – Manecoon21
    Oct 13 at 14:06
  • 5
    if you can, i would recommend installing sp_whoisactive whoisactive.com . this will give you much better info on currently running processes
    – Bob Klimes
    Oct 13 at 14:18
0

If you have confirmed it is SQL Server process, get my ViewSessionsConnections stored procedure, and deploy it (create) to any user database.

Run it:

exec ViewSessionsConnections 'running'

It will show you which sessions actually running at the moment and burning the CPU.

But would be nice to know what caused it, and how to solve it without a restart.

If you want to know what happened, say, 20 min ago, 1 hour ago, etc. and what caused it, you would need third party monitoring tool such as Redgate SQL Monitor, Idera SQL Manager, etc., these will record in-flight historical data and you can review it later

0
0

In comments and answer there are few great tips mentioned. I am just going to add my experience of handling CPU issues.

First do not look in task manager , probably not the ideal place to check and we’ll SP_who2 is pretty old. The one that I mostly use is from https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit called SP_BlitzWho which gives you some really good insight on what’s hammering on that CPU.

Another great tool which can help you in such performance troubleshooting is extended events. https://github.com/erikdarlingdata/DarlingData You can use sp_PressureDetctor or SP_HumanEvents to get some interesting facts about why server may be struggling with high CPU.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.