SQL doctor Development Blog

You may be inadvertently limiting the number of worker threads available to the SQL Server process.  This could be caused from the ‘max worker threads’ option being set to 255 after the upgrade of the SQL Server 2000 instance to a later version of SQL Server.

On SQL Server 2000, the default setting for ‘max worker threads’ was 255.  When upgrading to a later version of SQL Server, this setting of 255 is retained which is not the recommended setting for later versions.

For SQL Server 2005/2008 the recommended setting of ‘max worker threads’ is 0.  This allows SQL Server to calculate the optimal number of threads based on the number of CPU’s and if the server is 32-bit or 64-bit.

To configure this option, use the following:

exec sp_configure 'show advanced options', 1;
reconfigure with override;

exec sp_configure 'max worker threads', 0;
reconfigure with override;

Note: The SQL Server instance must be restarted in order for this setting to take effect.

Additional information on ‘max worker threads’ can be found in BOL:

http://msdn.microsoft.com/en-us/library/ms187024.aspx