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

Managing Max Degree of Parallelism Expand / Collapse
Author
Message
Posted Friday, June 13, 2003 12:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 6, 2009 11:47 PM
Points: 48, Visits: 52
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hroggero/managingmaxdegreeofparallelism.asp

Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
Post #13196
Posted Wednesday, June 25, 2003 5:29 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 573, Visits: 107
MAXDOP well where to start.

The first encounter of this was with SQL 7. a very badly written statment resulted in an almost infite loop due to the parallel processes not getting back together.

My view is that if you have a well defined statement then the default works well. However if you are resulting in large rowsets in your query i.e two sub queries returning lots or rows that then have to be joined, then using MAXDOP is a serious consideration.

When handling large sets of data within a query I have often seen SQL choose a parallel query plan that results in the query not completing.

Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088



Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons
Post #66532
Posted Thursday, June 26, 2003 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 6, 2009 11:47 PM
Points: 48, Visits: 52
This is a very good point. This is where the hardware architecture makes a difference...

For instance, on an ES7000, you could affinitize SQL Server to run on only 12 processors out of 16, and specify a MAX DOP of 4. However, there is no way to tell SQL Server which 4 processors are to be used for a given query. So SQL Server could pick 4 processors that are all on different sub-pods, or 4 processors that are all on the same sub-pod.

You would get very different results since in the first case the hardware has to do a lot of 3rd-level cache synchronization across sub-pods, whereas in the second scenario it would be minimized, and hence reducing concurrency issues. What makes it worse is that this is highly unpredictable.

Since you really never know exactly which processors are going to be used (because of the underlying hardware and the current CPU load), it is difficult to predict the performance outcome of any given statement, with or without the DOP implemented.

Because of the hardware-dependent nature of parallel queries, it is important to look at the DOP from a macro standpoint. What is the "overall" impact of its implementation? This question is valid even if the DOP is implemented for a single query.

I agree with you that you don't want to change the DOP unless necessary. SQL Server 2000 seems to implement the DOP in a very stable fashion. I know of a client that has successfully implemented this technique in production.

All-in-all, it is difficult to talk about parallel queries without considering the hardware SQL Server is running on. The underlying hardware architecture can have a very dramatic impact on SQL Server's behavior.

quote:

MAXDOP well where to start.

The first encounter of this was with SQL 7. a very badly written statment resulted in an almost infite loop due to the parallel processes not getting back together.

My view is that if you have a well defined statement then the default works well. However if you are resulting in large rowsets in your query i.e two sub queries returning lots or rows that then have to be joined, then using MAXDOP is a serious consideration.

When handling large sets of data within a query I have often seen SQL choose a parallel query plan that results in the query not completing.

Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088





Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
Post #66533
Posted Wednesday, June 23, 2004 2:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 25, 2004 5:50 AM
Points: 50, Visits: 1

I do have one question that I never resolved in my own mind - how does MAXDOP work with HyperThreaded CPU's?

If, for instance, on a Dual-Xeon/P-IV system, SQL Server thinks it has 4 processors.  Each virtual processor isn't really an entire processor independant of one of the others in its own right, so increasing the load on one will reduce the available load on the 2nd within the pair.

Does SQL Server have the smarts to get around this, by not allocating busy threads to the 2nd virtual CPU in a pair?  Or does it try to squeeze both through?  Are the internal algorithms tuned for Hyperthreading in some way?

In regards to licensing, I'm aware that you don't need extra licenses for these Hyperthreaded chips.




Post #122504
Posted Thursday, September 15, 2005 2:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 6, 2006 11:12 AM
Points: 1, Visits: 1

I have seen MAXDOP work best when added as a hint within specific parallel queries after it being tested. 

However, I have seen that most programmers recommend it in the interest of reducing deadlocks.  MAXDOP does not stop or alleviate deadlocks especially in a hybrid environment. 

I have fought programmers on this suggestion and have proven my point.  Setting it at the server level may increase the performance of paralell queries (a handful) but however adversely affects all other processing.

Thanks.

 

 

 

 

 

 

Post #220367
Posted Tuesday, November 8, 2005 8:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 8, 2011 4:49 AM
Points: 7, Visits: 36

We have a system with 2 Dual Core Opterons 275's, has anyone had any experience using MAXDOP to increase performance with these processors?

Post #235727
Posted Thursday, April 9, 2009 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 16, 2009 11:20 AM
Points: 6, Visits: 3
Wow, this is an old thread..

I do have a question though. In SQL 2005, can a value be passed to OPTION (MAXDOP n) with out creating dynamic sql code?

EX: OPTION (MAXDOP @maxdopvalue)



Post #694316
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse