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

very high CX packet waits Expand / Collapse
Author
Message
Posted Sunday, January 27, 2013 10:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936

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.

Post #1412181
Posted Monday, January 28, 2013 3:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1412316
Posted Monday, January 28, 2013 4:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1412326
Posted Monday, January 28, 2013 9:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936
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.
Post #1412502
Posted Monday, January 28, 2013 11:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1412766
Posted Tuesday, January 29, 2013 1:45 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 @ 3:52 AM
Points: 42,445, Visits: 35,501
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 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 #1412811
Posted Tuesday, January 29, 2013 12:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936
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?
Post #1413232
Posted Tuesday, January 29, 2013 12:51 PM


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 @ 3:52 AM
Points: 42,445, Visits: 35,501
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 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 #1413270
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse