Here is my take on SQL Server Worker Threads and Max Worker Thread Setting.
What are Worker Threads
SQL Server, serve "Requests". Request is a logical representation of query or batch or system task (e.g. log update).
Requests can be served via one or more tasks. If it is serial request, one task works at a given time. But if it is a parallel request, multiple tasks can works at a given time.
SQL server spawn, worker to carry on a task. "Worker" is a logical representation of operating system thread. Worker threads are the backbone of SQL Server’s multitasking capabilities.
Max Worker Threads
If you go into SQL Server Properties page -> Processors section, you see a setting called "Maximum worker threads".
This controls how many maximum worker threads SQL server can spawn at a given time. Well Zero doesn't mean there will be none.
When setting is set to zero, SQL server use following formular to calculate maximum number or worker threads it can have:
On 86x processor -> 256 + ((No of Logical Processors – 4) * 8)
On 64x Processor -> 512 + ((No of Logical Processors – 4) * 16)
So if you have 64bit machine with 12 Logical processors, max worker count will be 640.
You can find this out easily by executing following query:
SELECT max_workers_count
FROM sys.dm_os_sys_info
References: Configure the max worker threads (server configuration option) - SQL Server | Microsoft Learn
Expert articles:
BrentOzar -> https://www.brentozar.com/archive/2020/01/three-reasons-to-temporarily-change-max-worker-threads/
No comments:
Post a Comment