October 29, 2018 at 6:24 pm
Hi all,
By default in sql 2008 r2, we have MAXDOP = 0. This means that a certain query will going to paralell if their cost is bigger than CTFP (by default 5), and it will use all the cpu cores available or visible in SQL. Even if we have more than 1 NUMA node for our SQL.
Nowadays I have a HP Proliant GEN9 with 2 CPU with 16 cores each one. This give me 32 cpu cores and 2 NUMA nodes. With hiperthreading enabled. NUMA is the acronym for: Non Uniform Memory Access, and in practical terms means that the memory assigned to each CPU will not be accessible for the others CPUs.
Thus, if I set MAXDOP = 8 (Brent Ozar's rule of thumb to start tweaking), this means that the same query will use just 8 cores instead of 32. And that is a good start to diminish the CX_PACKET (parallel), in SQL Basically, this wait occurs when a thread is waiting for a resource. If this query are using all the cores on NUMA nodes, each node they can´t share the memory asigned to each one and start to increment the CX_PACKET wait.
So far so good. My point is that when I reviewed the BIOS set up for this server model, I realized that there is a good looking name for a BIOS parameter called NUMA group size optimization, and it has two available values: The default setting is Clustered, which basically leads to the fact that Windows will create for each physical processor a processor group.This means that in my case I will get two NUMA nodes with 16 cores. If I change the setting to FLAT, each core will treated as a single group and I will see 32 processors not cores. The other "nice" feature of this is, when you use CLUSTERED mode, the whole memory will be accessed by all the NUMA nodes!!
This mean that if I have MAXDOP=0, I will not need to change it to upper value?
Am I reazoning this concepts correctly?
if someone have similar configuration, what value uses for MAXDOP?
Thank you in advance.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply