August 10, 2009 at 2:59 am
tahir1985 please let me know which hav u used for this ......
August 10, 2009 at 3:46 am
One of the aspects of MAXDOP that is missed by many people is that MAXDOP applies to each portion of the plan, not to the overall statement.
For example, look at a statement which joins the results of two GROUP BY subqueries, with a statement option of MAXDOP(2). Many people assume this means the statement will use a maximum of 2 cores at one time, but it could validly use 4 cores at one time.
A) GROUP BY subquery 1 could be parallelised, with the MAXDOP(2) restriction limiting this subquery to 2 procesors.
B) GROUP BY subquery 2 could also be parallelised, with the MAXDOP(2) restriction limiting this subquery to 2 procesors.
C) Both subqueries could be executing at the same time, so up to 4 processors could be in use. If you had 4 such subqueries, then up to 8 cores could be used, etc.
Also, remember that the parallelism opportunities you see in the compiled plan could be greater than those used in the executable plan.
An executable plan is created each time the statement runs, and is often just a copy of the compiled plan. However, where the compiled plan includes parallelism, the executable plan will take into account the facilities available at run time. SQL Server has some algorithms that can reduce the amount of parallelism at run time if the server is busy.
This means that the same statement or SP that shows a plan with parallism may run with or without parallelism depending on the server load at run time.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 10, 2009 at 4:10 am
I assumed that MAXDOP limited absolutely the number of cores allocated to a session, query, or batch. Is that not so?
I wonder if parallelism-waiting is a fault. Naturally, parts of a task executing in parallel may take different durations and then must be combined for output, so one side will finish first and then wait for the other(s). The work is still getting done sooner than it would be without parallelism, and the server isn't tied up and idle - not any more than it would be (e.g. with locking) without parallelism.
Put it this way, if a parallel plan is poorer than a non-parallel plan then the server is supposed to be smart enough not to use it.
On the other hand, I'll restate: for whatever reason - and with arguably poor database design, and not necessarily the current versions of SQL Server - I've seen queries that were pathological - sick - when parallelism was on, and cured when parallelism was off. I assumed that the query optimiser reached a non-parallel plan that it failed to consider when it had parallelism options, but maybe instead it is a statistical error in estimating the costs of plans.
In the OLTP scenario if there are a lot more users than processor cores on a server that is quite busy, and given parallelism also has costs, I'd assume that non-parallel execution for each user is the better choice.
August 10, 2009 at 4:24 am
currently i m using MAXDOP 0 (Default)
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 10, 2009 at 4:37 am
MAXDOP 0 is often bad news. This allows SQL Server to produce a plan that does not take into account the resources on the box. I have seen really horrible plans and execution times when MAXDOP has been left at zero.
The minimum tuning you should do is to set the server-level MAXDOP value to the number of cores on your processor. Many people recommend setting MAXDOP to maybe 1/2 or 2/3 the number of processors, but as a minimum effort it should not be zero and should not be higher than the number of cores.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 10, 2009 at 4:42 am
i m having total 8 CPUs
i feel MAXDOP should be 4 (as per MS suggestion)
what u feel ?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
August 10, 2009 at 4:57 am
MAXDOP 0 -is- the number of cores on the server (allowed to SQL Server, which may be fewer than "all").
So what does MAXDOP 0 achieve?
August 10, 2009 at 5:52 am
EdVassie (8/10/2009)
MAXDOP 0 is often bad news. This allows SQL Server to produce a plan that does not take into account the resources on the box. I have seen really horrible plans and execution times when MAXDOP has been left at zero.The minimum tuning you should do is to set the server-level MAXDOP value to the number of cores on your processor. Many people recommend setting MAXDOP to maybe 1/2 or 2/3 the number of processors, but as a minimum effort it should not be zero and should not be higher than the number of cores.
Ed, I don't understand your statements about MAXDOP 0. Can you elaborate please? My understanding is that 0 means SQL Server will consider using all available cores.
I definitely agree that virtually every environment in existence outside of the top half or maybe one percent of them that actually have a sufficiently-sized IO subsystem should set MAXDOP at some fraction of the number of phyisical cores. I usually recommend 1/4 to 1/3, not the higher ratios you have, because almost no one has a decently configured IO subsystem.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 10, 2009 at 5:57 am
Put it this way, if a parallel plan is poorer than a non-parallel plan then the server is supposed to be smart enough not to use it.
To my knowledge, the optimizer currently has no smarts built into it to detect, track and analyze IO statistics both near real time nor probabilistically. That level of knowledge would be required for the optimizer to say "hmm, the data drive(s) that store the data for this query are really slow right now (or are historically slow at this given time of the day), so I won't let this query go parallel because it will just make CPUs wait for IO which is suboptimal".
I will add that it is the company's and DBA's "responsibility" to ensure their system is properly sized, configured and maintained and the developer's "responsibility" to ensure good design and coding if they all want optimal performance. If one does those things then the engine is ready to do good work for you. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 10, 2009 at 6:34 am
Can you elaborate please?
Sorry, my memory seems to be changing from accessible to random... this applied to a different RDBMS.
TheSQLGuru is right in saying in SQL Server, MAXDOP 0 means use all available processors, therefore setting MAXDOP to the number of cores should give the same result.
Although most experts agree that the default setting for MAXDOP is not a good setting, there are a lot of opinions on what is the optimal calculation for setting this value. The important message is that the more cores you have on your server, the more important it is to work out the best MAXDOP setting for your installation.
While looking at how to answer TheSQLGuru's question, I came across the thread below started by Kevin Kline that adds another twist to the MAXDOP story...
http://sqlblog.com/blogs/kevin_kline/archive/2008/04/15/nasty-rumors-about-maxdop.aspx
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply