Recently while troubleshooting a performance issue on the SQL Instance which was experiencing a very high THREADPOOL wait type I observed something which I was unaware until I hit this issue.
While I was looking at the system health xel files captured at the time of the issue, specifically at the query_processing event trace, the following highlighted line caught my attention.
The server has a 16 core x64 bit processors and as per the max worker thread calculation, the worker threads on the server should not exceed 512 + (16-4)*16=704 values. In the query_processing event, we observed the maxworkers correctly reported as 704, however I saw that workerCreated exceeded the maxWorkers which was unusual.
I checked Denzil R & Suresh Kandoth and they helped me understand that this is an expected behavior in SQL Server code since the max worker threads setting only controls the user tasks or in other user batch and doesn’t control the system tasks or internal tasks. The same is also documented in the msdn article as shown below
The max worker threads server configuration option does not take into account threads that are required for all the system tasks such as Availibility Groups, Service Broker, Lock Manager, and others. If the number of threads configured are being exceeded, the following query will provide information about the system tasks that have spawned the additional threads
SELECT s.session_id, r.command, r.status, r.wait_type, r.scheduler_id, w.worker_address, w.is_preemptive, w.state, t.task_state, t.session_id, t.exec_context_id, t.request_id FROM sys.dm_exec_sessions AS s INNERJOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id INNER JOIN sys.dm_os_tasks AS t ON r.task_address = t.task_address INNER JOIN sys.dm_os_workers AS w ON t.worker_address = w.worker_address WHERE s.is_user_process = 0;
Don’t be surprised if you see the worker threads in sys.dm_os_workers to exceed the max worker threads and you can use the above query to identify the system task which are using the additional worker threads
Hope this helps !!!
Premier Field Engineer
Thanks for this blog. It was helpful. but it bring up some questions. 1) If a SQL server has 576 as max worker thread and all of them are used by user processes, will AAG and other system processes spawn additional threads ? and if yes additional worker threads would require memory .. which will come from buffer pool ?