SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CXPACKET wait type


CXPACKET wait type

Author
Message
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12599 Visits: 8560
chileu17 (10/21/2008)
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.


Hmm, I thought that "add more RAM" and "add more physical spindles" was pretty self-explanatory. BigGrin

It really is that simple though. Get data through the CPUs more quickly (which RAM and IO perf boost will do) and CXPACKETwaits drop down.

Other things: cut read requirements by a) better indexing b) refactoring query to reduce data pulled/accessed, lower maxdop (already covered in depth), ensure all IO config options are set right between sql server and SAN (HBA [queue depth?, cache ratios and size?), SAN cache, drivers up to date?, etc). There are other things as well.

Best is to get a pro to come in and give your system a performance review. We have hardly touched the surface here.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Marios Philippopoulos
Marios Philippopoulos
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4608 Visits: 3756
Well-tuned queries - in an OLTP system - genarally should not parallellize. This is almost certainly a case of a suboptimally tuned system.

Is auto-updatestats, auto-createstats turned on for your databases?

Do you regularly carry out index defragmentation?

Are your queries properly indexed (as was already mentioned)?

Is your tempdb properly configured?

These are some of the questions to consider.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4608 Visits: 3756
Also, gather some of your largest tables, those most actively queried in your system.

Do "sp_helpindex tblName" to get a list of indexes for each table.

Run "DBCC SHOW_STATISTICS ('tblName', 'idxName')" on the tables and select indexes.

Is the Rows Sampled equal to the Total Rows in the index?

If not, you may want to run "UPDATE STATISTICS tblName WITH FULLSCAN" to ensure stats is maintained on the table on the basis of the full data distribution (as opposed to a subset sample). I have often found this to solve my performance problems.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
OomBoom
OomBoom
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 514
WE had an IO problem on RAID 5. Change to RAID 1 0 if possible. It will improve IO alot. Add more physical drives to the SAN drive. Put the log files on a different SAN drive with different physical drives.

Also get your SAN provider to diagnose the SAN. We have a cache problem that fills up on the controller and then slows the SAN performance drastically.

This will all improve your IO capabilities.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
vliet
vliet
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 763
I've seen this problem more often on SQL 2005 than on SQL 2000 instances, but I must agree servers tend to get more cores everyday. On some instances used mainly for OLTP I set the maximum number of processors per query to one, thereby effectively disableing parallel queries. This sometimes improves both query performance and throughput but it is not an options if you want to perform some serious reporting on a server.

But there is no general solution. I/O seems to be the bottleneck but in some cases I could greatly reduce query execution time (and CXPACKET waits) by using the OPTION LOOP JOIN. No two cases are the same and if a SAN is involved, things are getting more complicated because most SAN experts are not used to the demands of SQL server regarding caching, write order, and many other aspects of disk I/O. Getting a SQL server up to speed on a SAN is far from simple, and requires a specialist with knowledge and experience on both subjects.

As mentioned by others, up-to-date statistics won't ever harm your queries. More memory does not always improve performance; a very large table might still not fit into memory, and thus a table scan on this table might still require massive disk I/O. In this case profiling will offer you some insight in missing indexes that could increase performance, but as always, it depends ...
Abe Miessler
Abe Miessler
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 45
I'm having similar issues on a report i'm working on which does a lot of select into temorary tables before it does the final select that returns the results. I tried changing the MAXPOD to 2 and then 4 (out of a total of 8) for every single select in my stored procedure but have had no luck yet. Could be because of my use of temporary tables to hold results? Any other suggestions for getting rid of CXPACKET wait type?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88166 Visits: 45277
Please post new questions in a new thread in future.

If you look while the query is running, there will be at least one thread that doesn't have a cxpacket, that has a different wait type. Whatever that is is what's holding the query up. What is it?

I usually recommend generic performance tuning (tune the query, tune the indexes) before panicking over CXPackets. It's quit common to see them on non-optimised queries.

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


Chris Taylor
Chris Taylor
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 1910
they're also common when your server has ran out of VAS (Virtual Address Space)

_________________________________________________________________________________

SQLGeordie

Web:- Jarrin Consultancy
Blog:- www.chrisjarrintaylor.co.uk
Twitter:- @SQLGeordie
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88166 Visits: 45277
Swirl, please explain that?

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


Chris Taylor
Chris Taylor
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 1910
I wrote a blog regarding issues i was having with that:
http://www.sqlservercentral.com/blogs/christaylor/archive/2009/05/04/sql-server-cpu-s-at-100-anyone-checked-the-vas-virtual-address-space.aspx

I have however just realised i've negated to include in the blog the fact that we got a lot of CXPacket wait types when we hit this issue.

_________________________________________________________________________________

SQLGeordie

Web:- Jarrin Consultancy
Blog:- www.chrisjarrintaylor.co.uk
Twitter:- @SQLGeordie
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