Setting Max Degree of Parallelism

Question

Post reply

Setting Max Degree of Parallelism

DKlein

SSCrazy

Points: 2351
More actions
March 15, 2011 at 9:08 am

#234099

Hello all,
What are some considerations or best practices for parallelism on a reporting server. I seem to be having problems with large reports using up all the processors (of which there are 16), and creating blocks and time-outs for items in the queue.
Thanks,
DK

Viewing 10 posts - 1 through 9 (of 9 total)

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

bake13 SSC Eights! Points: 955 More actions · Answer 1

My recommendation for a server with 16 CPUs is no more than half, so:

sp_configure 'max degree of parallelism',8

reconfigure with override

If you examine the wait stats on your server, I suspect parallel query wait (CXPACKET) will be your highest. Try lowering the MaxDOP, resetting the wait stats and monitor.

DKlein SSCrazy Points: 2351 More actions · Answer 2

nivek-224024,

Thanks for the quick reply! It was extremely helpful.

DK

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Even on a reporting server you might want to consider changing the defaul threshold for parallelism to a higher number. I wouldn't go as high as on an OLTP system, but the default value of 5 is a little low That could help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

DKlein SSCrazy Points: 2351 More actions · Answer 4

Grant,

Thanks for the advice, I am still working it out. Why wouldn't you set it as high as a OLTP? Sorry if that was too open-ended.

DK

bake13 SSC Eights! Points: 955 More actions · Answer 5

Well, on an OLTP system, typically the cost of splitting the query across several CPUs, executing, then bringing the results back together would actually be a more expensive (time mainly) operation than simply executing on one processor.

OLTP queries typically request smaller sets of data. Larger, long-running queries can typically benefit from some parallel query execution, but even that is on a case-by-case basis, in my opinion.