Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

CXPACKET wait type Expand / Collapse
Author
Message
Posted Tuesday, October 14, 2008 11:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 12:13 PM
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.
Post #585961
Posted Wednesday, October 15, 2008 1:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
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

Post #586004
Posted Thursday, October 16, 2008 2:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:59 AM
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,
Post #587306
Posted Friday, October 17, 2008 8:03 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 4,439, Visits: 6,347
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
Post #587649
Posted Friday, October 17, 2008 8:46 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:54 AM
Points: 9,294, Visits: 9,492
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."
Post #587693
Posted Monday, October 20, 2008 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:59 AM
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
Post #588602
Posted Monday, October 20, 2008 11:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 4,439, Visits: 6,347
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. :)


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #588714
Posted Monday, October 20, 2008 1:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:59 AM
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 :)
Post #588785
Posted Monday, October 20, 2008 4:03 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 4,439, Visits: 6,347
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 :)


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. :D 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.



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #588854
Posted Tuesday, October 21, 2008 3:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:59 AM
Points: 119, Visits: 401
Sorry SqlGuru 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 please any suggestions are welcome. Thanks in advance again.
Post #589472
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse