CXPacket What?

By Mitch Bottel



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.



... To view more http://sqlserverperformance.idera.com/network-performance/cxpacket/