August 9, 2010 at 7:15 pm
we have MAX DOP enabled at server level and in our select statements we are using MAX DOP hints. The higher we mention the less is the execution time, like if i mention OPTION (MAXDOP 1) execution time would be 30 secs and if i mention 4 it would be 15 secs and so on. So when i mention as MAXDOP 4 does it mean that only this one select process can use 4 out of 8 CPU's and all other processes have only 4 CPU's or does it mean that it will use 4 CPU's but other processes can also use these 4 when needed? Please clarify/.thanks..
August 9, 2010 at 8:45 pm
When you say you have MAXDOP enabled, I assume you mean you have it set to MAXDOP = 0. This is the default and it means SQL will use as many parallel threads as it needs/can, and may spread the query over all the CPUs. It does this based on the COST THRESHOLD. i.e. if the query run time is estimated to be over 5 Seconds (default) the optimiser will try create a parallel query plan. In simple terms, the higher the cost, the more threads.
MAXDOP defines the maximum number of threads the query can be parallelised to. It may not always go to that level although Technet seems to imply it will use as many as you define if you use the query hint. The MAXDOP query hint will only apply to that query, not other queries. For that query it overides the sp_configure setting. If the query uses 4 of the 8 CPUs it doesn't totaly exclude other queries from running on the CPUs. It does however exclude other queries from the CPUs as long at the CPUs are being used by the current query. This is no different to the normal behaviour.
In the real world the CPU can only process 1 set of commands at any one time, but with clever switching and resource allocation we get very efficient use of the CPUs. One of the weaknesses of setting MAXDOP = 0 is that a poorly designed query could hog all 8 CPUs for an extended period, preventing other queries from getting CPU time. This is why some DBAs recommend fixing MAXDOP = 1 on OLTP servers (a contentious issue).
Hope that answers the question.
Cheers
leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 9, 2010 at 10:30 pm
Thanks for reply, but from my question i meant to say MAXDOP is 1. Mentioned below is o/p from sp_configure.
max degree of parallelism06411
August 10, 2010 at 2:54 pm
MAXDOP is not a flag, it's a parameter that can be set from 0 - 64, reperesenting the number of threads that can be created in a parallel query.
MAXDOP = 1 means you have DISABLED paralllelism. This setting forces SQL to ONLY USE 1 THREAD.
MAXDOP = 2 means no more than 2 threads etc.
MAXDOP = 0 means Use as many as you can.
So when you use the MAXDOP = 4 Query hint, you over ride the MAXDOP = 1 configuration setting, so that one query can use parallelism. The query is obviously benefitting from parallelism, that is why it runs faster with this option.
On SQL 2005 there are some good reasons to set MAXDOP = 1, although these are still open to debate among DBAs. One issue is that on servers with MAXDOP = 0, the cost of creating a parallel query is sometimes more expensive than the query itself. Also on OLTP servers a poor query can be spread across all the CPUs and block other threads for an extended period. With MAXDOP = 1 a poor query may perform badly, but it won't affect other queries in the same way.
Some DBAs feal this is better managed by increasing the Cost Threshold for Parallelism, typically to 30 seconds. You would need to test this at different settings to see what works.
I believe SQL 2008 has an improved optimiser and it looks like these issues have been resolved.
I hope this clears things up.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 10, 2010 at 3:09 pm
Leo.Miller (8/10/2010)
Some DBAs feal this is better managed by increasing the Cost Threshold for Parallelism, typically to 30 seconds.
Just one comment - cost threshold is not measured in seconds. It's the same unitless cost measurement that's used in execution plans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2010 at 3:22 pm
GilaMonster (8/10/2010)
Leo.Miller (8/10/2010)
Some DBAs feal this is better managed by increasing the Cost Threshold for Parallelism, typically to 30 seconds.Just one comment - cost threshold is not measured in seconds. It's the same unitless cost measurement that's used in execution plans.
Not according to this MS article: http://technet.microsoft.com/en-us/library/ms188603.aspx
" The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration"
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 10, 2010 at 8:00 pm
thanks...what exactly is cost of threshold? I was in an assumption that when you set to 5 it mean sql engine will calculate estimate excution plan and if the query cost is more than 5 it will let the queries to run in parallel( depends on what the value is set of MAXDOP on server level). Is this a correct statement?
August 10, 2010 at 8:41 pm
Have a read here: http://technet.microsoft.com/en-us/library/ms188603.aspx
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 10, 2010 at 10:13 pm
Ok, got it now. As per the article if i set MAX DOP to 1 it will not be effective even if i change the cost threshold value but if i am using MAX DOP as query hints changing cost threshold will be effective right? I do have a query in which we are using max dop as 4 how can i find a value for cost of threshold?
August 10, 2010 at 10:36 pm
Cost Threshold is instance wide and doesn't have a query hint option, you can see the value if you do an sp_configure.
I would assume if you set MAXDOP = 0 via sp_configure, then use a query hint of MAXDOP = 4, that SQL will still use the Cost Threshold to determine if parallelism is required for that query, but I've never actually tested this.
If you hace a test server try it, set cost threshold very high and see if the query still goes parallel.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 10, 2010 at 11:09 pm
The 'cost threshold for parallelism' setting applies to the whole SQL Server instance.
The query optimiser will only consider looking for parallel plan alternatives if the best serial plan it finds (after stage one optimisation) has an estimated cost greater than the value stored in 'cost threshold' setting. Stage one optimisation only considers serial plans.
Some people find the default setting of 5 too low. I have yet to hear a convincing argument for this, especially on a server that has instance-wide MAXDOP set to 1, with query hint MAXDOPs used as an override. Presumably, one would only override MAXDOP in this way on queries that would definitely benefit from a parallel plan - regardless of what the optimiser thinks the estimated cost is.
Paul
August 11, 2010 at 6:54 am
Paul White NZ (8/10/2010)
The 'cost threshold for parallelism' setting applies to the whole SQL Server instance.The query optimiser will only consider looking for parallel plan alternatives if the best serial plan it finds (after stage one optimisation) has an estimated cost greater than the value stored in 'cost threshold' setting. Stage one optimisation only considers serial plans.
Some people find the default setting of 5 too low. I have yet to hear a convincing argument for this, especially on a server that has instance-wide MAXDOP set to 1, with query hint MAXDOPs used as an override. Presumably, one would only override MAXDOP in this way on queries that would definitely benefit from a parallel plan - regardless of what the optimiser thinks the estimated cost is.
Paul
I have a very convincing reason why I state that the CTFP default of 5 is almost universally too low: IO subsystems are almost universally underpowered compared to modern server CPU counts. Oh, and most systems are also suboptimally indexed, leading to lots of table scans and parallel queries, which simply lead to even worse CXPACKET waits where your 16 to 24 2.5GHz CPUs are twiddling their electronic thumbs at an incredible clip. 🙂
EDIT: the MAXDOP setting of 1 just reached my coffeeless brain. My statement above doesn't apply to this. But I have never, ever seen your scenario in the wild. :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 11, 2010 at 10:36 am
thanks...but i am little lost here. Mentioned below few questions which will help me in understanding better
i) if MAXDOP is zero - cost threshold value doesnt matter since sql engine will push the query to all the processsors right?
ii) if maxdop is not zero and some other value like 2 and if i used query hint in my select statement with MAXDOp as 4 does cost threshold still count or no matter wat the cost threshold value is sql will push the queries across 4 CPUS?
thanks...
August 11, 2010 at 11:40 am
iqtedar (8/11/2010)
i) if MAXDOP is zero - cost threshold value doesnt matter since sql engine will push the query to all the processsors right?
No. The cost threshold determines when SQL may switch to running the query in parallel. Cheap queries will run on one processor
ii) if maxdop is not zero and some other value like 2 and if i used query hint in my select statement with MAXDOp as 4 does cost threshold still count or no matter wat the cost threshold value is sql will push the queries across 4 CPUS?
Again, cost threshold determines when SQL may switch to a parallel plan. If you put the hint MAXDOP 4 in a query, you're not saying that the query *must* run on 4 processors, only that if it parallels it may not parallel to more than 4 processors..
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2010 at 11:41 am
iqtedar (8/11/2010)
i) if MAXDOP is zero - cost threshold value doesnt matter since sql engine will push the query to all the processsors right?
No. The instance-wide MAXDOP setting sets the maximum possible degree of parallelism. A setting of zero (the default) allows SQL Server to use as many threads as there are processor cores that it is configured to use.
The cost threshold value is only used by the optimiser. If the cost threshold is set to 5, the query must have an estimated cost of at least 5 before the optimiser will look for a parallel plan alternative.
ii) if maxdop is not zero and some other value like 2 and if i used query hint in my select statement with MAXDOp as 4 does cost threshold still count or no matter wat the cost threshold value is sql will push the queries across 4 CPUS?
The MAXDOP query hint just overrides the default server setting, and specifies the maximum number of concurrently executing threads SQL Server will use to execute a parallel plan for that query.
Summary:
The 'cost threshold' determines how expensive a query must be before SQL Server considers parallelism.
The MAXDOP setting determines the maximum number of concurrent threads SQL Server can use for that query.
Paul
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply