BUG: Processor Tab in SQL Server Enterprise Manager Displays Wrong Number of Processors Configured for Parallel Execution of Queries (273880)
The information in this article applies to:
This article was previously published under Q273880
BUG #: 57817 (SQLBUG_70)
SYMPTOMS
In a multiprocessor server, the Microsoft SQL Server Enterprise Manager (SEM), does not show the correct number of configured processors for use for parallel execution of a query.
You can select the the graphical user interface (GUI) Server Properties, Processor tab, Use "n" processors option button and then assign the number of processors to be used for parallel execution of queries. When you click Apply, close the window, and then reopen the Server Properties, Processor tab, the value in the list box for the number of parallel processors does is not updated to the newly changed value.
The GUI always shows Use 1 processor when accessed, regardless of the sp_configure "max degree of parallelism" setting. When you access the Server Properties GUI to look at or to change other settings, and you click the Processor tab, and then press OK, the sp_configure "max degree of parallelism" setting changes to 1. If you press Cancel, the sp_configure setting remains at the original configured value.
If the GUI is set to Use all available processors and you use only sp_configure to make changes, and you then set "max degree of parallelism" to anything other than 1, the GUI partially reflects this change by displaying Use 1 processor.
WARNING: If you later access the Server Properties GUI, Processor tab and press OK, the sp_configure setting actually changes to 1.
The changes made in the Processor tab for the number of processors for parallel execution is not saved and the next time the Server Properties window opens, the previous change is not reflected.
WORKAROUND
Do not use the Server, Properties, Processor tab to view and/or change the number of processors used. Doing so causes the sp_configure value to change to 1 as the configured value for the maximum degree of parallelism.
To view the current processor configuration for the maximum degree of parallelism option, only use Transact-SQL syntax by running the sp_configure stored procedure through either the Query Analyzer or the ISQL command line utility. To modify the max degree of parallelism value from the Query Analyzer or from ISQL, use code similar to the following:
USE master
go
sp_configure 'show advanced option', 1
go
RECONFIGURE WITH OVERRIDE
go
sp_configure 'max degree', 1
go
Max Degree of Parallelism is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change max degree of parallelism only when the Show Advanced option is set to 1. Changes to the max degree of parallelism setting take effect immediately (without a server stop or restart).
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.
| Modification Type: | Major | Last Reviewed: | 10/16/2002 |
|---|
| Keywords: | kbBug kbCodeSnippet kbDSupport KB273880 |
|---|
|