MAXDOP should be set to the number of cores / number of NUMA nodes.
To find out how many NUMA nodes there is, execute dbcc memorystatus.
You will have a result looking similar to the following:
"Memory node ID = 64" should not be included, only count the ones that comes in order , in this example we have nodes 0, 1, 2 and 3 which gives us that we have 4 NUMA nodes.
This server has a total of 16 cores, so MAXDOP should be set to 16 / 4 = 4.
If we only see one result set with Memory node Id = 0, then the server does not have NUMA nodes. In that case set MAXDOP = number of cores.
MS Recommendation:
MaxDOP
The option, max degree of parallelism, controls the number of processors that can be used to run a single SQL Server statement using a parallel execution plan. The default value for this configuration is 0, indicating that all available processors can be used. Parallelism is often beneficial for longer running queries or for queries that have complicated execution plans. However, OLTP-centric application performance could sometimes suffer when parallel plans use more threads than the current number of physical processors can handle. The test case now makes the optimal recommendations in the NUMA architecture.
Use the following guidelines when you configure the MAXDOP value:
• For servers that use more than eight processors, use this configuration: MAXDOP=8.
• For servers that have eight or less processors, use this configuration where N equals the number of processors: MAXDOP=0 to N.
• For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs (cores) that are assigned to each NUMA node.
• For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors.
To find out how many NUMA nodes there is, execute dbcc memorystatus.
You will have a result looking similar to the following:
Here we get 5 result sets with a column named Memory node Id = x (and some other result sets that are not relevant).
"Memory node ID = 64" should not be included, only count the ones that comes in order , in this example we have nodes 0, 1, 2 and 3 which gives us that we have 4 NUMA nodes.
This server has a total of 16 cores, so MAXDOP should be set to 16 / 4 = 4.
If we only see one result set with Memory node Id = 0, then the server does not have NUMA nodes. In that case set MAXDOP = number of cores.
MS Recommendation:
MaxDOP
The option, max degree of parallelism, controls the number of processors that can be used to run a single SQL Server statement using a parallel execution plan. The default value for this configuration is 0, indicating that all available processors can be used. Parallelism is often beneficial for longer running queries or for queries that have complicated execution plans. However, OLTP-centric application performance could sometimes suffer when parallel plans use more threads than the current number of physical processors can handle. The test case now makes the optimal recommendations in the NUMA architecture.
Use the following guidelines when you configure the MAXDOP value:
• For servers that use more than eight processors, use this configuration: MAXDOP=8.
• For servers that have eight or less processors, use this configuration where N equals the number of processors: MAXDOP=0 to N.
• For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs (cores) that are assigned to each NUMA node.
• For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors.
No comments:
Post a Comment