TSQL – MaxDOP und NUMA – Empfehlungen

Letzte Woche habe ich einen Beitrag verfasst zum Thema, wie ermittelt man ein paar wichtige Eckpunkte des SQL Servers wie Anzahl CPU, Anzahl Schedulers, Min.- und Max Memory oder maxDOP über den SQL Server mit einem kleinen TSQL Skript…

Irgendwie hat mir die Empfehlung für den „optimalen maxDOP“ keine Ruhe gelassen und ich habe nochmal zahlreiche Blogbeiträge und Artikel in der MSDN dazu gelesen, anscheinend hat sich in den letzten Jahren ein wenig was dazu geändert. Vielleicht liegt es an der fortschreitenden CPU-Architektur oder an der zunehmenden Virtualisierung von SQL Servern, je nach dem auf welcher Seite man liest (im Vergleich zu vor 2-3 Jahren) werden andere Herangehensweisen beschrieben.

Laut Microsoft ( und auch die Darstellung in anderen Blogs/Foren lauten hier gleich ) wird folgendes empfohlen:

SQL Server 2005 and later versions

Server with single NUMA node	Less than 8 logical processors	Keep MAXDOP at or below # of logical processors
Server with single NUMA node	Greater than 8 logical processors	Keep MAXDOP at 8
Server with multiple NUMA nodes	Less than 8 logical processors per NUMA node	Keep MAXDOP at or below # of logical processors per NUMA node
Server with multiple NUMA nodes	Greater than 8 logical processors per NUMA node	Keep MAXDOP at 8

Quelle:https://support.microsoft.com/en-us/kb/2806535

Wie kann man diese Werte aber nun in mein Skript von gestern einpflegen, dass eine Empfehlung für den optimalen maxDOP („Maximum Degree of Parallelism“) dabei herauskommt?

Vorraussetzungen für die Ermittlung eines optimalen maxDOP Wertes

Welche Parameter bzw Werte muss man beachten, wenn man gemäß obiger Tabelle Empfehlungen für einen optimalen Wert für den Maximum Degree of Parallelism geben möchte?
Der optimale maxDOP sollte maximal 8 betragen, hierzu ist es aber entscheidend zu wissen, wieviele logische Prozessoren pro NUMA Node dem SQL Server zugewiesen sind. Bei Servern, die mehrere NUMA Nodes haben und NUMA aktiviert wurde und auch noch Hyper-Threading nutzt, sollte der Wert für maxDOP die Anzahl der physischen Prozessoren pro NUMA-Knoten nicht überschreiten. Somit ergibt sich Notwendigkeit die folgenden Werte zu ermitteln, um damit weitere Berechnungen durchzuführen, um sich dem optimalen Wert anzunähern.

• Anzahl der NUMA-Nodes für den jeweiligen SQL Server
• Anzahl der logischen Prozessoren bzw Kerne
• Ist Hyperthreading aktiviert

Wieviele NUMA – Nodes hat der SQL Server?

Kann man diese Werte auch direkt aus dem SQL ermitteln? Steht das irgendwo drin? Vielleicht über eine DMV zu ermitteln?

Zumindest steht es beim Starten des SQL Servers im Log wieviele NUMA Nodes der Server hat, dann wird es doch sicherlich auch im laufenden Betrieb verfügbar sein…

Und tatsächlich konnte ich folgende Abfrage der System-Views ermitteln, die hier behilflich ist:

select * from sys.dm_os_memory_nodes where memory_node_id < 64
idealer noch 

select count(*) from sys.dm_os_memory_nodes where memory_node_id < 64

Wie wir sehen können, handelt es sich bei meiner Testmaschine um einen SQL Server mit nur einer NUMA-Node, wenn wir uns jetzt die obige Tabelle anschauen, dann bleiben uns nur noch zwei Möglickeiten für den „optimalen“ Max Degree of Parallism (maxDOP). Aber halt STOP, welche Zeile muss ich nun auswählen? Wieviele logische Prozessoren hat mein SQL Server aber bzw wie viele kann er nutzen?

