CPU DMVs

Is the server actually CPU-bound? This collection covers sys.dm_os_schedulers and sys.dm_os_wait_stats from a CPU angle. The scheduler queries show task counts per CPU and give you an average current/wait task ratio as a load indicator; the signal-wait query splits total wait time into signal (CPU) versus resource waits - if the signal percentage is high, you are genuinely out of CPU. The final two queries list the specific sessions that are actively consuming CPU right now.

/*
sys.dm_os_schedulers
sys.dm_os_wait_stats
*/


-------------------------------
-- Get list of all schedulers
-------------------------------
SELECT *
FROM sys.dm_os_schedulers 

SELECT scheduler_id, cpu_id, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count, work_queue_count 
FROM sys.dm_os_schedulers 


-------------------------------
-- Get list of schedulers
-------------------------------
SELECT scheduler_id, cpu_id, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count, work_queue_count 
FROM sys.dm_os_schedulers 
WHERE scheduler_id < 255

-------------------------------
-- Get cpu usage - tip 2890
-------------------------------
SELECT AVG(current_tasks_count) AS [Avg Current Task], 
AVG(runnable_tasks_count) AS [Avg Wait Task]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
AND status = 'VISIBLE ONLINE'

SELECT COUNT(*) AS proc# 
FROM sys.dm_os_schedulers 
WHERE status = 'VISIBLE ONLINE' 
AND is_online = 1


-------------------------------
-- Get signal wait time - http://technet.microsoft.com/en-us/magazine/hh781189.aspx
--
-- determine how much of wait is due to signal wait (cpu)
-------------------------------
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] , 
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]
FROM sys.dm_os_wait_stats ;


-------------------------------
-- Get list of processes using CPU - tip 2890
-------------------------------
SELECT 
a.scheduler_id ,
b.session_id,
 (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement
FROM sys.dm_os_schedulers a 
INNER JOIN sys.dm_os_tasks b on a.active_worker_address = b.worker_address
INNER JOIN sys.dm_exec_requests c on b.task_address = c.task_address
CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) AS s2 


-------------------------------
-- Get list of processes using CPU - tip 2890
-------------------------------
SELECT s2.text, 
session_id,
start_time,
status, 
cpu_time, 
blocking_session_id, 
wait_type,
wait_time, 
wait_resource, 
open_transaction_count
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS s2  
WHERE status <> 'background'