Select Page

CXPACKET Wait

CXPACKET wait in sql server occurs during parallel query execution, when a session is waiting on a parallel process to complete. This is usually a normal benign wait type. CXPACKET is not usually the problem, but rather the victim.

CXPACKET will occur whenever you have more than one processor or core.

You might find documentation that says you can get rid of CXPACKET by setting the MAXDOP setting to 1. This does indeed eliminate all CXPACKET waits, however setting MAXDOP to 1 turns off all parallel processing on your SQL Server, which will likely slow down the execution of many queries.

It is likely that if your CXPACKET waits are high then you may be caused bye one of the following:

  • Missing indexes
  • CPU pressure
  • Memory pressure
  • Out of data statistics causing SQL Server to incorrectly divide the query into equal sized sets
  • Fragmented indexes causing slower IO speeds that impact one thread over the others
  • Missing search predicate
  • Queries that are forcing a row by row processing of results rather than using sets.
  • Client applications not efficiently processing result sets
  • Incorrectly configured ‘max degree of parallelism’ server option
  • Incorrectly configure ‘cost threshold for parallelism’ server option

One way to help this is to adjust the Cost Threshold for Parallelism which will reduce the number of queries that are being parallelized and may help with the CXPACKET wait, but this may not be the best option.

The right thing to do is to tune the queries that are causing processor load which should help with the other queries that are causing CXPACKET waits.

2 Comments

  1. farooq mohammed

    I am enjoying the tool because its free and my organisation can’t afford third party tools. May i know what the colours green and maroon mean in server overview page.

    Thanks
    Farooq

    Reply
    • Steve Stedman

      The red implies the number on the chart go worse, usually larger, and green means no change or improved.

      -Steve

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

To prove you are not a robot: * Time limit is exhausted. Please reload CAPTCHA.