SQLServerCentral / Discuss Content Posted by Herve Roggero / Article Discussions / Article Discussions by Author / Managing Max Degree of Parallelism / Latest Posts

RE: Managing Max Degree of Parallelism

George-73571 — Thu, 09 Apr 2009 11:59:07 GMT

Wow, this is an old thread..I do have a question though. In SQL 2005, can a value be passed to OPTION (MAXDOP [i]n[/i]) with out creating dynamic sql code?EX: OPTION (MAXDOP @maxdopvalue)

RE: Managing Max Degree of Parallelism

Paul Campbell-243089 — Tue, 08 Nov 2005 08:21:00 GMT

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

RE: Managing Max Degree of Parallelism

frank garcia-214722 — Thu, 15 Sep 2005 14:01:00 GMT

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.

RE: Managing Max Degree of Parallelism

Mark_Holst — Wed, 23 Jun 2004 02:19:00 GMT

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.

RE: Managing Max Degree of Parallelism

Herve Roggero — Thu, 26 Jun 2003 07:37:00 GMT

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 SabinCo-author of SQL Server 2000 XML Distilledhttp://www.amazon.co.uk/exec/obidos/ASIN/1904347088

RE: Managing Max Degree of Parallelism

Simon Sabin — Wed, 25 Jun 2003 17:29:00 GMT

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 SabinCo-author of SQL Server 2000 XML Distilledhttp://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Managing Max Degree of Parallelism

Herve Roggero — Fri, 13 Jun 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hroggero/managingmaxdegreeofparallelism.asp