Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CXPACKET wait type


CXPACKET wait type

Author
Message
flyme4ual
flyme4ual
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 192
Hi guys,

I'm seeing CXPACKET wait types in one of my database running SQL2005 which causing the CPU hit 100%. The server is 64 bit Windows 2003 with 8 CPU. Several tables contain over 10 million rows. Does anyone encounter this issue before and what was done to fix the problem?

Thanx.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
CXPacket is wait caused by a parallel skew. What happens is that SQL runs a query in parallel and, for whatever reason, one of more of the threads lags behind. The ones that finish have to wait for the slower ones to catch up. That wait is the CXpacket wait

You can either reduce the max degree of parallelism for the entire server or, if it's a specific query that's giving the problem, you can add the maxdop hint to that query.

Be careful if you do the former, as it will affect the entire server. If you decide to go that route, I would suggest setting the server's max degree of parallelism to 4 (half the number of cores that you have) and see how it goes from there.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


chileu17
chileu17
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 401
Hi everyone I am having a similar problem here, well on my dev server I have 4 cpu, I dunno why I get so much CXPACKET wait time, well my maxdop is 0, should I set it to 2? so I can get a better performance?
Thanks for your help,
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8298
At least 75% of my clients have multi-cpu servers with insufficient RAM or IO capabilities (or both!), and thus suffer from significant CXPACKET waits on largish queries. It is often optimal in general in situations like that to simply set MAXDOP at the server level to some fraction of the total number of CPUs (1/4 to 1/2 usually). In other cases you can specify the maxdop as an OPTION for an individual query.

NOTE: if you have hyperthreading enabled it gets MUCH worse. Consider disabling HT if you are seeing logs of CXPACKET waits first, then reevaluating and if necessary adjust MAXDOP.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
chileu17 (10/16/2008)
Hi everyone I am having a similar problem here, well on my dev server I have 4 cpu, I dunno why I get so much CXPACKET wait time, well my maxdop is 0, should I set it to 2? so I can get a better performance?

I would at least try it to see if it helps.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
chileu17
chileu17
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 401
TheSQLGuru (10/17/2008)
At least 75% of my clients have multi-cpu servers with insufficient RAM or IO capabilities (or both!), and thus suffer from significant CXPACKET waits on largish queries. It is often optimal in general in situations like that to simply set MAXDOP at the server level to some fraction of the total number of CPUs (1/4 to 1/2 usually). In other cases you can specify the maxdop as an OPTION for an individual query.

NOTE: if you have hyperthreading enabled it gets MUCH worse. Consider disabling HT if you are seeing logs of CXPACKET waits first, then reevaluating and if necessary adjust MAXDOP.


Thanks we are gonna try that out because this CXPACKET wait is just killing our performance
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8298
Probably the BEST thing you can do (if able) is to a) max out the servers RAM (which is hopefully many, many GBs) and b) add more spindles to the IO mix to improve IO performance.

I also recommend (regularly on performance forums such as this) that you get a professional to come in (onsite or remote) and give your systems a performance review. There are a pleathora of things that can be done suboptimally that will hurt performance. Smile

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
chileu17
chileu17
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 401
yeah we are thinking on calling some MS performance expert on this matter, because we don't seem to get a better performance in any of our attempts. But just to try something new, what did you mean with more spindles to the IO mix to improve IO performance?
Thanks for the help guys Smile
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8298
chileu17 (10/20/2008)
yeah we are thinking on calling some MS performance expert on this matter, because we don't seem to get a better performance in any of our attempts. But just to try something new, what did you mean with more spindles to the IO mix to improve IO performance?
Thanks for the help guys Smile


CXPACKET waits are due to parallelism where the query optimizer decides to break up the IO and processing for a query into multiple threads that run concurrently then it gathers the data back together to provide the necessary output. In pretty much every case, the CPUs wait for IO streams because the IO subsystem is slow as molasses in February, relatively speaking. BigGrin So by providing better IO capabilities you can feed those GHz-speed CPUs with data with fewer waits --> much faster parallel query performance. This is the 10000 foot view, btw. There is just a weeeee bit more to it than this. Cool

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
chileu17
chileu17
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 401
Sorry SqlGuru Hehe I think I couldn't explain to you my question. I do understand your point, BUT how do I implement what you are talking about?More indexes?new transactions?I know that the most probable answer is that it depends on every case right lol well I have a dedicated server with no other apps running on it. It runs on Windows server 2003 with a HD of 150 gb with a redundant SAN. We have installed a RAID 5 on it Sad please any suggestions are welcome. Thanks in advance again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search