March 27, 2015 at 6:25 am
Hi All,
whenver I read the article about MAXDOP , i get more confusion about the parallelism setting.
Can anyone please help me on below scenario.
I have 16 cpu core with two numa and having two two big database and the transaction load is also more.
What should I set for MAxdop setting . I have set it as 1.Is this correct. Please suggest.
Thanks in advance...
March 27, 2015 at 7:51 am
there is no hard and fast rule that is correct in all circumstances, but I very much doubt 1 (no parallelism) is the correct value for you, however, there are some guidelines:
For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node. (so 8)
the more queries that run at one time, go lower, so for you we are looking at between 4 and 8, but I would start at 8.
Cost threshold for parallelism should also be considered, the default of 5 is generally too low, I start at 25.
The only way to get the best value is to test, take your most frequent queries and see what works for them.
---------------------------------------------------------------------
March 27, 2015 at 7:55 am
The MAXDOP setting allows queries with higher calculated costs to run on multiple threads across many processors. This can be very advantageous and allow your more complex queries to complete in shorter amounts of time. I would strongly recommend that you allow this setting to be auto-negotiated by the SQL Server rather then specifying a hard and fast value. If you do notice a high number of CXPACKET waits, you may want to adjust the configuration for "cost threshold for parallelism" to a higher value than the default, 5. My recommendation, that is shared by many, is to start at 50 and review performance metrics through extended events.
If you feel you absolutely need to configure the MAXDOP to a set value, use 3 for your instance (1.5*NUMA). This formula has been referenced in forums on this site before.
March 27, 2015 at 8:21 am
probably best to read microsofts recommendations: http://support.microsoft.com/en-us/kb/2806535
A maxdop of either 1 or 0 is unlikely to be your best bet.
---------------------------------------------------------------------
March 27, 2015 at 9:32 am
Christopher Kutsch (3/27/2015)
If you feel you absolutely need to configure the MAXDOP to a set value, use 3 for your instance (1.5*NUMA). This formula has been referenced in forums on this site before.
If that's a reference to the recommendation that I mention sometimes, based on a conversation with Adam Machanic, then you've got the formula the wrong way round. It's not 1.5*(number of NUMA nodes) as that would be quite weird and highly unlikely to produce an optimal config. It's 1.5*(Number of physical cores in a NUMA node), so 12 (assuming that hyperthreading is enabled)
He hasn't, to my knowledge, published his testing and, based on what I know about the kind of systems he works with, is probably for massive anaytics-type systems, and so I would recommend either 8 or 12 depending on whether the system is more OLTP or more OLAP.
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
March 30, 2015 at 12:44 am
Thanks to all for helping me on this issue. Now I got some idea to use MAXDOP.
Thanks a lot 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply