Microsoft KB Archive/269486

= BUG: Changing Max Worker Threads Requires You to Restart SQL Server =

Article ID: 269486

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q269486



BUG #: 51846 (SQLBUG_70)



SYMPTOMS
When you adjust the maximum worker threads configuration by using code similar to the following: sp_configure 'max worker threads' The run value is updated immediately and you do not have to stop and restart the SQL Server service. However, the User Mode Scheduler picks up the newly configured max worker threads only after you stop and then restart the SQL Server service.



WORKAROUND
Stop and restart SQL Server so that the new configuration settings can take effect.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.



MORE INFORMATION
The command shown in the code sample of the &quot;Symptoms&quot; section dynamically changes the configuration option by running the RECONFIGURE statement and does not affect the way the User Mode Scheduler handles this configuration setting. The User Mode Scheduler does not pick up the new number of worker threads until after the server is restarted.

Changing the &quot;max worker threads&quot; configuration setting from its default value can negatively affect server performance and Microsoft does not recommend changing the value. Please read the following Microsoft Knowledge Base article before you make any changes to the &quot;max worker threads&quot; setting:

319942 HOW TO: Determine Proper SQL Server Configuration Settings