Tricky Max Worker Threads in SQL Server 2005

In case you haven't read the SQL Server 2005 Books On-Line (BOL) cover to cover yet, you might not notice that the default setting

'max worker threads' has changed in SQL Server 2005. In SQL Server 2000, max worker threads always defaulted to 255.  In SQL Server 2005, the new default is dependent upon your default depending on the machine architecture (32 vs 64bit) and/or number of processors. The value is based on a table described in BOL section 'max worker threads Option'.  Mark Souza presented this and many other valuable bits of information in his presentation at the 2005 PASS Community Summit in Grapevine, Texas entitled "Session 308M: SQL Server 2005 early Adopter Lessons Learned".

If you ever see warning messages about worker threads, be sure to read up on how SQL Server 2005 handles the assignment of worker threads to UMS schedulers too, since that too has changed with the newer version.

Best regards,

-Kevin