We have here an easy, handy-dandy way to find out how much CPU is in use on your server. Of course you can find out by opening up task manager, or by running perfmon, but that’s not really the point. We want something quick and easy. We want something we could potentially use for monitoring, alerting, or tracking over time in SQL Server.

Well of course the answer is Powershell!

Open up Powershell right now, and type “Get-WmiObject win32_processor | select LoadPercentage“. That’s how much CPU is in use right now.

Now let’s say that you have a recurring intermittent CPU spike on your SQL Server. How could we use this? Let’s take a look at our script:


CREATE TABLE #PS
(
PSoutput NVARCHAR(2000)
);

DECLARE @posh NVARCHAR(2000);
SET @posh = ‘powershell “Get-WmiObject win32_processor | select LoadPercentage”‘;

INSERT  INTO #PS
( PSoutput )
EXEC xp_cmdshell @posh;

SELECT  CAST(LTRIM(RTRIM(PSoutput)) AS INT) AS LoadPercentage
FROM    #PS
WHERE   PSoutput LIKE ‘%[0-9]%’;

DROP TABLE #PS;

Here’s what we’re doing:

1. Create a temp table #PS to hold our output.
2. Save the Powershell command as a variable.
3. Run the Powershell command using xp_cmdshell (this will have to be enabled using sp_configure), and load the results into #PS.
4. Select the only numeric value in #PS, trim the spaces off it, and cast it as an integer.

From here, we could do several things.

• If we wanted to track CPU over time, we could stick this in a job to save that value to a table, and have it run every few minutes.
• Or, we could set up an alert if the CPU goes over a percentage.
• Better yet, a hybrid solution would be to track just the instances of CPU over a threshold (say, 90%), and alert if it happens several times.

We have a solution in place (on a server that spikes CPU randomly) that runs sp_WhoIsActive to a table if CPU is above threshold, and alerts if the CPU has been high for 3 samples out of the last 5. It keeps us from running pell-mell to the computer every time an alert happens, because we set it up to gather information automatically.

Of course it would also be useful to see how much CPU that SQL Server itself is using, to compare against the server total. But that’s a blog for another day.

Sean McCown
www.MidnightDBA.com/DBARant

Jen McCown
www.MidnightDBA.com/Jen

This is a MidnightDBA collaborative blog.