CXPACKET Wait in SQL Server

CXPacket wait in sql server occurs during parallel query execution, when a session is waiting on a parallel process to complete.

MSDN says that CXPACKET "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". Whilst this is true, it's also often the case that CXPACKET waits are the result of inefficient queries or stored procedures.

Here is an example of a SQL Server instance with high CXPACKET wait:

In this real-life example, several stored procedures had high CXPACKET waits because they included inneficient SQL statements which were not correctly indexed. A typical well-tuned database instance would not parallelize a query unless there was a missing index or there is an incomplete WHERE clause etc.

Potential Solutions to CXPACKET Wait

1. Is the problem related to inefficient SQL which can be tuned?

Use a tool such as DBTuna to quickly find out which stored procedures or batches are taking the time, and which have high CXPACKET wait. Once these have been identified, drill-down to establish which individual SQL's the wait is on. Once isolated, use a tool such as the SQL Server Index Tuning Wizard to check for missing indexes, or out of date statistics. Fix if possible. This was the process used to solve the above real-life example. The top stored procedure included multiple select statements, but just one was the bottleneck which included an unindexed sub-query.

2. If the problem cannot be tuned with Indexing

If the statement cannot be tuning using normal mechanisms e.g. Indexing, re-writing etc. then it may be that the solution is to turn off parallelism, either for an individual query or for the whole server.

To find out the current configuration of parallelism you can run the following command: sp_Configure "max degree of parallelism".

If max degree of parallelism = 0, you might want to turn off parallelism completely for the instance by setting max degree of parallelism to 1. You could also limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 4 processors, set max degree of parallelism to 2.

See the MSDN page for more information on CXPACKET and the Max Degree of Parallelism.

If you would like to find out more about the performance of your SQL Server instances then why not download DBTuna today! Or arrange a web meeting at a convenient time via our contact page.

Copyright ©2010 DBTuna. All rights reserved.