max degree of parallelism

Question

max degree of parallelism

Ilse-156674

SSC Enthusiast

Points: 150
More actions
July 13, 2004 at 12:38 am

#180818

Hi,
On a SMP system (4 cpu's), SQL server 2000 sp3, I've changed max degree of parallelism to 1. This was the advice of MS to avoid 100% use of CPU's.
Two times in a week we need to manual reindex the tables and update statistics (reindex-job), before the change of the parameter max degree, this took 2 hours, now it takes 5.
I would like to change the max degree every time the reindex-job is executed. But in the docu of MS I've read that you may Change max degree of parallelism rarely for servers running on an SMP computer.
Could it do any harm to change the parameter two times in a week ? (set it to 0 in the beginning of the job and to 1 at the end of the job, no other jobs or users are using the db at that time.
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

what was the reason for changing in the first place?

if it was a particular query or stored procedure that was causing the problem then you should try and correct that issue.

perhaps setting parallellism back to 4 and for the processes that were burning the CPU before use a query hint such as

select * from mytable OPTION (MAXDOP 2)

MVDBA

Ilse-156674 SSC Enthusiast Points: 150 More actions · Answer 2

We have been working with query hints and we tried to find the process that caused the problem, when whe didn't find a solution we contacted MS. They adviced us to place the param to 1 , because in that case the SQL will use all of the 4 processors, but one piece of execution plan will be executed on only one processor. (A statement consists of a lot of elementary commands, so it means, that your statements will use more processors)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

i can't see any problems changing the MDP on a regular basis as long as you make sure that other processes aren't affected.

i suppose it will give you time to fidn what is causing the problem.

you could also try using a 3rd party product to track down long running and poor performing SQL (including CPU hog)

i used NETIQ's product recently and couldn't fault it. although the free trial version i downloaded appears to have chnaged to a new version.

http://www.netiq.com/solutions/sql/default.asp

MVDBA