none
SQL SERVER – Reducing CXPACKET Waits RRS feed

  • Question

  • Hi,

    i check monitor in sql server why the system is slow it shows cxpacket  waits very high,when i check online it talks about parallelism .we increased parallelism but it didn't help.Any other ideas other than parallelism.

    Thanks


    Tuesday, May 24, 2016 3:16 PM

Answers

  • All that CXPACKET tells you is that you have parallelism. This may be good, this may be bad. It's bad when the partitioning of the thread is unbalanced and all work is performed on one thread.

    Rather, it is better to identify the slow queries and see what can be improved about this. For instance, could it help to add indexes?

    Tuesday, May 24, 2016 9:31 PM

All replies

  • All that CXPACKET tells you is that you have parallelism. This may be good, this may be bad. It's bad when the partitioning of the thread is unbalanced and all work is performed on one thread.

    Rather, it is better to identify the slow queries and see what can be improved about this. For instance, could it help to add indexes?

    Tuesday, May 24, 2016 9:31 PM
  • Hi,

    i check monitor in sql server why the system is slow it shows cxpacket  waits very high,when i check online it talks about parallelism .we increased parallelism but it didn't help.Any other ideas other than parallelism.

    Your approach towards troubleshooting is incorrect just by looking at activity monitor and seeing CXPACKET waits you came to conclusion that parallelism is problem and instead of decreasing it you increased. Although I would not suggest to change max degree of parallelism blindly.

    What is actual issue here ? What do you exactly mean by server is slow

     

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, May 25, 2016 7:38 AM
  • Read also this link to identify queries wait for CXPACKET waits

    http://www.madeiradata.com/t-sql-tuesday-49-using-cxpacket-waits-to-detect-query-bottlenecks/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, May 25, 2016 7:47 AM
    Answerer
  • CXPACKET waits are a symptom, not the problem.  You really need to look at the execution plan of the query and see where the bottlenecks are.  Look for things like table scans, index scans, non-optimal index usage, steps that return very large number of rows (relative to the function the step needs to perform), joins happening before filtering, etc.

    If you can post your execution plan, perhaps we could provide some more suggestions.

    HTH,

    Carl

    Wednesday, May 25, 2016 9:42 PM