CXPACKET

CXPACKET

http://www.confio.com/English/Tips/Sql_Server_Wait_Type_CXPACKET.php

The SQL Server CXPacket waittype is involved in parallel query execution. It indicates that the SPID is waiting on a parallel process to complete or start. The CXPacket waittype occurs when trying to synchronize the query processor exchange iterator. Excessive CXPacket waits are typically resolved by DBAs but may indicate a problem with the WHERE clause in the SQL Server query .

For OLTP applications where optimal SQL Server performance is required, CXPacket greater than 5% of total query execution time indicates a problem. Parallelism reduces SQL Server performance for OLTP applications. CXPacket indicates the operation of multiple parallel CPUs, each executing a portion of the query. Ordinarily a well tuned OLTP application would not parallelize unless an index is missing, there is an incomplete WHERE clause, or the query is not a true OLTP transaction.

Solutions

In SQL Server performance optimization, sometimes the cost of breaking apart a parallel query and putting the many results back together is more than the cost of running the query had parallelism not been used. In those cases, these wait types become numerous and long lasting. Queries which are heavily balanced to one sub query or another are a common cause of these. If, for example, your query retrieved records from four tables and one of them held the vast majority of records, and parallelism caused this to be spread across several threads, three of them would have to wait on the largest and you would see Exchange wait types. There are many suggestions for taking care of these types of SQL Server waits, with the most common to be to turn parallelism off, sometimes for just that query, sometimes for the whole server.

To check for parallelism: sp_Configure "max degree of parallelism".

If max degree of parallelism = 0, you might want to use one of the following options:

<!--[if !supportLists]--> <!--[endif]-->Turn off parallelism completely for OLTP workloads: set max degree of parallelism to 1

<!--[if !supportLists]--> <!--[endif]-->Limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 8 processors, set max degree of parallelism to <=4.