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 "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
Additional query words: sp_configure, max worker threads
Keywords: kbbug kbcodesnippet kbpending KB269486
