At the 2010 SQL PASS Summit we had the great pleasure of speaking with the well known SQL Server MVP Pinal Dave (blog).  One of the topics that were discussed was the use of wait stats by SQL doctor for generating recommendations and he agreed to spend extra time with us to discuss his experience with wait stats and how they can be used by SQL doctor for performance tuning.

Over the past two months we had several conversations with Pinal about wait stats with CXPACKET waits being one of the first we discussed.  It appears that it is very common for CXPACKET waits to be one of the highest waits experienced on a SQL Server.  This is evident by the results of a wait stats survey put on by Paul Randal (blog|twitter).  The results of the survey were shared in an excellent blog post on wait stats.  This shows that CXPACKET waits are overwhelmingly the highest experienced on this random sampling of over 1800 different SQL Server instances.

What are CXPACKET waits?

Since we can expect to run into high CXPACKET waits one time or another when dealing with SQL Server, it will be worth our time and effort to understand this wait type.  CXPACKET waits occur when a parallel query is executed and one or more threads have to wait on one of the other threads to complete.  The time spent in the waiting threads of a parallel query is the time that is measured by CXPACKET.  To fully understand this, we need to have a greater depth of understanding on parallel queries.

What are parallel queries?

Books online provides details on ‘Parallel Query Processing’ along with other information on parallelism in SQL Server that can be study for a greater understanding.  There is also a great blog post by Craig Freedman on the subject of parallel query process.  For the purpose of this blog post I want to provide a very basic understanding to help in reaching our goal of understanding CXPACKET waits.

From the most basic perspective, a parallel query is a query that can be divided into smaller pieces and worked on in parallel by multiple threads.  This requires extra overhead to coordinate the parallel processing along with the initialization, synchronization, and termination of the parallel plan.

Why must parallel queries wait?

SQL Server will parallelize a query by dividing each operation into equal sized sets for processing.  These sets are then split up into multiple threads targeting specific processors.  This allows each thread to be executed in parallel by each of the processors.  Even with SQL Server splitting up the work into equal sized pieces, it is still impossible for all of the threads to complete at exactly the same time to prevent one or more of the threads from experiencing CXPACKET waits.  In short, if you have parallel query execution you will have CXPACKET waits. 

Should I be concerned about CXPACKET waits?

This is not something to be concerned about until the CXPACKET waits become excessive.  Once CXPACKET waits are excessive, you know you have some performance tuning that needs to be performed on a specific query or the whole SQL Server.

CXPACKET waits are only a symptom and not an actual problem.  If you are experiencing high CXPACKET waits, you need to find out why the SQL Server engine divided a query into equal size sets that don’t get processed within a relatively equal time period.  This can be due to any of the following:

• Out of data statistics causing SQL Server to incorrectly divide the query into equal sized sets
• Fragmented indexes causing slower IO speeds that impact one thread over the others
• Client applications not efficiently processing result sets
• Hyper-Threading that causes SQL Server to process threads on hyper-threaded cores instead of only physical cores
• CPU pressure
• Memory pressure
• Incorrectly configured ‘max degree of parallelism’ server option
• Incorrectly configure ‘cost threshold for parallelism’ server option

I am certain that I missed something in this list but this is all that has come to mind while writing this post.  If you have others please leave a comment and I will be more than happy to update the list.

What should I do about high CXPACKET waits?

As with everything dealing with SQL Server performance, the answer is always the same, it depends.  Now that we have the standard ‘it depends’ answer out of the way, let me give you and easy for me answer:

• Make sure that ‘auto create statistics’ and ‘auto update statistics’ is enabled all all databases
• Rebuild all indexes
• Perform an ‘UPDATE STATISTICS’ on all stats objects with the FULLSCAN
• Hyper-Threading should be disable
• ‘max degree of parallelism’ should not be set to a number greater than the number of physical processor cores
• ‘cost threshold for parallelism’ should not be set too low

If you followed the above you will probably find that most of the time your CXPACKET waits will be back under control.  Chances are this will only be temporary and you will have to go through the process over and over again for it to continue to stay under control without ever finding the root cause to the problem.  This may be perfectly acceptable and if you put in place a maintenance job that performs all of your index and statistics maintenance on a routine interval you may find that the CXPACKET waits and many more of your performance problems all but vanish.

Since it is not always realistic to rebuild all of your indexes and perform FULLSCAN stats updates, we may want to consider the much harder answer.  This one revolves around determining the type of processing performed on the SQL Server along with finding the root cause to the CXPACKET waits.

At this point you basically need to know if the server you are dealing with is a true OLTP server or not.  If it is a true dedicated OLTP server, you should never see high CXPACKET waits and many would argue that you should see no CXPACKET waits since no queries should ever be parallelized because the queries are very short running which would not benefit from parallelism.

If we find our self dealing with a true OLTP server that has high CXPACKET waits it is generally due to one or more of the following:

• Missing indexes
• out of data statistics
• missing search predicate
• function used on a column in a search predicate

An easy way to identify these queries is to review the query plans from the plan cache that can be parallelized.  The following links can be used to find scripts that pull this information:

http://www.sqlservercentral.com/blogs/stratesql/archive/2010/12/9/can-you-dig-it_3F00_-_1320_-parallelism-in-the-plan-cache.aspx

http://www.jasonstrate.com/2009/04/find-query-plans-that-may-utilize-parallelism/

I also want to discuss a process for identifying the root cause for CXPACKET waits on a non-OLTP server but this discussion will have to be continued in a part 2.

To be continued…