Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MAXDOP question Expand / Collapse
Author
Message
Posted Friday, September 13, 2013 1:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 298, Visits: 1,092
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.
Post #1494703
Posted Friday, September 13, 2013 1:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1494707
Posted Friday, September 13, 2013 1:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 298, Visits: 1,092
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.
Post #1494711
Posted Saturday, September 14, 2013 3:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:41 AM
Points: 15,552, Visits: 27,922
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1494797
Posted Tuesday, September 24, 2013 3:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 298, Visits: 1,092
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?
Post #1498047
Posted Tuesday, September 24, 2013 4:45 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:41 AM
Points: 15,552, Visits: 27,922
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1498082
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse