April 10, 2016 at 3:21 am
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
April 10, 2016 at 5:06 am
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
April 10, 2016 at 6:41 am
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
April 10, 2016 at 7:12 am
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.
Thanks for the link , i checked it and it has lots of good info. in it.
April 10, 2016 at 7:18 am
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
April 10, 2016 at 7:51 am
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
April 11, 2016 at 12:58 am
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
April 11, 2016 at 2:37 am
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
April 11, 2016 at 2:42 am
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