Changing the 'max worker threads' configuration of SQL Server

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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.
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

Ιδιότητες

Αναγνωριστικό άρθρου: 2157129 - Τελευταία αναθεώρηση: 08/09/2012 06:45:00 - Αναθεώρηση: 4.0

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 Express with Advanced Services, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Express, Microsoft SQL Server 2008 R2 Express with Advanced Services

  • KB2157129
Σχόλια