BUG: Changing Max Worker Threads Requires You to Restart SQL Server (269486)


The information in this article applies to:

  • Microsoft SQL Server 7.0
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 "Symptoms" 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 "max worker threads" 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 "max worker threads" setting:

319942 HOW TO: Determine Proper SQL Server Configuration Settings

REFERENCES

For a brief overview of the User Mode Scheduler (UMS), please see the following article in the Microsoft Knowledge Base:

262973 PRB: Thread Starvation Detected Message Reported in Error Log

Modification Type:MajorLast Reviewed:10/16/2002
Keywords:kbBug kbCodeSnippet kbDSupport KB269486
©2002 Microsoft Corporation. All rights reserved.