none
Fix CXPACKET Waittype RRS feed

Answers

  • How to fix CXPACKET waittype

    The way to "fix" it is
    EXEC sp_configure 'max degree of parallelism', 1
    RECONFIGURE

    and you will never see that wait type again.

    However, don't do this if you don't know what you are doing (and your questions makes clear that you don't). Most likely you will have users start complaining about queries running slower.

    When you have parallelism, you will always have CXPACKET waits, because there is always a main thread waiting for the workers to complete. On the other hand, you may have queries that run with parallel plans when a serial plan would have been sufficiently effcient, but that serial plan is not possible because the required index is not there. And lots of variations on the same theme.

    So a better answer is: tune your queries.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by juniorkiran Friday, October 11, 2019 8:04 AM
    Thursday, October 10, 2019 9:40 PM
  • Hi juniorliran,

     

    >>How to fix CXPACKET waittype

     

    The CXPACKET wait type is normal for SQL Server and it is an indicator that SQL Server is using a parallel plan in executing a query.

    As long as the CXPACKET is less than 50% of total waits, it shouldn’t be considered as a problem but rather as an indicator.

     

     Configuring MAXDOP settings to 1 should be the last resource used in troubleshooting excessive CXPACKET wait times.

     

    SQL Server’s query optimizer is using the Cost Threshold for Parallelism (CTFP) to determine when the query should be parallelized, when the serialized query plan cost exceeds the cost threshold for parallelism it will create a parallel query plan. The CTFP is set by default to 5, which mean that even not so expensive query plan could initiate the parallel plan to be created.

     

    To prevent unwanted parallelism, the CTFP number could be increased a minimum value of 25. Although, finding the proper CTFP number and fine tuning it for maximum performance is something that has to be done by analyzing the query plans and available resources is the way to determine what CTFP configuration would work best for specific system.

     

    As workarounds to 'fix' CXPACKET wait type:

     

    Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice, as it could be just the exception in the system that is normally working correctly

     

    Check the indexes and statistics on tables used by the query and make sure they are up to date

     

    Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system

     

    As mentioned by Erland,  the better answer is : tune your queries.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by juniorkiran Friday, October 11, 2019 8:05 AM
    Friday, October 11, 2019 7:57 AM

All replies

  • Why do you think CXPACKET waits need to be fixed? These are natural in parallel execution plans and needed for thread coordination.

    That begin said, many parallel queries in an OLTP workload may be an indicator that query and index tuning is needed. Such tuning may result in more efficient single threaded plans and mitigate CXPACKET waits.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, October 10, 2019 12:18 PM
    Moderator
  • Thursday, October 10, 2019 6:51 PM
    Moderator
  • How to fix CXPACKET waittype

    The way to "fix" it is
    EXEC sp_configure 'max degree of parallelism', 1
    RECONFIGURE

    and you will never see that wait type again.

    However, don't do this if you don't know what you are doing (and your questions makes clear that you don't). Most likely you will have users start complaining about queries running slower.

    When you have parallelism, you will always have CXPACKET waits, because there is always a main thread waiting for the workers to complete. On the other hand, you may have queries that run with parallel plans when a serial plan would have been sufficiently effcient, but that serial plan is not possible because the required index is not there. And lots of variations on the same theme.

    So a better answer is: tune your queries.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by juniorkiran Friday, October 11, 2019 8:04 AM
    Thursday, October 10, 2019 9:40 PM
  • Hi juniorliran,

     

    >>How to fix CXPACKET waittype

     

    The CXPACKET wait type is normal for SQL Server and it is an indicator that SQL Server is using a parallel plan in executing a query.

    As long as the CXPACKET is less than 50% of total waits, it shouldn’t be considered as a problem but rather as an indicator.

     

     Configuring MAXDOP settings to 1 should be the last resource used in troubleshooting excessive CXPACKET wait times.

     

    SQL Server’s query optimizer is using the Cost Threshold for Parallelism (CTFP) to determine when the query should be parallelized, when the serialized query plan cost exceeds the cost threshold for parallelism it will create a parallel query plan. The CTFP is set by default to 5, which mean that even not so expensive query plan could initiate the parallel plan to be created.

     

    To prevent unwanted parallelism, the CTFP number could be increased a minimum value of 25. Although, finding the proper CTFP number and fine tuning it for maximum performance is something that has to be done by analyzing the query plans and available resources is the way to determine what CTFP configuration would work best for specific system.

     

    As workarounds to 'fix' CXPACKET wait type:

     

    Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice, as it could be just the exception in the system that is normally working correctly

     

    Check the indexes and statistics on tables used by the query and make sure they are up to date

     

    Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system

     

    As mentioned by Erland,  the better answer is : tune your queries.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by juniorkiran Friday, October 11, 2019 8:05 AM
    Friday, October 11, 2019 7:57 AM
  • Thanks a lot Erland

    Kiran

    Friday, October 11, 2019 8:03 AM
  • Thanks a lot Dedmon

    Kiran

    Friday, October 11, 2019 12:35 PM