very high CX packet waits

Question

very high CX packet waits

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
January 27, 2013 at 10:28 pm

#288066

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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 SSC Guru Points: 59351 More actions · Answer 2

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 SSC-Dedicated Points: 36494 More actions · Answer 3

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 SSC Guru Points: 59351 More actions · Answer 4

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;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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 SSC-Dedicated Points: 36494 More actions · Answer 6

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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 SSC Veteran Points: 298 More actions · Answer 8

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.