SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server - Waitstats CXPACKET wait type and how to reduce it

26 February,2011 by Jack Vamvas

According to SQL Server BOL – the CXPCACKET wait type

“Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.”

 The CXPACKET wait type occurs when more than one CPU core exists. Queries will run in parallel  and the waits will occur when one part of the query is waiting for another thread to complete it’s workload.

 CXPACKET wait types are not necessarily a sign that queries are performing inefficiently- and should be considered as normal when multiple cores exists.

The approach I take when analysing this wait type is to first identify which queries are running with parallelism and then determine why ? they are running with parallelism. Are they frequent queries?

 Reducing the CXPACKET is not just about looking at parallelism – but also consider other factors , such as workload type – (OLTP,DSS). The complexity arises when considering a mixed system , as is typical with many OLTP system – that also have reporting , such as Crystal Reports running over them .  The recommended setting for OLTP is MAXDOP = 1 , but when there is reporting , what is the correct level?

 The nature of OLTP systems would suggest the less parallelism the better – as we are looking for a high volume , but short transactions . For DSS systems – this is not such a concern , but may still indicate that there are other problems , such as : queries need tuning, out of date statistics, index reorganize

 For example recently on a mixed OLTP\Reporting Server I switched to MAXDOP = 2 , which solved some problems – but created others. It was only after some thorough testing I switched back to MAXDOP=1 , and adjusted some of the queries. Hardware is also an issue

 Configuring parallelism can be achieved on 2 levels:

1)At an server instance level –  this will apply to all connections

2)Use a Table hint Option (Maxdop N) – N representing the processor number. This applies to the one session on the one query

Related Posts

SQL Server - CPU Count

Affinity Mask on SQL Server 2008 R2

SQL Server – SOS_SCHEDULER_YIELD and how to reduce it

SQL WAIT TYPE – OLEDB and how to reduce it

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer