Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Changing the 'max worker threads' configuration of SQL Server

Changing the 'max worker threads' configuration of SQL Server

View products that this article applies to.

Summary

The default value of 'max worker threads' is 0 for SQL Server. It is not recommended to change this default value but rather allow SQL Server to dynamically determine the value at startup depending on the platform and number of logical CPUs detected.

↑ Back to the top

More Information

The 'max worker threads' configuration value determines the maximum number of worker threads created for handling SQL Server requests. If the 'max worker thread' configuration value is 0, the maximum worker thread value is calculated based on platform (x86 or x64) and the number of CPUs
detected. More details on the calculation can be found in the SQL Server Books Onilne under the section titled max worker threads option

In SQL Server 2008 R2 and later versions, a lower bound is enforced. In scenarios where the "max worker thread" is set to a lower value, an error message that resembles the following may be displayed:

Msg 5862, Level 16, State 1, Procedure sp_configure, Line 166
The number of max worker threads is set too low. On this computer, the number must be more than 255. You should increase the number of max worker threads.

You can find what value SQL Server has automatically selected for 'max worker threads' by running the following query:

select max_workers_count from sys.dm_os_sys_info
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
System Center AdvisorSQL Server configuration "max worker threads" might result in performance issuesSystem Center Advisor detects whether this instance of SQL Server is configured to use the default value for the configuration option "max worker threads". If a non-default value is used and that value is lower than the default value for the given number of processors, advisor generates this alert. This condition can lead to reduced worker threads per scheduler and result in performance problems when the SQL Server is at peak loads. Review the information in the knowledge base article and reset the configuration option to its default value.SQL Server 2008

SQL Server 2008 R2

SQL Server 2012

↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2157129
Revision : 1
Created on : 1/7/2017
Published on : 8/9/2012
Exists online : False
Views : 348