Reducing SQL Server CXPACKET Wait Type

文章对CXPACKET的解释比较简单易懂，但是对问题的解决解释的不到位。我认为最根本的方法就是修改SQL，使得SQL不会被拆分成过多的threads。

In my last post, I wrote about how SQL Server schedules tasks to be executed. It’s important to be  able to understand this when when trying to analyse wait types and statistics in SQL Server.

For this post, I will be looking at the CXPACKET wait type and what you can do to reduce it.

I mentioned before that the wait types need translation – MSDN describes this as:

Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

This is a parallel processing wait type where many threads are used to satisfy the query. Each thread deals with a subset of the data. The idea is that if there are more threads working on different parts of the data set, it can be more efficient.

Parallel execution can therefore be a highly effective way of dealing with large sets of data, for example in a data warehouse/reporting server. However, in OLTP systems, it could well have a negative impact on performance.

CXPACKET wait type occurs when there are threads taking longer to execute than the other threads in that query. The completed threads are left in waiting state until the last thread can complete.

I’ll go into more detail about these settings in the next section but by default your SQL Server is configured for parallelism as the “max degree for parallelism setting” is set at zero.

For a query then to qualify for parallel execution, its estimated (or cached) cost needs to exceed the “cost threshold for parallelism” setting.

So in an example scenario on a server with 8 cores, if a parallel query executes with “max degree of parallelism” set at zero, then up to 8 cores can be used in the query. As each core completes its work, it waits until all cores have completed.

The result is CXPACKET waiting and the potential to delay other SPIDs wanting a piece of CPU time to process their request.

Assessing causes of CXPACKET wait type is vitally important

You have to remember that CXPACKET waits are normal for multiprocessor servers because of parallel execution. So simply adjusting your server configuration in an effort to reduce this wait type could have an overall negative impact on performance.

Understanding whether the system is an OLTP, DSS or combination of both is critical.

It’s no good simply assuming that because you have CXPACKET waits, that it is automatically a problem. If CXPACKET is one of the top reported wait types on your SQL Server, the next thing to consider is, what are the causes and is this wait type normal for my workload?

There may be other contributing factors. Is CXPACKET a symptom of something else? For example, it could be as simple as a missing index or out of date statistics. Your SQL Server then tries to compensate by generating a query plan that uses parallelism in an effort to optimize the query, or rather, to make the scan operation go faster.

How to reduce CXPACKET wait type by changing settings

There are some settings which can affect how your queries use parallelism.

1. Server level – “Max Degree of Parallelism”
2. Server level – “Cost Threshold for Parallelism”
3. Query hint – MAXDOP

Max Degree of Parallelism - specifies the number of CPU cores to be used in parallel query execution at the server level. The default value is 0 which means that SQL Server will use all available cores. Setting this to 1 turns off parallelism. Here is an example which instructs the SQL Server to use no more than 2 cores for parallel execution:

1	EXEC sp_configure 'max degree of parallelism',

2

GO

3	RECONFIGURE WITH OVERRIDE

4

GO

Cost Threshold for Parallelism -  the estimated elapsed time in seconds before the server will consider parallelism in the query execution. Here is an example setting this value to the estimated elapsed time of 10 seconds:

1	EXEC sp_configure 'cost threshold for parallelism', 10

2

GO

3	RECONFIGURE WITH OVERRIDE

4

GO

MAXDOP - a query hint where the number of cores is specified for the query. Here is an example using MAXDOP for 2 cores:

1	SELECT Column1, Column

2	FROM SomeTable

3	WHERE (Column1 > @Value1 AND Column2 < @Value2)

4	OPTION (MAXDOP 2)

Note that you can also change max degree of parallelism and cost threshold for parallelism settings using Management Studio. These settings can be found under the Properties->Server Properties->Advanced section.

Summary and guidance points for CXPACKET wait type and parallelism

• OLTP – parallelism can have a negative impact on performance.
• OLAP/reporting benefits from parallelism.
• Look at your plan cache, which of your queries use parallelism?
• Check query plans – do you have missing indexes or out of date statistics?
• What other waits are occurring that might be causing CXPACKET?
• Consider using MAXDOP to fine tune specific queries.
• Set max degree of parallelism and cost threshold for parallelism to best suit your server’s function.
• Do not adjust max degree of parallelism without proper analysis of your servers workload/queries.