How to change the SQL Max worker threads to improve performance

A normal” SQL installation on a standard Windows Server will usually run without performance or bottleneck issues. However, a 32-bit server with 4 processors or less uses only 256 worker threads, which is predefined by the SQL installer.

But if your system will be generating a fairly large load of data, the number of Max worker threads should be changed. The standards are different for 32- and 64-bit operating systems, and the graph below provides guidelines to properly configure your SQL.

Pay attention to the caution section: even though the default setting on a 32-bit machine is 256 threads, it is recommended to increase the number to 1024 threads.

To change the number, follow these instructions as outlined in MSDN:

1. Open the SQL Server Management Studio
2. In Object Explorer, right-click a server and select Properties.
   • If you’re not able to see Object Explorer, go to the View menu and select it.
3. Click the Processors node.
4. In the Max worker threads box, type or select a value from 32 through 32767.
5. Stop and restart the SQL server agent service for the change to take effect.

For more details and instructions for configuring other versions of SQL Server, see:

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