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


very high CX packet waits


very high CX packet waits

Author
Message
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
i have noticed very high CXPacket waits on one of our dataware house server. All these queries are just select with bunch of joins. I couldn't find any missing indices. At the server level CTP was set to 5 and maxdop to 0. I changed the CTP value to 32 and maxdop to 1 (server has 24 logical cores), since then i dont see any CXPacket waits. I know tweaking maxdop is not the best way, any recommendations on how to avoid this . Since i changed the MAXDOP values queries started running much faster.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
When a parallel operation is created for SQL Query, there are multiple threads for a single query. Each query deals with a different set of the data (or rows). Due to some reasons, one or more of the threads lag behind, creating the CXPACKET Wait Stat.

where the transactions are smaller and queries are not long but very quick usually, set the “Maximum Degree of Parallelism” to 1 (one). This way it makes sure that the query never goes for parallelism and does not incur more engine overhead

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
Another thing which we need to consider is ,look for queries that run under parallelism and test them manually using different levels of DOP using the OPTION(MAXDOP n) query hint to see if reducing parallelism actually improves or harms performance.

Tune the query before tuining the server

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
Bhuvnesh (1/28/2013)
Another thing which we need to consider is ,look for queries that run under parallelism and test them manually using different levels of DOP using the OPTION(MAXDOP n) query hint to see if reducing parallelism actually improves or harms performance.

Tune the query before tuining the server


Thanks. You still didnt answer my question. As mentioned in my post i am completely aware that i shouldn't be tweaking with MAXDOP settings, my question was why is my server performing better after changing it to 1 and any recommendations on my query.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
sqldba_newbie (1/28/2013)
You still didnt answer my question.
i cant , without seeing the actual query exec plan (before changes and after changes), post the plans then based on that i will try.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
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
If you want all queries to only use one processor, why not pull the other 23 out? That's essentially what you've done by setting maxdop to 1. It is NOT a good thing to do.

Of course you won't see any CXPacket waits with maxdop at 1. Maxdop of 1 means never parallel.

Maxdop should probably be changed from the default, but not to 1. See the last section here: https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

CXPacket waits are not an indication of poor performance. They're an indication that queries are running in parallel. That is all. If you have lots and lots and lots of CXPacket, then, to reduce them and improve performance, you need to look for the other waits. In any query that's got huge CXPacket waits, there will be one or more threads that have some other wait type. That's the wait type that you need to investigate and resolve.

Look for the other wait types
Optimiser your queries
Increase cost threshold for parallelism
Set maxdop back to something > 1


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


curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
GilaMonster (1/29/2013)
If you want all queries to only use one processor, why not pull the other 23 out? That's essentially what you've done by setting maxdop to 1. It is NOT a good thing to do.


If there are 10 processes running wouldnt they use 10 different core's if MAXDOP is set to 1?


Of course you won't see any CXPacket waits with maxdop at 1. Maxdop of 1 means never parallel.

Maxdop should probably be changed from the default, but not to 1. See the last section here: https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

CXPacket waits are not an indication of poor performance. They're an indication that queries are running in parallel. That is all. If you have lots and lots and lots of CXPacket, then, to reduce them and improve performance, you need to look for the other waits. In any query that's got huge CXPacket waits, there will be one or more threads that have some other wait type. That's the wait type that you need to investigate and resolve.

Look for the other wait types
Optimiser your queries
Increase cost threshold for parallelism
Set maxdop back to something > 1


I do see heavy waits on PAGEIOLATCH_SH. Do you have query to find total wait on waitype PAGEIOLATCH_SH for a particular spid?
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
sqldba_newbie (1/29/2013)
GilaMonster (1/29/2013)
If you want all queries to only use one processor, why not pull the other 23 out? That's essentially what you've done by setting maxdop to 1. It is NOT a good thing to do.


If there are 10 processes running wouldnt they use 10 different core's if MAXDOP is set to 1?


Probably, but you're still throttling your server, especially a data warehouse-type server that will typically be running smaller numbers of large queries.

I do see heavy waits on PAGEIOLATCH_SH. Do you have query to find total wait on waitype PAGEIOLATCH_SH for a particular spid?


sys.dm_os_waiting_tasks


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


GK01
GK01
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
GilaMonster (1/29/2013)


CXPacket waits are not an indication of poor performance. They're an indication that queries are running in parallel. That is all. If you have lots and lots and lots of CXPacket, then, to reduce them and improve performance, you need to look for the other waits. In any query that's got huge CXPacket waits, there will be one or more threads that have some other wait type. That's the wait type that you need to investigate and resolve.

> 1


This saved my day.
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