-3

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?

1 Answer 1

2

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.