SQL Server Fixing SQL Server Max Worker Threads: error: 35217, severity: 16, state: 1 June 28, 20181192 views0 Share By IG Share If you try fixing SQL Server Max Worker Threads for errors such as: “The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread.” then we have some recommendations for you. The error looks like something given below: Error: “The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread.” Fixing SQL Server Max Worker Threads Typically, the easiest shortcut to solve this problem is by increasing the number of CPU cores in your operating environment. If you are running a VM (Virtual Machine), this can be achieved quickly, provided you have the available resources. But, it would be better to understand why this occurs and what are the limitations for such scenarios. Thread Usage by Availability Groups Always On availability groups (AG) has the following requirements for worker threads: On an idle instance of SQL Server (2012, 2016 or 2017), Always On availability groups use 0 threads. The maximum number of threads used by the availability groups is the configured setting for the max number of server threads (‘max worker threads’) minus 40. See Table below: The availability replicas hosted on a server instance share a common single thread pool. Threads are always shared on an on-demand basis, as follows: In typical scenarios, there are about 3 to 10 shared threads, but this number can increase depending on the primary replica workload. If a given thread is idling for a while, it is released back into the general thread pool of the SQL Server thread instance. Typically, an inactive thread is released after ~15 seconds of inactivity. However, depending on the last activity, an idle thread might be retained longer. A SQL Server instance uses up to 100 threads for a parallel-redo for secondary replicas. Each of the databases uses up to one-half of the aggregated total number of CPU cores, but no more than 16 threads per database. If the total number of required threads for a single instance exceeds 100, SQL Server uses a separate redo thread for every remaining database. Redo threads are released after ~15 seconds of inactivity. Note to Self: Databases are determined to go single-threaded based on their ascending database ID. As such, the database creation order should be counted for SQL Server instances that host more availability group databases than the available worker threads. For example, on a system with 64 or more CPU cores, all databases starting with the 7th database that joined the availability group will be in serial redo mode irrespective of the actual redo workload for each database. Databases that require parallel redo should be added to the availability group first. Also, availability groups use unshared threads, as follows: Each of the primary replicas uses 1 Log Capture thread for each central database. Also, it uses 1 Log Send thread for each secondary database. Log send threads are released after ~15 seconds of inactivity. A backup on a secondary replica holds a thread on the primary replicas for the duration of the backup operation. For more information, see Worker Pool Usage for Availability Groups Enabled Databases Also Read: SQL Server Always On Max Worker Threads Restrictions Here is a quick look at the fixed 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 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 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; Conclusion Typically, you will face difficulties with worker threads on SQL Server when you do not have enough CPU cores to handle the load. In these scenarios, you will need to increase the CPU cores to address the problem. Questions or suggestions are welcome in the comments section below. Thanks! 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. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
TSQL How to Effectively split a string using STRING_SPLIT function in SQL server By DataGeekApril 15, 2019
SQL Server SQL Server 2019 System Requirements – Hardware & Software Prereqs In this article, we will be sharing the SQL Server 2019 System Requirements based on ...
Interview Questions T-SQL Performance Tuning on Bulk Load Data in SQL Server 2017 In this article, we are going to talk about a specific scenario where T-SQL Performance ...
SQL Server How To Check & Fix Index Fragmentation on SQL Server via Script & ssms Before we learn to fix Index fragmentation on SQL Server, let us understand why this ...
SQL Server How To Do GPU Offloading in SQL Server 2017 For Parallelism The idea of having GPU Offloading in SQL Server 2017 is quite attractive. It might ...