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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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'
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: