CXPACKET wait

  • Hi All,

    I have noticed that the most wait taking place on live environment is CXPACKET.

    I have read some articles on it and downloaded and run the Microsoft baseline configuration analyzer and Microsoft best practices analyzer.

    I was hoping it recommends a DOP value but according to report it was OK.

    First of all is there any possible way to know which SQLs are waiting for CXPACKET and is there a recommended way to test that.

    Also if i change DOP to improve performance for specific SQLs, how can i be sure it will not cause problem for other SQLs.

    Thanks and looking forward for your replies.

    Nader

  • CXPACKET has to do with Parallellism, it's an indicator of inefficient parallellism. Parallellism is not bad, but is should only be used for queries which benefit.

    There are 2 settings which will influence parallellism: Cost Treshold for parallellism (CTfP) and Max Degree of Parallellism (MaxDOP).

    For every query, the optimizer calculates a cost value (see the execution plan). If this cost value is above CtfP, the query will be executed in parallel. However, the default value of 5 was determined in 1991. If a query in 1991 ran for more then 5 seconds it should go parallel. This value is way too low for the current hardware. I recommend to change this value to 50, clear your waitstat data and analyze the waitstats again. If CXPACKET is still above 25% of your waitstats you might increase this value.

    For MaxDOP: this value is the number of processors being used in a parallel operation. It should be no more than the number of processors in a NUMA group, with a maximum of 8.

    See this excellent video of Brent Ozar[/url]

    Wilfred
    The best things in life are the simple things

  • The only thing that CXPacket alone tells you is that queries are running in parallel. Nothing more.

    Increasing Cost threshold (it's not measured in seconds) is a good idea. Other than that, identify your worse performing queries and tune them. Every time I've run into parallelism problems (CXPacket along with other waits and performance problems) it's been due to inefficient queries and poor indexing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Wilfred van Dijk (4/10/2016)


    CXPACKET has to do with Parallellism, it's an indicator of inefficient parallellism. Parallellism is not bad, but is should only be used for queries which benefit.

    There are 2 settings which will influence parallellism: Cost Treshold for parallellism (CTfP) and Max Degree of Parallellism (MaxDOP).

    For every query, the optimizer calculates a cost value (see the execution plan). If this cost value is above CtfP, the query will be executed in parallel. However, the default value of 5 was determined in 1991. If a query in 1991 ran for more then 5 seconds it should go parallel. This value is way too low for the current hardware. I recommend to change this value to 50, clear your waitstat data and analyze the waitstats again. If CXPACKET is still above 25% of your waitstats you might increase this value.

    For MaxDOP: this value is the number of processors being used in a parallel operation. It should be no more than the number of processors in a NUMA group, with a maximum of 8.

    See this excellent video of Brent Ozar[/url]

    Thanks for the link , i checked it and it has lots of good info. in it.

  • GilaMonster (4/10/2016)


    The only thing that CXPacket alone tells you is that queries are running in parallel. Nothing more.

    Increasing Cost threshold (it's not measured in seconds) is a good idea. Other than that, identify your worse performing queries and tune them. Every time I've run into parallelism problems (CXPacket along with other waits and performance problems) it's been due to inefficient queries and poor indexing.

    Thank you for your reply, i have watched the video in previous post.

    My conclusion that i will try to apply during weekend is.

    1. Change cost threshold of parallelism from default = 5 to 50.

    2. Change DOP to 4 instead of 0

    The database server in that environment is 4 quad cores =4*4.

    With regards to CPU consuming SQLs and indexes , i have been working on that during last period and things are much better now.

    Thanks Again and please i would appreciate if you confirm the previous 2 settings are safe to change.

    Regards

    Nader

  • You can change both MaxDOP and Cost Treshold without having to restart the instance.

    Make sure you clear the current waitstats before evaluating the new settings( give the server some time to collect a reliable overview of the waitstats after you've cleared the old waitstats). See this link from Paul Randal[/url] to monitor your waitstats (and the command to clear you current waitstats)

    Wilfred
    The best things in life are the simple things

  • Wilfred van Dijk (4/10/2016)


    You can change both MaxDOP and Cost Treshold without having to restart the instance.

    Make sure you clear the current waitstats before evaluating the new settings( give the server some time to collect a reliable overview of the waitstats after you've cleared the old waitstats). See this link from Paul Randal[/url] to monitor your waitstats (and the command to clear you current waitstats)

    Thank you very much Wilfred for the useful links.

    One more question please, is it safe to change those 2 options during working hours or better wait during weekends when load is much less.

    Regards

    Nader

  • They clear the plan cache when changed, so don't do them during working hours.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you Gail, appreciate all the replies.

    Regards

    Nader

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply