January 26, 2009 at 3:23 am
We use a piece of software that requires that the DB server have the max degree of parallelism set to 1 (using the code below) although the server has multiple processors.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE
GO
* If I subsequently issue a command to rebuild an index with the online option will this only use the single processor and therefore be serial?
* If I specify the MAXDOP as a number greater than 1 will this override the server setting and utilise more of the processors?
Your help is much appreciated.
Regards,
Phil
January 26, 2009 at 7:32 am
Phil,
I think the following article may answer your question:
http://support.microsoft.com/kb/329204
http://www.mssqltips.com/tip.asp?tip=1028
Gethyn Elliswww.gethynellis.com
January 26, 2009 at 7:39 am
Thank you Ellis Remotedba.
Regards,
Phil
January 27, 2009 at 1:48 am
It appears that there is conflicting documentation about this option. If you see the BOL http://msdn.microsoft.com/en-us/library/ms181714(SQL.90).aspx, you'll notice that it explicitly says you can exceed the 'max degree of parallelism' setting:
MAXDOP number
Overrides the max degree of parallelism configuration option of sp_configure for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure. All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see max degree of parallelism Option.
I've tested a few queries on one of my larger test databases, and the results seem to confirm the BOL documentation and contradict KB above.
Regards,
Phil
January 27, 2009 at 2:34 am
I think the second article above implies that BOL is correct, which is why i added it after i sent you KB, but if you have tested it and it works then i guess you have a definate answer.
Gethyn Elliswww.gethynellis.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply