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
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:
Product versions against which the rule is evaluated
System Center Advisor
SQL Server configuration "max worker threads" might result in performance issues
System 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.
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