CXPACKET bottleneck.

Doria 1,246 Reputation points
2020-09-02T01:33:34.4+00:00

Hi everyone!

Are there any recommendations or good configuration practices for the treatment of the CXPACKET bottleneck?

22091-untitled.png

All help is welcome!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Uri Dimant 206 Reputation points
    2020-09-02T02:07:09.623+00:00

    It does not look terrible, do the users complain about performance?
    https://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/

    1 person found this answer helpful.
    0 comments
  2. SQLZealots 276 Reputation points
    2020-09-02T02:17:52.847+00:00

    A good read as below:
    knee-jerk-wait-statistics-cxpacket

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
    Blog
    LinkedIn

    1 person found this answer helpful.
    0 comments
  3. Cris Zhan-MSFT 6,601 Reputation points
    2020-09-02T03:19:08.127+00:00

    Hi,

    CXCONSUMER waits are generally benign, encountered as a normal part of parallel(CXPACKET) execution. By making the consumer threads register benign CXCONSUMER waits, the remaining CXPACKET waits from the producer threads are what indicate a performance problem.

    Also check this similar case:
    https://dba.stackexchange.com/questions/210795/what-can-i-do-about-high-cxpacket-waits

  4. Shashank Singh 6,246 Reputation points
    2020-09-02T07:36:32.99+00:00

    My starting question is are you facing performance issue with these waits, the whole answer depends on that. As others have mentioned these are mostly benign waits and in case of issue they are not actually the issue but the next most wait coming is what causing it. In your case if you tell me this is a problem then I would ask you to look at SOS_SCHEDULER_YIELD waits. May be the max degree of parallelism is not set correctly, may be their is CPU pressure. We need to dig deeper and check. The CXCONSUMER in this case can be ignored.

    0 comments
  5. Doria 1,246 Reputation points
    2020-09-02T19:48:30.877+00:00

    Thanks all for the answers!

    A lot to read and learn, thanks. Well, I can't say that we are having performance problems, but I like to optimize our production database, as always. Follow our settings:

    22304-1.png

    22245-2.png

    I am thinking of configuring MAXDOP to 8, according to Microsoft’s recommendations in KB 2806535. But how can I better measure if there is a CPU pressure?

    22209-3.png

    22267-4.png

    It doesn't end here. Let's talk more.