Also wieder eine weitere einfache Abfrage ausgeführt:

SELECT cpu_count FROM sys.dm_os_sys_info

Wie nicht anders zu erwarten, erhalte ich für meinen Test SQL Server einen CPU Count von 4 logischen Prozessoren. Mit diesen beiden Werten, also Anzahl logischer Prozessoren und Anzahl NUMA Nodes kann man eine Entscheidung treffen für einen „optimalen“ Wert für den Konfigurationsparameter „maxDOP“. Laut obiger Tabelle muss der Wert genau der Anzahl der logischen Prozessoren oder unterhalb davon liegen.

Aber auch hier haben schlaue MVPs sich zahlreiche Gedanken gemacht, so dass ich diese nur aufgreifen muss. Bzw eigentlich nur sagen kann, es handelt sich bei diesen Empfehlungen ganz stark um Aussagen, die jeder eigentlich nur für seinen eigenen Server für seine ganz eigene Applikation ermitteln kann. Denn gerade der Wert „maxDOP“ und der dazugehörige Parameter „Cost Threshold for Parallelism“ sind sehr relevant für die Performance des SQL Servers. Man kann zwar eine Aussage/Empfehlung treffen => „Stellt alle eure SQL Server auf einen Wert von maxDOP von 6, das passt schon…“ Meine Vorschläge oder die der anderen SQL Schreiber sind nur grobe Vorgaben/Best Practises, die man als Anhaltspunkt oder Startpunkt für weitergehende Tests verwenden kann.

Aber weiter im Kontext… zahlreiche SQL Server Cracks haben sich tagelang wenn nicht wochenlang mit dem „optimalen“ Wert für maxDOP auseinander gesetzt und getestet und getestet und getestet. 😉

Herauskam, dass man bei einer Vielzahl von Tests festgestellt hat, dass die Hälfte der obigen Annahme für viele Systeme die beste Voraussetzung ist. Also um bei meinem Test SQL Server zu bleiben => Anzahl der logischen Prozessoren zu Anzahl NUMA Nodes ergab bei mir : 1 NUMA Node und 4 logische Prozessoren, daraus ergäbe sich dann ein „optimaler“ Wert für maxDOP von 2. In einigen Blogs habe ich den Verdacht gelesen, dass es Zusammenhänge mit Hyperthreading geben könnte. Man kann es nicht genug sagen, testet diesen Wert auf eurem System… testen, testen, testen.

In diesem Zusammenhang – wie auch bereits weiter oben angemerkt – gibt es noch einen weiteren Wert in der Konfiguration des SQL Server, der in direktem Zusammenhang mit maxDOP steht => „Cost Threshold for Parallelism“
Dieser Wert ist auf jeden Fall auf aktuelleren Systemen mit aktuellen Prozessoren weit wichtiger für die Performance eines SQL Servers, aber dazu gibt es demnächst einen eigenen Blogbeitrag 😉

Fazit:

Wenn man unbedingt eine grundlegende Formel zur Konfiguration des Wertes maxDOP erstellen möchte, so sollte diese doch heißen (neben testen, testen und nochmals testen) :

Wenn die Anzahl der NUMA Nodes = 1 ist und die Anzahl der logischen Prozessoren  <= 8 dann setze maxDOP auf 4, ansonsten wenn die Anzahl der logischen Prozessoren größer 8 ist, dann setze maxDOP auf 8.

Für mehr als eine NUMA Node gilt die Berechnung ebenfalls nur dann eben für die Anzahl der logischen Prozessoren pro NUMA Node.

Wenn das alles nichts hilft und die Anzahl der logischen Prozessoren <= 8 liegt, dann Anzahl der logischen Prozessoren / 2.

Genau so habe ich das (hoffentlich) auch in meinem Statement dargestellt.