Ever receive a late afternoon call just as you are unplugging your laptop to head home from an angry developer complaining about latency in his application trying to return data from your server? I know, I know, your first thought is “why did I answer that phone?” He claims that it is not his code and must be the database server. You hold your sarcastic comments, log into your server, and run sys.dm_os_wait_stats DMV only to find the wait stats for CXPacket to be through the roof. CXPacket what?
So what exactly are CXPackets? Class Exchange Packets are defined by Microsoft as occurring when you try “to synchronize the query processor exchange iterator. Microsoft goes on to tell us that we “may consider lowering the degree of parallelism if contention on this wait type becomes a problem.” Well, that is one option, but it should really be the last option. When a query runs in parallel, it means there are multiple threads running for one query. The query can only continue once all threads are complete. In other words, you are only as fast as your slowest thread.
However, parallelism is not always the issue. It is the easiest one to blame and often is the first recommended fix by most bloggers. There are several checks that can be performed before you dig too deep:
- Is the server hyper-threaded? If so, why and does it need to be?
- Is the Max Degree of parallelism set to a value greater than the amount of physical processors?
- When were statistics last updated?
- How fragmented are the indexes and do they need to be rebuilt/reorganized?
If all of these seem to be OK, then you will need to dig a little deeper into the issue by finding the query(s) that is experiencing the CXPacket waits. This information can be obtained by downloading and using Adam Machanic’s (blog|twitter) sp_WhoIsActive. This is a fantastic script that will give you all the information about the active tasks on your SQL instance. It will provide the wait stats, SQL text, session IDs, read and write stats, database name, and even host name. In other words, it gives you everything. . At this point, you will have identified the query and can evaluate whether it can be optimized or if you need to create an index. It is also possible to use Option(MAXDOP N) hint to manually set the parallelism to a specific value for that query. . Make sure the MAXDOP number you set in the hint does not exceed what is set in the Resource Governor in SQL 2008 or it will just default to it.
The last option is to set the Maximum Degree of Parallelism to 1. While it may sound redundant because we have read about it all over the Internet, but this is more typical in an OLTP environment than a reporting environment. By setting the Max Degree of parallelism to 1, you basically disable Parallelism. In the case of a hybrid environment (OLTP and OLAP), you may need to set it higher than 1. Remember, you should not exceed the physical number of processors. In a server setup with NUMA, you can set it to the number of processors on a single node. If you are increasing the parallelism, it is a good idea to look into setting the cost threshold for parallelism option so not all queries will be run in parallel. Instead, you will run only the queries that exceed this threshold. This is a set and test change and is different in everyone’s environment. Just be sure to be careful.
I have been using the SQL doctor tool since it was in Beta and have been constantly surprised and impressed with its functionality. Recently, I was contacted by Idera and informed that a new version (2.0) of Idera SQL doctor is now performing wait stat checks and making recommendations based on its findings. Included in the findings are recommendations for CXPacket waits. I hope to get a chance to use it soon to test drive its capabilities.
*Keep in mind that these are just recommendations and should be thoroughly tested before implemented in a production environment.
MORE RESOURCES ON THIS TOPIC
White Paper: Waiting on Wait Stats
Webcast: What Are You Waiting For?
Free Trial: SQL diagnostic manager
Free Trial: SQL doctor