CXPACKET bottleneck.

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

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.
8,483 questions
No comments
{count} votes

8 answers

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

    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/

    No comments

  2. answered 2020-09-02T02:17:52.847+00:00
    SQLZealots 276 Reputation points

    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

    No comments

  3. answered 2020-09-02T03:19:08.127+00:00
    Cris Zhan-MSFT 6,561 Reputation points

    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. answered 2020-09-02T07:36:32.99+00:00
    Shashank Singh 6,211 Reputation points

    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.

    No comments

  5. answered 2020-09-02T19:48:30.877+00:00
    Doria 1,221 Reputation points

    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.