SELECT
parent_node_id AS Node_Id,
COUNT(*) AS [No.of CPU In the NUMA],
SUM(COUNT(*)) OVER()
AS [Total No. of CPU],SUM(runnable_tasks_count ) AS [Runnable Task Count],
SUM(pending_disk_io_count) AS [Pending disk I/O count],SUM(work_queue_count) AS [Work queue count]
FROM sys.dm_os_schedulers WHERE status='VISIBLE ONLINE' GROUP BY parent_node_id
- Number of records in the output will be equal to number of NUMA nodes (if it is fetching only one record , it is not a NUMA supported server)
- Node_id : NUMA node id . Can be mapped into the later scripts.
- No.of CPU in the NUMA : Total number of CPU assigned to the specific NUMA node or the number of schedulers.
- Total No. of CPU : Total No. of CPU available in the server.If you have set the affinity mask, total number of CPU assigned to this instance.
- Runnable Task Count: Number of workers, with tasks assigned to them, that are waiting to be scheduled
on the runnable queue. Is not nullable. In short number of request in runnable queue.To understand more about Runnable queue , read my earlier post.
- Pending disk I/O count : Number of pending I/Os that are waiting to be completed. Each scheduler has a
list of pending I/Os that are checked to determine whether they have been
completed every time there is a context switch. The count is incremented when
the request is inserted. This count is decremented when the request is
completed.
- Work queue count: Number of tasks in the pending queue. These tasks are waiting for a worker to
pick them up.