August 11, 2010 at 8:34 pm
thanks Paul and Gail.Based on your statements it makes me believe that irrespective of the value of MAXDOP, Cost threshold will always play a role?
August 11, 2010 at 8:38 pm
iqtedar (8/11/2010)
irrespective of the value of MAXDOP, Cost threshold will always play a role?
Not true, Cost Threshold will play a role if MAXDOP is NOT EQUAL TO 1.
This is true for they sp_configure value of MAXDOP and if you over ride the system value with a query hint.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 11, 2010 at 8:52 pm
iqtedar (8/11/2010)
thanks Paul and Gail.Based on your statements it makes me believe that irrespective of the value of MAXDOP, Cost threshold will always play a role?
Yes, I think you've got the idea now. As Leo says, the only exception is if the effective MAXDOP for the query is 1 - in which case you are telling SQL Server to only ever generate serial plans, so the cost threshold isn't important in that particular case.
Otherwise, yes: cost threshold is independent of MAXDOP.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 11, 2010 at 11:13 pm
iqtedar,
Here is another article you may want to check:
http://www.sqlservercentral.com/articles/Configuring/managingmaxdegreeofparallelism/1029/
--Vadim.
--Vadim R.
August 12, 2010 at 9:49 am
yeah makes sense it wouldnt use COST threshold with DOP 1 (forgot my math...).. Just one last question. Now i am trying to create a baseline and have some good results before i present this change to prod. But i am i do not where to start from . I request if someone has done this before please advice me, mentioned below are few things i would like to capture and analyze
i) First i will need to know what counters or dmv's should i use to capture data based on the current setup so that i can compare it after i change the settings. One thing i can think of i using this view "sys.dm_os_wait_stats" and capture for entire day. I would clear the data in this view before i start capturing . I am planning to look only at the CXPACKET count and a decrease in this value after the changing Cost threshold and max dop should be an indication of improvement in performance ? Is this right?
ii) Should i only capture certain queries which are giving the issue or should i do i monitor through out the environment for few days. I would imagine since i am changing at server i would monitor the entire sql instance?
Thanks
August 16, 2010 at 3:17 am
" The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration"
Having heard an explanation of this from the Microsoft Product Manager in charge of this area of code, both Leo and Gail are right. (Gail probably already knows this :-))
The cost threshold does relate to the execution time units given by the optimizer, as Gail says. These units have a duration of approximately 1 second on a specific hardware configuration. My memory is not too sure at this stage, but I think the specific hardware was a 300 MHz Pentium. This means that a single optimizer unit lasts for considerably less than 1 second on the 3GHz machines of today.
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 16, 2010 at 3:34 am
Another aspect of MAXDOP is that it applies to each portion of the execution plan, not to the whole plan.
If you are doing a SELECT from a single table using a table scan, there is only one portion to the query. If your SELECT uses a non-covering index, there are at least 2 portions to your query. If your SELECT is joining two tables and each table is accessed via a non-covering index you have at least 4 portions to your query, etc.
If your execution plan shows that some portions of the query are not inter-dependent (such as separate dimension table lookups), then these may run in parallel even if MAXDOP is set to 1, wit hthe result that your query may at times use more than 1 CPU. If you have MAXDOP set to (say) 4, then these lookups can each use up to 4 CPUs when they are running.
When a CPU is in use by a given query, it cannot be used by any other process. However, there are lots of situations (called interrupts) that cause the query to give up use of the CPU. A very common interrupt is the need to read data from disk. While the data is being read, the query is forced to give up use of the CPU until the data is available. Also, SQL Server will generate regular interrupts to force a query to give up its CPU - but if nothing else needs the CPU then the query will immediately resume work. A full list of possible interrupts would take many pages to complete, but the important thing to remember is that if your query takes 5 seconds to complete, it does not mean it has had exclusive use of a CPU for 5 seconds.
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 16, 2010 at 6:35 am
EdVassie (8/16/2010)
Another aspect of MAXDOP is that it applies to each portion of the execution plan, not to the whole plan.
In SQL Server 2005 and later, MAXDOP specifies the maximum number of concurrently running threads the query uses. Areas of a plan bounded by Exchange iterators may each be allocated up to MAXDOP threads - so there may be more than MAXDOP threads associated with a query, but only MAXDOP of them can run (be in the 'running' state in sys.dm_os_tasks) at one time. There was a bug in SQL Server 2000 that meant that queries with multiple Exchanges could end up running more than MAXDOP threads concurrently.
If you are doing a SELECT from a single table using a table scan, there is only one portion to the query. If your SELECT uses a non-covering index, there are at least 2 portions to your query. If your SELECT is joining two tables and each table is accessed via a non-covering index you have at least 4 portions to your query, etc.. If your execution plan shows that some portions of the query are not inter-dependent (such as separate dimension table lookups), then these may run in parallel even if MAXDOP is set to 1, wit hthe result that your query may at times use more than 1 CPU. If you have MAXDOP set to (say) 4, then these lookups can each use up to 4 CPUs when they are running.
A serial plan always runs on a single worker thread, on a single scheduler, to completion. Serial plans never execute on more than one logical execution unit (core), though the operating system may move this one thread between physical execution units (unless the corresponding affinity mask bit is set on). That said, I/O requests are often processed asynchronously on a separate thread managed by the storage engine. Perhaps that is where the confusion has arisen.
When a CPU is in use by a given query, it cannot be used by any other process. However, there are lots of situations (called interrupts) that cause the query to give up use of the CPU. A very common interrupt is the need to read data from disk. While the data is being read, the query is forced to give up use of the CPU until the data is available. Also, SQL Server will generate regular interrupts to force a query to give up its CPU - but if nothing else needs the CPU then the query will immediately resume work. A full list of possible interrupts would take many pages to complete, but the important thing to remember is that if your query takes 5 seconds to complete, it does not mean it has had exclusive use of a CPU for 5 seconds.
SQLOS is co-operatively multi-tasked; it is not pre-emptively scheduled (with the exception of certain SQLCLR operations that leave managed code). SQL Server certainly does not use interrupts as you describe. The SQL Server code is very carefully written to regularly voluntarily yield control to allow other tasks to run. This has been true since the User Mode Scheduler was introduced in SQL Server 7.0, precisely to avoid the inefficiencies associated with running a database server under a pre-emptively multi-tasked operating system like Windows.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 16, 2010 at 6:39 am
Paul, thanks for the clarification.
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 16, 2010 at 8:41 am
so does cost of threshold mean the time taken to complete the query when ran in serial?
August 16, 2010 at 8:47 am
does cost of threshold mean the time taken to complete the query when ran in serial
Sort of... The optimizer tries to work out how long the query would take to run, and if it estimates it will take more than 5 units then the optimizer will consider using parallelism for the execution plan.
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 16, 2010 at 9:23 am
Ok. Thanks. I am trying to do some analysis by changing cost threshold and max dop. I have actually opened another topic here to get some baselines for MAXDOP, if you get sometime i would appreciate your input for that. Thanks
August 17, 2010 at 4:23 am
It looks like nobody has addressed these questions, so let me chime in.
iqtedar (8/12/2010)
i) First i will need to know what counters or dmv's should i use to capture data based on the current setup so that i can compare it after i change the settings. One thing i can think of i using this view "sys.dm_os_wait_stats" and capture for entire day. I would clear the data in this view before i start capturing . I am planning to look only at the CXPACKET count and a decrease in this value after the changing Cost threshold and max dop should be an indication of improvement in performance ? Is this right?
You don't need to clear the wait counts. Just capture it at regular intervals and then calculate the changes when you analyze it. Also, take a look at capturing the data in sys.dm_os_performance_counters.
No, it is not right to say that a decrease in CXPACKET waits is an improvement in performance. CXPacket waits are not a bad things for the most part. It merely indicates that queries have been parallelized. And why does SQL parallelize queries? For better performance. There is a point where too much is too much and excessive CXPackets can be an indicator of problems. But your goal is to find a happy balance, not to alleviate CXPacket waits.
There are very few systems out there that actually run better with MaxDOP set to 1. I'd be wary of anybody who advises you set MaxDOP to 1. Don't do it without a strong case for it. And ignore anybody who says that all OLTP systems should be set to MaxDOP = 1. That's an urban myth!!
iqtedar (8/12/2010)
ii) Should i only capture certain queries which are giving the issue or should i do i monitor through out the environment for few days. I would imagine since i am changing at server i would monitor the entire sql instance?
Right now, you are baselining the system. This is not a targeted attack. This is measuring the health of the system as a whole. Save the targeted attack for another day.
August 17, 2010 at 4:34 am
I also want to add 1 more thing about parallel plan generation.
Connor Cunningham, who helped develop the QP engine, talked to us during my MCM rotation about how the QP engine generates parallel plans. According to Connor, SQL Server always generates a serial and a parallel plan (unless maxDOP = 1) and then chooses which one use based on the cost. So the cost helps the engine decide which one to use, not which one to generate.
Additionally, the query engine may choose to use a serial plan even after the QP gives it a parallel plan. The parallel plans are generated ina fashion that makes it easy for the query engine to strip out the parallelism pieces and run it as a serial plan. On the other hand, it will never run a query in parallel if it was given a serial plan. It won't generate the parallel pieces on the fly.
This is often misunderstood, because only the plan that is sent to the query engine is cached. This has mislead people to believe that the QP only generates a single plan.
EDIT: I guess that was more than just 1 more thing. :Whistling:
August 17, 2010 at 7:09 am
Hey Robert,
Robert Davis (8/17/2010)
SQL Server always generates a serial and a parallel plan (unless maxDOP = 1) and then chooses which one use based on the cost. So the cost helps the engine decide which one to use, not which one to generate.
This is only the case if the query being compiled skips Trivial Plan, and if a good enough serial plan is not found in the first phase of applying exploration and implementation rules to the algebrised expression tree.
Phase 1 only considers serial plans, and optimisation will end there if a 'good enough' plan is found. It's fairly easy to determine which phase parallel plans are generated in by monitoring the search(n) columns of sys.dm_exec_query_optimizer_info DMV.
Additionally, the query engine may choose to use a serial plan even after the QP gives it a parallel plan. The parallel plans are generated in a fashion that makes it easy for the query engine to strip out the parallelism pieces and run it as a serial plan.
Strictly, the execution engine might generate a serial execution context from a parallel compiled plan, if the current server workload dictates. This is an relatively rare occurrence, however, and frequently sub-optimal, since the best serial plan may well have a very different shape from the best parallel plan.
Parallel plan execution contexts are never cached, and a serial EC generated from a parallel plan is not cached either.
This is often misunderstood, because only the plan that is sent to the query engine is cached. This has mislead people to believe that the QP only generates a single plan.
I'm not quite sure what you're saying here. QO does only produce a single plan as its output, which is copied from the memoized structure, reformatted, and placed in the plan cache prior to execution.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply