CXPACKET is one of the most common wait stats in SQL Server, frequently appearing as one of the top 5 wait stats in systems with multiple CPUs. This wait stat occurs when there is contention on the query processor exchange iterator. If this wait type becomes a problem, it is recommended to lower the degree of parallelism.

When a parallel operation is created for a SQL query, multiple threads are used to process the query. Each thread handles a different set of data or rows. However, sometimes one or more threads lag behind, resulting in the CXPACKET wait stat. The organizer or coordinator thread (thread 0) waits for all the threads to complete and gathers the results to present on the client’s side. The wait by the organizer thread for slow threads to finish is known as the CXPACKET wait.

It’s important to note that not all CXPACKET wait types are bad. In some cases, it may be necessary or beneficial. Disabling this wait type for a query can actually make it run slower because parallel operations are disabled.

To reduce CXPACKET wait, the approach depends on the server workload type:

OLTP (Online Transaction Processing) System:

In a pure OLTP system where transactions are small and queries are quick, it is recommended to set the “Maximum Degree of Parallelism” to 1. This ensures that the query never goes for parallelism and avoids additional engine overhead.

EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Data Warehousing / Reporting Server:

For servers running long-running queries, such as data warehousing or reporting servers, it is advised to set the “Maximum Degree of Parallelism” to 0. This allows most queries to utilize parallel processing, improving performance due to multiple processors.

EXEC sys.sp_configure N'max degree of parallelism', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

Mixed System (OLTP & OLAP):

In a mixed system with both OLTP and OLAP queries, finding the right balance is crucial. One approach is to set the “Maximum Degree of Parallelism” to 2, allowing queries to use parallelism on 2 CPUs. However, the “Cost Threshold for Parallelism” should be set high, so only queries with a higher cost qualify for parallelism. This approach works well for systems with a combination of OLTP and reporting servers.

EXEC sys.sp_configure N'cost threshold for parallelism', N'25'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

It’s important to note that these recommendations are based on experience and may vary from system to system. It is recommended to test these configurations on a development server before implementing them on a production server.

For further clarification and more in-depth information, it is recommended to refer to the online documentation.

Thank you for reading!