Some ways to detect CPU pressure in SQL Server

How can you detect whether your SQL Server is under CPU pressure?  A quick and dirty method is to look at the Performance tab in Task Manager, and see whether you see sustained periods above 95% CPU utilization. As a side note, pulling up Task Manager during a daily scrum is a good way to distract and mesmerize any managers or vice-presidents that may be attending the scrum.

Going a little deeper, I like to monitor Processor, % Processor Time and System, Processor Queue Length and System, Context Switches/sec in Performance Monitor to get an overall impression of CPU pressure.

Finally, drilling into SQL Server itself, here are two DMV queries that you can run to get a feel for what is going on with the server, CPU-wise. The third query below will work in SQL 2000 or 2005.

This query (SQL 2005 only) is a good indicator of CPU pressure. If the signal waits are above 20%, that is a definite indicator of CPU pressure.

— Total waits are wait_time_ms (high signal waits indicates CPU pressure)
SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)
,’%signal (cpu) waits’ = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
,resource_wait_time_ms=SUM(wait_time_ms – signal_wait_time_ms)
,’%resource waits’= CAST(100.0 * SUM(wait_time_ms – signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
FROM sys.dm_os_wait_stats

 

This query (SQL 2005 only) is a another possible indicator of CPU pressure. If the runnable tasks count is above single digits, you may have some CPU pressure (although it could be something else).

— Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

 

This query, (which works in both SQL 2000 and SQL 2005) will give you a snapshot of what queries and stored procedures are taking a lot of CPU time in SQL Server.

— Get the most CPU intensive queries
SET NOCOUNT ON;

DECLARE @SpID smallint

DECLARE spID_Cursor CURSOR
FORWARD_ONLY READ_ONLY FOR

SELECT TOP 15 spid
FROM master..sysprocesses
WHERE status = ‘runnable’
AND spid > 50 — Eliminate system SPIDs
AND spid <> 556 — Replace with your SPID
ORDER BY CPU DESC

OPEN spID_Cursor
FETCH NEXT FROM spID_Cursor
INTO @spID
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ‘Spid #: ‘ + STR(@spID)
    EXEC (‘DBCC INPUTBUFFER (‘ + @spID + ‘)’)
    FETCH NEXT FROM spID_Cursor
    INTO @spID
END

— Close and deallocate the cursor
CLOSE spID_Cursor
DEALLOCATE spID_Cursor

Technorati Tag: SQL Server