January 22, 2018 at 7:42 am
Experts, I have been going through handful of servers I am responsible for and I was just looking into DB settings. Some of the settings I saw which didn't look right to me and I am hoping to get some help. I am not sure if I should change it or leave it the way they are.
Default index fill factor = 0
CT of parallelism = 5
MAXDOP = 0
January 22, 2018 at 7:54 am
Default fill factor doesn't matter too much provided that you consider the fill factors individually for each index you create. 0 is fine for indexes with a monotonically increasing key, but not so good for ones with a more random key.
5 is too low a value for cost threshold for parallelism on most modern system. Start with something between 30 and 50, and monitor.
Best not to let individual queries chew up all the processors on a server. If you search you'll find all sorts of formulas for calculating MAXDOP. Often they work out at half the processors in the system.
John
January 22, 2018 at 8:44 am
I'd agree with John.
Fill Factor 0 is the same as 100. If you see lots of page splits, or an increasing amount, then I might lower this, but I'd keep it in the 90 range unless I really had a good set of measurements that show it needs to be lower. any change in this will mean more page reads for queries.
MAXDOP definitely low. I think this has some good guidance, but if you look at your hardware, you'll have an idea of where to set this: https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi
Cost threshhold, seems lots of people start about 50 and then adjust this. Grant has a post on how to decide what to do here: https://www.scarydba.com/2017/02/28/determining-the-cost-threshold-for-parallelism/
The key is to measure your system now, before you make changes. Gather metrics on page splits, query performance, and then make an adjustment. Measure again, repeat.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply