A DMV a Day – Day 9

The DMV for Day 9 is sys.dm_os_schedulers, which is described by BOL as:

Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks.

You can discover several useful pieces of information from sys.dm_os_schedulers, but I will just show a couple today. These queries work with SQL Server 2005, 2008, and 2008 R2. They both require VIEW SERVER STATE permission.

-- Get Avg task count and Avg runnable task count
SELECT AVG(current_tasks_count) AS [Avg Task Count], 
AVG(runnable_tasks_count) AS [Avg Runnable Task Count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
AND [status] = 'VISIBLE ONLINE';

This query will help detect blocking and can help detect and confirm CPU pressure. High, sustained values for current_tasks_count usually indicates you are seeing lots of blocking. I have also seen it be a secondary indicator of I/O pressure. High, sustained values for runnable_tasks_count is usually a very good indicator of CPU pressure.  By “high, sustained values”, I mean anything above about 10-20 for most systems.

-- Is NUMA enabled
SELECT 
  CASE COUNT(DISTINCT parent_node_id)
     WHEN 1 
         THEN 'NUMA disabled' 
         ELSE 'NUMA enabled'
  END
FROM sys.dm_os_schedulers
WHERE parent_node_id <> 32;

The second query will tell you whether Non-uniform memory access (NUMA) is enabled on your SQL Server instance. AMD based servers have supported hardware based NUMA for several years, while Intel based Xeon servers, have added hardware based NUMA with the Xeon 5500, 5600, and 7500 series. There is also software based NUMA.