June 13, 2003 at 12:00 am
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
June 25, 2003 at 5:29 pm
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
June 26, 2003 at 7:37 am
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
June 23, 2004 at 2:19 am
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.
September 15, 2005 at 2:01 pm
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.
November 8, 2005 at 8:21 am
We have a system with 2 Dual Core Opterons 275's, has anyone had any experience using MAXDOP to increase performance with these processors?
April 9, 2009 at 11:59 am
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)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply