Reducing SQL Server CXPACKET Wait Type

cxpacket wait type parallelismIn 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:


EXEC sp_configure 'max degree of parallelism',2
GO
RECONFIGURE WITH OVERRIDE
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:


EXEC sp_configure 'cost threshold for parallelism', 10
GO
RECONFIGURE WITH OVERRIDE
GO

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


SELECT Column1, Column
FROM SomeTable
WHERE (Column1 > @Value1 AND Column2 < @Value2)
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.

Subscribe to our mailing list

* indicates required Email Address *
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

Comments

  1. Wil Roufchaie says:

    Wonderful tips and summary of the wait event!

  2. Aaravind Ganapathi says:

    Hi Andy …a very good explanation on CXPACKET wait type.

  3. RaKeSh Tiwarekar says:

    Hi Andy,

    We changed ‘max degree of parallelism’ for our production servers. Still we observed high CXPACKET wait type. We have Index maintenance and statistics update maintenance plans and proactively monitor missing index and create if required.
    Can you help me with finding optimum value for ‘max degree of parallelism’?

    • Andy Hayes says:

      Thanks for your comment Rakesh. I personally do not think there is an optimal value as each server is different. You have to remember that CXPACKET waits can simply be normal for a server where multiple processors are used in queries. You could for example try and optimize to reduce CXPACKET wait time and actually reduce performance of those queries instead of improving them. Identifying the causes/reasons for CXPACKET is key.

  4. juliancastiblancop says:

    It was simple and clear. Good Job and thank you for share your knoweldge!!!

  5. Really good article for CXPACKET wait!!!
    But could you please also share what factors need to be consider while setting up the MAXDOP for any perticular SP?

  6. Short and crisp. Thanks for sharing your knowledge.

Speak Your Mind

*


*