Monitoring SQL Server Worker Threads

    Effective performance monitoring in SQL Server includes keeping an eye on worker threads and their utilization. Below is a detailed SQL query that helps you understand the current state of worker threads on your SQL Server instance. 

This query leverages the sys.dm_os_schedulers dynamic management view to provide valuable insights.

Query to Monitor Worker Threads

-- Declare a variable to hold the maximum worker threads count

DECLARE @max INT;

-- Get the maximum number of worker threads

SELECT @max = max_workers_count

FROM sys.dm_os_sys_info;

-- Retrieve current thread utilization statistics

SELECT

    GETDATE() AS 'CurrentDate',

    @max AS 'TotalThreads',

    SUM(active_workers_count) AS 'CurrentThreads',

    @max - SUM(active_workers_count) AS 'AvailableThreads',

    SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',

    SUM(work_queue_count) AS 'RequestsWaitingForThreads',

    SUM(current_workers_count) AS 'AssociatedWorkers'

FROM sys.dm_os_schedulers

WHERE STATUS = 'VISIBLE ONLINE';