December 1, 2014 at 1:23 pm
I have less than 4 cores so my current setting is 0. However, I see some recommendations to set it to "0 to N". Will this make any difference?
December 1, 2014 at 1:41 pm
I always point people with MaxDOP setting questions to this KB Article to start with. It sounds like in your situation with only 4 cores 0 is probably okay, unless you are using hyper-threading and then, according to the KB Article, you should probably use 4.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 1, 2014 at 2:32 pm
Jack Corbett (12/1/2014)
I always point people with MaxDOP setting questions to this KB Article to start with. It sounds like in your situation with only 4 cores 0 is probably okay, unless you are using hyper-threading and then, according to the KB Article, you should probably use 4.
That article is very good and not always applicable for various systems. I was experiencing on a hyper-threaded system with 24 cores, setting maxdop to 4, 8 and 0. I must say it very depends on the system. For e.g. the system has lots of short transactions and sometimes long and data-massive transactions. Setting it to 4/8 was improving for the short transactions but showed worse for the long and data-massive ones resulting with more locks and deadlocks, so I had to return it to 0 which showed better. Seems that 0 is the best option in general.
Igor Micev,
My blog: www.igormicev.com
December 1, 2014 at 3:01 pm
Igor Micev (12/1/2014)
Jack Corbett (12/1/2014)
I always point people with MaxDOP setting questions to this KB Article to start with. It sounds like in your situation with only 4 cores 0 is probably okay, unless you are using hyper-threading and then, according to the KB Article, you should probably use 4.That article is very good and not always applicable for various systems. I was experiencing on a hyper-threaded system with 24 cores, setting maxdop to 4, 8 and 0. I must say it very depends on the system. For e.g. the system has lots of short transactions and sometimes long and data-massive transactions. Setting it to 4/8 was improving for the short transactions but showed worse for the long and data-massive ones resulting with more locks and deadlocks, so I had to return it to 0 which showed better. Seems that 0 is the best option in general.
As I said that article is a starting point. I would disagree that 0 is the best option in general. As that article states, SQL Server, when parallelizing an operation, utilizes all the processors that are available based on the MaxDOP setting. 0 means use all, and, on very large systems, this can often cause issues when distributing and regathering the streams because there are too many processors being used. You also have have to remember that MaxDOP <> Affinity. Setting MaxDOP to something other than 0 doesn't mean SQL Server won't utilize all available processors, it just means it will only parallelize queries out to that number. Other tasks can be assigned to the other processors available to SQL Server while the N are being used for the parallel query. I'm not doubting that on your system a higher number was better, but that leaving it set to 0 is usually not the best setting.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2014 at 6:11 am
JoshDBGuy (12/1/2014)
I have less than 4 cores so my current setting is 0. However, I see some recommendations to set it to "0 to N". Will this make any difference?
Check your Cost threshold for parallelism if its default of 5 it's probably way too low.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 3, 2014 at 11:09 am
Perry Whittle (12/3/2014)
JoshDBGuy (12/1/2014)
I have less than 4 cores so my current setting is 0. However, I see some recommendations to set it to "0 to N". Will this make any difference?Check your Cost threshold for parallelism if its default of 5 it's probably way too low.
Yes, this is my next change. I'm working with a new company that has a cost threshold set to 5. I was going to do an after hours change and change it to a higher number, something like 20 and gauge performance. I'm running into high CXPacket waits, around 60%. I'm also modifying indexes and have seen some pretty decent reductions in CPU usage.
December 3, 2014 at 11:32 am
JoshDBGuy (12/3/2014)
Perry Whittle (12/3/2014)
JoshDBGuy (12/1/2014)
I have less than 4 cores so my current setting is 0. However, I see some recommendations to set it to "0 to N". Will this make any difference?Check your Cost threshold for parallelism if its default of 5 it's probably way too low.
Yes, this is my next change. I'm working with a new company that has a cost threshold set to 5. I was going to do an after hours change and change it to a higher number, something like 20 and gauge performance. I'm running into high CXPacket waits, around 60%. I'm also modifying indexes and have seen some pretty decent reductions in CPU usage.
I start from 50 🙂
You can also look for parallel queries from DMVs and look for starting value from there.
December 3, 2014 at 11:36 am
Ville-Pekka Vahteala (12/3/2014)
JoshDBGuy (12/3/2014)
Perry Whittle (12/3/2014)
JoshDBGuy (12/1/2014)
I have less than 4 cores so my current setting is 0. However, I see some recommendations to set it to "0 to N". Will this make any difference?Check your Cost threshold for parallelism if its default of 5 it's probably way too low.
Yes, this is my next change. I'm working with a new company that has a cost threshold set to 5. I was going to do an after hours change and change it to a higher number, something like 20 and gauge performance. I'm running into high CXPacket waits, around 60%. I'm also modifying indexes and have seen some pretty decent reductions in CPU usage.
I start from 50 🙂
You can also look for parallel queries from DMVs and look for starting value from there.
Done this as well. I was a little confused as to the proper setting for MAXDOP.
December 3, 2014 at 12:32 pm
JoshDBGuy (12/3/2014)
Ville-Pekka Vahteala (12/3/2014)
JoshDBGuy (12/3/2014)
Perry Whittle (12/3/2014)
JoshDBGuy (12/1/2014)
I have less than 4 cores so my current setting is 0. However, I see some recommendations to set it to "0 to N". Will this make any difference?Check your Cost threshold for parallelism if its default of 5 it's probably way too low.
Yes, this is my next change. I'm working with a new company that has a cost threshold set to 5. I was going to do an after hours change and change it to a higher number, something like 20 and gauge performance. I'm running into high CXPacket waits, around 60%. I'm also modifying indexes and have seen some pretty decent reductions in CPU usage.
I start from 50 🙂
You can also look for parallel queries from DMVs and look for starting value from there.
Done this as well. I was a little confused as to the proper setting for MAXDOP.
I have left it to 0. Most of queries waiting CXPacket had cost 20-30 so increasing cost threshold was enough for me.
December 4, 2014 at 2:27 pm
I thought I'd interject here and make sure that we understand that CXPACKET waits are not, inherently, a bad thing. It just means that queries are going parallel. As with everything in life, moderation is important. If it is accounting for over 50% of your total waits, then it's probably time to address CTP/MAXDOP settings.
I recently started with the company I'm with about 6 months ago and the main production server had the default values set. We were seeing about 65% CXPACKET waits. We had to tweak and tune but we ended up with a CTP of 40 and a MAXDOP of 4 (we have 4 cores, 16 logical processors presented to the OS). For our workload, it worked very well. Brought our CPU usage down about 6-7% overall and CXPACKET waits now account for approx 30% of our waits. Big queries get enough juice to complete quickly while the smaller queries aren't hogging the schedulers and potentially not allowing the larger queries to stretch their legs.
I've definitely learned that this setting usually requires a few additional adjustments and is very system-specific. There's definitely no 'magic number', just pick a middle of the road starting place and tune up/down as needed. 50 is a good starting point for CTP. MAXDOP is dependent on how many cores/logical processors you have.
Another note, when you change these settings, the plan cache gets blown away. Each time we made changes I had to keep an eye on things while the plan cache rebuilt to make sure nothing was getting borked.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply