Configure the max worker threads Server Configuration Option

Applies to: yesSQL Server (all supported versions)

This topic describes how to configure the max worker threads server configuration option in SQL Server by using SQL Server Management Studio or Transact-SQL. The max worker threads option configures the number of worker threads that are available SQL Server-wide to process query requests, login, logout, and similar application requests.

SQL Server uses the native thread services of the operating systems to ensure the following conditions:

  • One or more threads simultaneously support each network that SQL Server supports.

  • One thread handles database checkpoints.

  • A pool of threads handles all users.

The default value for max worker threads is 0. This enables SQL Server to automatically configure the number of worker threads at startup. The default setting is best for most systems. However, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance.

Before You Begin

Limitations and Restrictions

  • The actual number of query requests can exceed the value set in max worker threads in which case SQL Server pools the worker threads so that the next available worker thread can handle the request. A worker thread is assigned only to active requests and is released once the request is serviced. This happens even if the user session/connection on which the request was made remains open.

  • The max worker threads server configuration option does not limit all threads that may be spawned inside the engine. System threads required for tasks such as LazyWriter, Checkpoint, Log Writer, Service Broker, Lock Manager, or others are spawned outside this limit. Availability Groups use some of the worker threads from within the max worker thread limit but also use system threads (see Thread Usage by Availability Groups ) If the number of threads configured is being exceeded, the following query will provide information about the system tasks that have spawned the additional threads.

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;  

Recommendations

  • This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server professional. If you suspect that there is a performance problem, it is probably not the availability of worker threads. The cause is more likely related to activies that occupy the worker threads and do not release them. Examples include long-running queries or bottlenecks on the system (I/O, blocking, latch waits, network waits) that cause long-waiting queries. It is best to find the root cause of a performance issue before you change the max worker threads setting. For more information on assessing performance, see Monitor and tune for performance.

  • Thread pooling helps optimize performance when a large number of clients connect to the server. Usually, a separate operating system thread is created for each query request. However, with hundreds of connections to the server, using one thread per query request can consume large amounts of system resources. The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number of query requests, which improves performance.

  • The following table shows the automatically configured number of max worker threads (when value is set to 0) based on various combinations of CPUs, computer architecture, and versions of SQL Server, using the formula: Default Max Workers + ((logical CPUs - 4) * Workers per CPU).

    Number of CPUs 32-bit computer (up to SQL Server 2014 (12.x)) 64-bit computer (up to SQL Server 2016 (13.x) SP1) 64-bit computer (starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x))
    <= 4 256 512 512
    8 288 576 576
    16 352 704 704
    32 480 960 960
    64 736 1472 1472
    128 1248 2496 4480
    256 2272 4544 8576

    Up to SQL Server 2016 (13.x) SP1, the Workers per CPU only depend on the architecture (32-bit or 64-bit):

    Number of CPUs 32-bit computer 1 64-bit computer
    <= 4 256 512
    > 4 256 + ((logical CPU's - 4) * 8) 512 2 + ((logical CPU's - 4) * 16)

    Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x), the Workers per CPU depend on the architecture and number of processors (between 4 and 64, or greater than 64):

    Number of CPUs 32-bit computer 1 64-bit computer
    <= 4 256 512
    > 4 and <= 64 256 + ((logical CPU's - 4) * 8) 512 2 + ((logical CPU's - 4) * 16)
    > 64 256 + ((logical CPU's - 4) * 32) 512 2 + ((logical CPU's - 4) * 32)

    1 Starting with SQL Server 2016 (13.x), SQL Server can no longer be installed on a 32-bit operating system. 32-bit computer values are listed for the assistance of customers running SQL Server 2014 (12.x) and earlier. We recommend 1,024 as the maximum number of worker threads for an instance of SQL Server that is running on a 32-bit computer.

    2 Starting with SQL Server 2017 (14.x), the Default Max Workers value is divided by 2 for machines with less than 2GB of memory.

    Tip

    For recommendations on using more than 64 CPUs, refer to Best Practices for Running SQL Server on Computers That Have More Than 64 CPUs.

  • When all worker threads are active with long running queries, SQL Server might appear unresponsive until a worker thread completes and becomes available. Although this is not a defect, it can sometimes be undesirable. If a process appears to be unresponsive and no new queries can be processed, then connect to SQL Server using the dedicated administrator connection (DAC), and kill the process. To prevent this, increase the number of max worker threads.

Security

Permissions

Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

Using SQL Server Management Studio

To configure the max worker threads option

  1. In Object Explorer, right-click a server and select Properties.

  2. Click the Processors node.

  3. In the Max worker threads box, type or select a value from 128 through 32,767.

Tip

Use the max worker threads option to configure the number of worker threads available to SQL Server processes. The default setting for max worker threads is best for most systems. However, depending on your system configuration, setting max worker threads to a smaller value sometimes improves performance. See Recommendations in this page for more information.

Using Transact-SQL

To configure the max worker threads option

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example shows how to use sp_configure to configure the max worker threads option to 900.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'max worker threads', 900 ;  
GO  
RECONFIGURE;  
GO  

Follow Up: After you configure the max worker threads option

The change will take effect immediately after executing RECONFIGURE, without requiring the Database Engine to restart.

See Also

Server Configuration Options (SQL Server)
RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL)
Diagnostic Connection for Database Administrators