Parallelism settings 1

[lck092580]

Quick question: What's recommended for Parallelism settings? Should I use all the processors available?

Also, does changing the settings require a restart?

Thanks.

 

[ptheriault]

The quick answer to your question can be found here.

http://support.microsoft.com/default.aspx/kb/322385

and here

http://support.microsoft.com/kb/329204

Parallelsim can be a good thing. It can also cause some problems for extremely transactional database. I have actually run into problems with complex querries blocking itself.
you don't have have to change the whole server setting of max degree of parallelism to change one statment. If you have one problem query you can use the MAXDOP 1 option.

Like this..

SELECT ...
FROM ...
WHERE ...
OPTION (MAXDOP 1)

To change the setting for the server run this.
You don't have to restart the services for this to take effect.

sp_configure 'max degree of parallelism', 1
go
reconfigure with override

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[lck092580]

Thanks for the detailed explanation and KB articles Paul!