MAXDOP question

Question

MAXDOP question

sunny.tjk

SSCertifiable

Points: 5041
More actions
September 13, 2013 at 1:13 pm

#279709

The computer that i run queries on has 2 processors. Shouldn't query1 execute faster than query2 ? Surprisingly, both executed taking exact time. Also, on OLTP system, should MAXDOP be disabled or should I leave it as default setting?
Query1:
SELECT * FROM table
UNION
SELECT * FROM table1
Query2:
SELECT * FROM table
UNION
SELECT * FROM table1
OPTION (MAXDOP 1)
Query1 was using parallelism in the execution plan.

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

sunny.tjk (9/13/2013)
The computer that i run queries on has 2 processors. Shouldn't query1 execute faster than query2 ? Surprisingly, both executed taking exact time. Also, on OLTP system, should MAXDOP be disabled or should I leave it as default setting?
Query1:
SELECT * FROM table
UNION
SELECT * FROM table1
Query2:
SELECT * FROM table
UNION
SELECT * FROM table1
OPTION (MAXDOP 1)
Query1 was using parallelism in the execution plan.

A part of your "problem" may be that you're returning the output to "The GREAT EQUALIZER"... the display. If you want to test such things, you'll need a large amount of data and some variables to dump each column into to take the display time out of the picture.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 2

Jeff Moden (9/13/2013)
sunny.tjk (9/13/2013)
The computer that i run queries on has 2 processors. Shouldn't query1 execute faster than query2 ? Surprisingly, both executed taking exact time. Also, on OLTP system, should MAXDOP be disabled or should I leave it as default setting?
Query1:
SELECT * FROM table
UNION
SELECT * FROM table1
Query2:
SELECT * FROM table
UNION
SELECT * FROM table1
OPTION (MAXDOP 1)
Query1 was using parallelism in the execution plan.
A part of your "problem" may be that you're returning the output to "The GREAT EQUALIZER"... the display. If you want to test such things, you'll need a large amount of data and some variables to dump each column into to take the display time out of the picture.

Jeff, table and table 1 have approx 600 thousand rows.

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

I would not recommend disabling parallelism. It's a good thing. I would suggest that you bump up the default value for the cost threshold for parallelism. The default value of 5 is just too low. I'd suggest trying a number like 35 and then monitoring the system to see if you need to adjust from there. A good friend of mine who consults regularly says she starts at 50 and then does the same.

"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

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 4

Grant Fritchey (9/14/2013)
I would not recommend disabling parallelism. It's a good thing. I would suggest that you bump up the default value for the cost threshold for parallelism. The default value of 5 is just too low. I'd suggest trying a number like 35 and then monitoring the system to see if you need to adjust from there. A good friend of mine who consults regularly says she starts at 50 and then does the same.

Grant, do i change the value of MAXDOP to the total number of physical CPUs?

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

sunny.tjk (9/24/2013)
Grant Fritchey (9/14/2013)
I would not recommend disabling parallelism. It's a good thing. I would suggest that you bump up the default value for the cost threshold for parallelism. The default value of 5 is just too low. I'd suggest trying a number like 35 and then monitoring the system to see if you need to adjust from there. A good friend of mine who consults regularly says she starts at 50 and then does the same.
Grant, do i change the value of MAXDOP to the total number of physical CPUs?

There is no absolute rule for this. But if we're talking four or more processors, I'd start with one less than the number of processors. See how that affects behavior, monitor the system, the usual.

"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