How to monitor the workers used over a period of time?

Question

Assume I have the default setting of max workers or say I have x number of max worker threads configured.

Is there any windows or sql performance counter to monitor the number of workers used over a period of time?

Answer 1

You can run this query at a frequent interval of your choice and save the result in a table.

/*
Copied from
https://dba.stackexchange.com/questions/188102/who-is-using-my-worker-threads-sql-server-2014-hadr
*/
DECLARE @max int
SELECT @max = max_workers_count FROM sys.dm_os_sys_info

SELECT 
  @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 'RequestWaitingForThreads' ,
  SUM(current_workers_count) AS 'AssociatedWorkers'
FROM sys.dm_os_Schedulers WHERE STATUS='VISIBLE ONLINE'

Remember:

The actual number of query requests can exceed the value set in max worker threads in which case SQL Server pools the worker threads so that the next available worker thread can handle the request. A worker thread is assigned only to active requests and is released once the request is serviced. This happens even if the user session/connection on which the request was made remains open.

I suggest you read the following:

• Configure the max worker threads Server Configuration Option
• Thread Count
• Perfmon Thread Count vs SQL Workers (SQL Server 2017)