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
— 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
You can user @@spid to ignore your spid 🙂
Regards,
Paulo Condeça.