Resolve Max Worker Threads issues in SQL Server

How to Resolve Max Worker Threads issues in SQL Server

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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

Disclaimer: The Questions and Answers provided on http://datacompute.in 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.

Related Post