Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 2157129 - Last Review: April 17, 2012 - Revision: 3.0
Changing the 'max worker threads' configuration of SQL Server
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.
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
(http://msdn.microsoft.com/en-us/library/ms187024.aspx)
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:
Collapse this tableExpand this table
Rule software
| Rule title
| Rule description
| Product versions against which the rule is evaluated |
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)
| Non-Default max worker threads value configured
| The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect the use of a non-default 'max worker threads' configuration value. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2.
If you run the BPA tool and encounter a Warning with the title of Engine -Non-Default max worker threads value configured, then your SQL Server 2008 or SQL Server 2008 R2 installation has a 'max worker threads' value != 0 as found in sys.configurations (the "value_in_use"). BPA uses SMO to find the run value of this configuration. | SQL Server 2008
SQL Server 2008 R2
|
SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)
| Non-Default max worker threads value configured
| The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect the use of a non-default 'max worker threads' configuration value.
If you run the BPA tool and encounter a Warning with the title of Engine -Non-Default max worker threads value configured, then your SQL Server 2012 installation has a 'max worker threads' value != 0 as found in sys.configurations (the "value_in_use"). BPA uses SMO to find the run value of this configuration. | SQL Server 2012
|
APPLIES TO
- 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
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate