Setup alerting of high CPU usage

Few days ago I got puzzled with the possible ways of monitoring CPU on SQL Server box, firing an alert if CPU utilization gets over some benchmark for a set period of time. Surprisingly enough, it quickly turned out there is no such thing as any out of the box CPU-related counter(s)  in SQL Server, at least up to SQL 2008 R2 (don’t know what’s in 2012 yet)

Despite this unfortunate fact of lacking any CPU utilization-related counters, SQL Server Alerts have tones of other counters for monitoring with consequent alerting, when the alert event is fired. However, if you are a decision maker within an organization, you should consider implementing one of those enterprise Server / Network Monitoring tools, which have comprehensive set of monitoring and alerting tools. Good examples would be: WhatsUpGold, PA Server Monitor by PA Power Admin, Server & Application Monitor by Solarwinds.

There are some free tools of this kind: —> … here… <—

Now, let’s get back to our sheep. It would probably make little sense to get alerts every time CPU usage just hits the peak or hits it for some very short time, like a few seconds – this could barely hurt anything. This way, it’s a combination of CPU high usage threshold and time-frame it’s going on for. Typically, you wouldn’t want to get alerted before high CPU utilization goes on for a few minutes, five would probably be a good number here. Researching on this topic didn’t give much, but one post I found particularly useful for this situation – you need to get alerted when total CPU consumption on your SQL Server box gets over certain threshold for more than specified time. Here it is: How to Monitor for High CPU utilization in SQL Server by Geoff Albin.  Very good post, which might be quite helpful. As to me I decided not to go that rout as we have WhatsUpGold in place, which does servers CPU monitoring among other things, but I will definitely save this one for any future occasions when there won’t be any server monitoring app in place.

Just beware, if you decide to run the test, it won’t display it like it’s mentioned in the post:

but rather as a bunch of ever popping-up dialog windows, which you’ll have to kill from Task Manager. Here is what’s happens when you run the script from command window:

Then it waits for 4 seconds and the entire loop starts all over again:

Now, to kill it use Task Manager, locate wscript.exe and kick it off.

Besides, considering creating the job yourself from within SSMS rather than running the script from “Step 2” might be a good idea.