How to Resolve Max Worker Threads issues in SQL Server

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

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.