March 7, 2014 at 5:22 am
This wait type occurs because a query has been "parallelised" and the organizer/coordinator thread is waiting for all the parallel threads to complete.
We can remove all CXPACKET waits by just setting MAXDOP to 1, or for particular queries offer a query hint.
What is the big deal with seeing high CXPACKET waits.
If you remove this particular wait type for any query, then that query may run slower because the parallel operations are disabled for the query.
The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:
•The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
•A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
•The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.
Is the controller thread taking up a thread that could be assigned to some other workload?
If you had lots of queries waiting on completing threads there would obviously become a bottleneck, so if the CXPACKET wait type was seen with associated THREADPOOL waits etc then it would indicate a problem but not on its own?
Can someone explain what the performance downsides to this wait type are?
EDIT:
*Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of threads that are being used http://msdn.microsoft.com/en-us/library/ms178065.aspx
March 7, 2014 at 6:53 am
CXPACKET waits in and of themselves are no big deal. They're just indications of parallelism occurring within your system. Now, you may be seeing parallelism for some queries that are not benefiting from it, but usually, the best solution there is not setting MAXDOP = 1 on the server or on a query hint. The best solution is to up the default value for Cost Threshold for Parallelism which is set to 5, a value that is much too low (opinion). I used to set it to 35 when I set up a server. Lately, I've been reexamining that and setting it to 50.
But, CXPACKET can be an indication of excessive parallel waits, however you need to associate it with some other metrics in order to be sure of this. On it's own, it doesn't mean much of anything at all. Or rather, it doesn't tell you enough. I'd look for excessive PAGEIOLATCH_* waits indicating waits on disk I/O through parallel paths in addition to this one. I'd also look at signal wait times in general being higher as other processes have to wait on CPU resources being consumed by the parallel executions. In short, while you want to know, for a specific query if it's being appropriately executed in parallel or not, so CXPACKET can be an indicator at that tactical level, on a server level, it doesn't tell you much. You have to look to the other measures in order to be able to evaluate CXPACKET.
It's not a wait I would look to eliminate, but one that I would mitigate depending on the circumstances.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 7, 2014 at 7:22 am
Grant Fritchey (3/7/2014)
It's not a wait I would look to eliminate, but one that I would mitigate depending on the circumstances.
I agree. +100
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 7, 2014 at 9:22 am
Question: if my database files sit all on one drive, will my query benefit from parallelism (even if query cost > threshold)?
March 7, 2014 at 9:42 am
SQL Guy 1 (3/7/2014)
Question: if my database files sit all on one drive, will my query benefit from parallelism (even if query cost > threshold)?
Yes you could benefit from parallelism in that scenario. It is something that should be tested and baselined in your particular setup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 7, 2014 at 10:41 am
SQLRNNR (3/7/2014)
SQL Guy 1 (3/7/2014)
Question: if my database files sit all on one drive, will my query benefit from parallelism (even if query cost > threshold)?Yes you could benefit from parallelism in that scenario. It is something that should be tested and baselined in your particular setup.
But, the key word in that sentence is "could." It really depends on the query, what it's doing, etc.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply