Before we discuss performance issues related to Max Worker Threads (MWT), let’s understand what MWT does. MWT are worker threads available for SQL Server and using the native Windows threads SQL Server simultaneously handles multiple tasks in parallel. For example, Database checkpoints, network layer processes, handling transaction processing, etc. The default value is 0. The 0 value allows SQL Server to spawn threads based on server resources dynamically. It works for the majority of the SQL Server deployment worldwide. To improve performance, sometimes increasing the MWT value manually helps. In this post, I am going to talk about how to resolve max worker threads issues in SQL Server.
How to Resolve Max Worker Threads issues in SQL Server
You will only run out of MWT when the SQL Server is very busy, high user concurrency and additional features like SQL Server Always On, Replication Services are enabled. Based on SQL Server versions (32 bit or 64 bit) and processor cores, a pool of worker threads are spawned by SQL Server to service user requests. Here is a quick look at the hard numbers.
CPU Core Count | Older 32 bit Servers | Latest 64 bit Servers |
---|---|---|
Up-to 4 processors | 256 | 512 |
Octa-Core 8 processors | 288 | 576 |
16 processors | 352 | 704 |
32 processors | 480 | 960 |
64 processors | 736 | 1472 |
128 processors | 4224 | 4480 |
256 processors | 8320 | 8576 |
The MWT Configuration option excludes threads used for Always On Availability Groups and Database Mirroring.
Use the following Query to find out which System Tasks have caused additional worker threads to be spawned:
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 INNER JOIN 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;
How to change the Max Worker Threads count using T-SQL
USE AdventureWorks2016 ; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE ; GO EXEC sp_configure 'max worker threads', 1000 ; GO RECONFIGURE; GO
Sometimes, reducing the Worker Thread count improves performance
Max Worker Threads Best Practices for Database Mirroring and Always On Availability Groups
- To Avoid MWT exhaustion, do not create more than 10 Availability Groups and 100 Databases. This means, 10 DB’s per Availability group. This is a recommendation and not a hard limit.
- If there is an MWT exhaustion, Add Additional CPU’s or Virtual CPU’s (In the case of Virtual Machines) to increase MWT count. See the above table for reference.
- Creating another instance on the same server to improve MWT will not increase performance.
- Too much Database Consolidation with Databases running Mirroring and Availability Groups can cause MWT exhaustion.
- Calculate the Max MWT and test the system extensively before Always implementing on AG.
How to Calculate MWT Count?
MWT = {512 + ((Number of CPU Cores - 4) * 16)} = Result
How to Calculate Max Worker Thread Usage
select scheduler_id,current_tasks_count, current_workers_count,active_workers_count,work_queue_count from sys.dm_os_schedulers where status = ‘Visible Online’
Breakdown of which System Task is using MWT Threads
select is_preemptive,state,last_wait_type,count(*) as NumWorkers from sys.dm_os_workers Group by state,last_wait_type,is_preemptive order by count(*) desc
Official Online Resources from Microsoft
-
Learn about max worker threads Option
- Learn about SQL Server Always On Restrictions