Setting max degree of parallelism to 1 to suppress query parallelism

Question

Post reply

Setting max degree of parallelism to 1 to suppress query parallelism

tt-615680

SSCertifiable

Points: 7835
More actions
January 18, 2011 at 5:39 am

#233425

Hi all,
I have got an SQL Server which has 8 processers and it is set to use all the processers. I was wondering if setting max degree of parallelism from 0 to 1 or any higher would it cause any problems please.
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

setting it to 1 will prevent query parallelism for your instance.

If that is your goal, that's the way to do it.

Is that your goal ?

On most of our oltp databases we restrict it to 4 to prevent a single query consuming all the available cores. (16 core boxes)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

tt-615680 SSCertifiable Points: 7835 More actions · Answer 2

ALZDBA (1/18/2011)
setting it to 1 will prevent query parallelism for your instance.
If that is your goal, that's the way to do it.
Is that your goal ?
On most of our oltp databases we restrict it to 4 to prevent a single query consuming all the available cores. (16 core boxes)

Thank you for your reply. Basically, I wanted to find a way of getting my queries to run faster and I read on one forum that changing the "Max Degree of Parallelism" to 1 or more might make more quires running more efficient.

Thank you

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 3

You can always set the degree of parallelism to 1 if you are having problems with particular queries. We use it sometimes.

Example:

SELECT

*

FROM

Orders

WHERE

orderdate BETWEEN '2011-01-16' AND '2011-01-17'

GROUP BY

[mkCardSchemeId]

OPTION ( MAXDOP 1 )

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

tt-615680 SSCertifiable Points: 7835 More actions · Answer 4

s_osborne2 (1/18/2011)
You can always set the degree of parallelism to 1 if you are having problems with particular queries. We use it sometimes.
Example:
SELECT
*
FROM
Orders
WHERE
orderdate BETWEEN '2011-01-16' AND '2011-01-17'
GROUP BY
[mkCardSchemeId]
OPTION ( MAXDOP 1 )

Thank you very much for your help.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

tt-615680 (1/18/2011)
Thank you for your reply. Basically, I wanted to find a way of getting my queries to run faster and I read on one forum that changing the "Max Degree of Parallelism" to 1 or more might make more quires running more efficient.
Thank you

I wouldn't start setting the parallelism to 1 !

I would start figuring out what's going on on my sql instance using a SQLserver profiler trace !

Then analyze its captured data to figure out what queries consume most ( a top 10 can already give you a good idea ) and fix those.

Maybe even analyse the result using the Index tuning wizard and see what it suggests (you need to analyze these results as well, because it may suggest way more than you need)

Maybe this free ebook can get you started :