How to Resolve Max Worker Threads issues in SQL Server

How to Resolve Max Worker Threads issues in SQL Server

Before we discuss about performance issues related to Max Worker Threads (MWT) let’s understand what MWT actually 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 dynamically spawn threads based on server resources. This 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.

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.

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 a MWT enhaustion, Add Additional CPU’s or Virtual CPU’s (In case of Virtual Machines) in order 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 implementing Always 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

• Lean about max worker threads Option

• Learn about SQL Server Always On Restrictions