March 18, 2013 at 12:51 pm
I have a SQL 2008R2 server with 16 cores. At the server level i have CTP set to 20 and MAXDOP value set to 8. If i understand correctly , any query whose cost is more than 20 can use upto 8 cores and if cost is less than 20 can use upto all cores. Does sql engine calculate estimated cost and then spin in different threads?
March 18, 2013 at 3:42 pm
sqldba_newbie (3/18/2013)
I have a SQL 2008R2 server with 16 cores. At the server level i have CTP set to 20 and MAXDOP value set to 8. If i understand correctly , any query whose cost is more than 20 can use upto 8 cores and if cost is less than 20 can use upto all cores.
Not quite. Any query whose cost is more than 20 can have a query operator use up to 8 cores and if cost is less than 20 each operator will use only one core.
Does sql engine calculate estimated cost and then spin in different threads?
The Query Optimizer determines whether a plan will take advantage of parallelism.
edit: clarify around "operator"
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 18, 2013 at 4:00 pm
Does sql engine calculate estimated cost and then spin in different threads?
The Query Optimizer determines whether a plan will take advantage of parallelism.
edit: clarify around "operator"[/quote]
based on what?
March 18, 2013 at 4:34 pm
Cost estimate compared to the Cost threshold for parallelism setting. The QO is a cost-based optimizer.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 18, 2013 at 4:42 pm
sqldba_newbie (3/18/2013)
At the server level i have CTP set to 20 and MAXDOP value set to 8. If i understand correctly , any query whose cost is more than 20 can use upto 8 cores and if cost is less than 20 can use upto all cores.
If a query has a cost less than 20, the optimiser will not consider parallelism. If it has a cost more than 20 it will consider a parallel plan, if it creates one then, when that plan is run, the parallel operators can parallel up to 8 ways.
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
March 18, 2013 at 10:23 pm
opc.three (3/18/2013)
Cost estimate compared to the Cost threshold for parallelism setting. The QO is a cost-based optimizer.
Thanks. Do you mean Cost from estimate plan?
March 19, 2013 at 7:38 am
sqldba_newbie (3/18/2013)
opc.three (3/18/2013)
Cost estimate compared to the Cost threshold for parallelism setting. The QO is a cost-based optimizer.Thanks. Do you mean Cost from estimate plan?
When a query is first run the query optimizer evaluates MANY things and develops potentially large numbers of permutations of solutions to bring about the effect/output of said query. EVERYTHING the optimizer chooses to do has some unitless mathematical value assigned to it. Query plan cost is just a summation of all the pieces-parts of the plan under current review. At this point EVERYTHING is SERIAL in nature, and the optimizer picks the lowest cost plan amongst those it evaluated. If that lowest cost plan is greater than the Cost Threshhold for Parallelism then the query optimizer may consider another pass of optimization, this time evaluating PARALLEL plans. If the lowest cost parallel plan falls out of this as having a smaller mathematical cost than the lowest cost SERIAL plan then the parallel plan is stored for execution. The DOP of said plan will not be higher than MAXDOP setting of server or the MAXDOP query hint if one is used, whichever is lower.
Now, when a plan is pulled from cache and EXECUTED, the query processing ENGINE evaluates the current server load from a variety of perspectives and MAY decide to reduce the parallelization of the plan at run-time to try to avoid overwhelming the box (or adding to an already bad situation) - including dropping DOP all the way back to 1, i.e. a serial plan.
NOTE: this is an EXTREME simplification of what really happens, but I hope it helps. Unless you have some particular problem we can assist with or are simply very curious this information is way more than most DBAs/DEVs really need. If you do want more information I feel that the forum isn't the place for it - time to hit the books/blogs/classes for a deep dive. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2013 at 7:46 am
sqldba_newbie (3/18/2013)
opc.three (3/18/2013)
Cost estimate compared to the Cost threshold for parallelism setting. The QO is a cost-based optimizer.Thanks. Do you mean Cost from estimate plan?
Yes. There is only an estimated plan at the point before execution begins. Optimization phase generates estimated plans. It is during the execution phase when the estimated plan is put into action.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 19, 2013 at 7:56 am
TheSQLGuru (3/19/2013)
The DOP of said plan will not be higher than MAXDOP setting of server or the MAXDOP query hint if one is used, whichever is lower.
The query hint overrides the server setting, so it is:
The maxdop hint value if specified. If not, the applicable resource governor maxdop setting, if specified. If not, the server maxdop setting or max processors available if that is 0.
Edit: Can't actually remember if the hint can override the resource govenor setting. It certainly can override the server setting.
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
March 19, 2013 at 9:11 am
GilaMonster (3/19/2013)
TheSQLGuru (3/19/2013)
The DOP of said plan will not be higher than MAXDOP setting of server or the MAXDOP query hint if one is used, whichever is lower.The query hint overrides the server setting, so it is:
The maxdop hint value if specified. If not, the applicable resource governor maxdop setting, if specified. If not, the server maxdop setting or max processors available if that is 0.
Edit: Can't actually remember if the hint can override the resource govenor setting. It certainly can override the server setting.
I had to go look it up and don't have a testbed at the moment to verify, but if the docs are accurate here then the Resource Governor setting will take precedence over the Query Hint:
If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 19, 2013 at 9:20 am
Ok, so the effective MAXDOP for a query is then the minimum of the query hint value and resource governor value (if specified), otherwise the server maxdop setting.
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
March 19, 2013 at 9:55 am
opc.three (3/19/2013)
sqldba_newbie (3/18/2013)
opc.three (3/18/2013)
Cost estimate compared to the Cost threshold for parallelism setting. The QO is a cost-based optimizer.Thanks. Do you mean Cost from estimate plan?
Yes. There is only an estimated plan at the point before execution begins. Optimization phase generates estimated plans. It is during the execution phase when the estimated plan is put into action.
So if i look at the cost from estimated execution plan, lets say cost of a query from estimated exec plan is 40. Does that mean my query will use upto 8 cores during run time? If that's the case, that's not what i see. Even though estimated cost is well over 20, i still see query using all the cores?
March 19, 2013 at 10:18 am
sqldba_newbie (3/19/2013)
So if i look at the cost from estimated execution plan, lets say cost of a query from estimated exec plan is 40. Does that mean my query will use upto 8 cores during run time?
It means that the query may use processors up to the effective maxdop setting at execution time.
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
March 19, 2013 at 11:26 am
Even though estimated cost is well over 20, i still see query using all the cores?
Cores, or threads? What exactly are you seeing that is making you think it is using all cores?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 19, 2013 at 11:44 am
opc.three (3/19/2013)
Even though estimated cost is well over 20, i still see query using all the cores?
Cores, or threads? What exactly are you seeing that is making you think it is using all cores?
Server has 16 cores, i see 17 threads
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply