CXPACKET
Published 28 March 2024
This is a common wait that occurs when a parallel query is executing. During parallel processing, SQL Server breaks down queries into separate parts that can be processed simultaneously using multiple threads. Each thread works on a separate piece of data so that queries can be processed more quickly and efficiently. If work is not split equally between threads, those with fewer rows to process must wait until long-running threads complete. CXPACKET is the total time in milliseconds of all threads waiting to process data.
CXPACKET waits can occur for different reasons in various environments, and you’ll need to find the root cause before deciding whether or not a problem exists. For example, in large-scale data warehousing or OLAP (Online Analytical Processing) environments with multiple processors and frequent parallel executions, a high CXPACKET wait is expected behavior and is not necessarily an issue. In an OLTP (Online Transaction Processing) with small transactions and short queries, excessive CXPACKET waits may affect throughput of other OLTP traffic.
Investigate queries
Examine the long-running parallel query and tune if necessary. See: Query tuning (TechNet).
Run the query in your SQL Server management tool to look for problems in the execution plan. See: Execution plan basics.
To ensure effective parallelisation, check that the statistics on tables and indexes used by the query are up to date. See: Statistics in SQL Server.
If PAGEIOLATCH_* waits are also high, check the query plan for large parallel table scans. See: Get all SQL Statements with "table scan" in cached query plan (TechNet).
Investigate system settings
Note: You should only make system changes if you’ve already investigated queries, and you understand the potential impact on your environment.
Check the sp_configure ‘cost threshold of parallelism’ setting and update if necessary. See: Configure the cost threshold for parallelism option (TechNet) and Tuning ‘cost threshold for parallelism’ from the plan cache.
Check the sp_configure ‘max degree of parallelism’ (MAXDOP) setting and update if necessary. See: Configure the max degree of parallelism option (TechNet) and Microsoft's recommendations.