„MaxDoP“ – das große Mysterium

Jeder Datenbank Administrator hat sicherlich schon mindestens einmal vor einem SQL Server gesessen und sich gefragt, wie konfiguriert man jetzt am besten diesen „Maximum Degree of Paralism“ oder auf deutsch „maximaler Grad an Parallelität“! Und keiner wusste eine 100%ige Antwort auf diese Frage, denn diese Frage lässt sich auch nicht so einfach beantworten… wie so oft bei den DBAs „it depends“ 😉

Also erst einmal zum Grundverständnis, was ist dieser maximale Grad an Parallelität?

MaxDoP – der Versuch Licht ins Dunkel zu bringen

der Maximale Grad an Parallelität definiert mit wievielen Kernen der SQL Server eine Abfrage bearbeiten kann, wenn der Query Analyzer einen optimalen Ausführungsplan bestimmt hat, der eine Parallelisierung vorsieht. Also was macht der SQL Server eigentlich im Lebenszyklus einer Abfrage, zumindest grob erklärt? Der SQL Server nimmt die Abfrage an und zerlegt sie in seine einzelnen Bestandteile und versucht mittels des Query Analyzers festzustellen, wie die Abfrage am besten auszuführen ist. Also welcher Teil zu erst, mit welchem Index, welche Operationen müssen durchgeführt werden. Hierbei ermittelt der Query Analyzer ebenfalls, wieviele Ressourcen er für die Ausführung der Abfrage benötigt. Wenn also der Query Analyzer zu der Ansicht – aufgrund von Konfiguration und Analyse der Abfrage – dass man eine Abfrage idealerweise parallelisieren kann, dann würde der SQL Server per Default erst einmal alle zur Verfügung stehenden Kerne benutzen. Das ist aber nun einmal nicht wirklich optimal, denn vereinfacht gesagt, kann dann nur diese eine Abfrage ausgeführt werden und für andere Abfragen steht keine Rechenleistung mehr zur Verfügung!

Ermitteln des optimalen MaxDOPs

Hier kommt auch wieder so eine typische SQL Server oder DBA-Antwort, dass kann man nicht genau sagen, sondern muss man ertesten! Wie bei so vielen Parametern am SQL Server hängt es von der eingesetzten Hardware (Server, Virtualisierungshost, Storage) aber auch von der nutzenden Applikation und deren Workload ab. Da hier alle Umgebungen in irgendeiner Art und Weise unterschiedlich sind, kann man hier keine pauschale Aussage treffen, sondern erst einmal nur eine Empfehlung für einen Startwert aussprechen. Bei den meisten Kunden mit 0815-Applikation (sorry) reichen diese Empfehlungen auch völlig aus, aber dann kommen solche Kunden mit riesigen ERP oder CRM Systemen, deren Datenbanken in die Terrabytes gehen und die SQL Server auch gerne mehr als 64 Kerne und 512GB Arbeitsspeicher aufweisen. Bei solchen System muss man natürlich vom Standard abweichen, und hier hilft dann nur noch testen, testen und noch einmal testen bis man den optimalen Grad an Parallelität gefunden hat.

Ich betrachte im Folgenden nur die aktuellen Systeme ab dem SQL Server 2016, um es nicht noch weiter zu komplizieren bzw stehen dieses Systeme schon mehrere Jahre in Betrieb, so dass hier in der Regel ein passender MaxDOP gefunden wurde.

Wie man sich selber herleiten kann, handelt es sich bei dem Konfigurationsparameter für MaxDOP um einen Integer-Wert, der ohne Neustart der Dienste sofort greift. Der Default-Wert von 0, setzt die Limitierung der zu nutzenden CPU-Kerne ausser Kraft, so dass alle Kerne genutzt werden können. Setzt man den Wert auf 1, wie beispielsweise beim Sharepoint, dann erhält man ein Single-Thread-System, heißt man deaktiviert die Parallelisierung! Ebenso kann man die Konfiguration von MaxDOP auch mittels Abfrage-Parametern MAXDOP [integer] ausser Kraft gesetzt bzw überschrieben werden.

Mit dem SQL Server 2019 hat Microsoft auch die Installation bzw die initiale Konfiguration des SQL Servers vereinfacht, in dem jetzt bereits während des Setups eine Empfehlung für das Setzen des MaxDOPs angeboten wird.

Für die manuelle Anpassung schreibt Microsoft in der SQL Server Dokumentation zum MaxDOP wie folgt:

Serverkonfiguration	Anzahl der Prozessoren	Anleitungen
Server mit einzelnem NUMA-Knoten	Kleiner oder gleich 8 logische Prozessoren	Belassen Sie MAXDOP bei # oder weniger logischen Prozessoren
Server mit einzelnem NUMA-Knoten	Mehr als 8 logische Prozessoren	Belassen Sie MAXDOP bei 8
Server mit mehreren NUMA-Knoten	Weniger als oder gleich 16 logische Prozessoren pro NUMA-Knoten	Belassen Sie MAXDOP bei # oder weniger logischen Prozessoren pro NUMA-Knoten
Server mit mehreren NUMA-Knoten	Mehr als 16 logische Prozessoren pro NUMA-Knoten	Sorgen Sie dafür, dass MAXDOP der Hälfte der logischen Prozessoren pro NUMA-Knoten mit einem MAX-Wert von 16 entspricht.

Jetzt haben wir den nächsten Punkt, der die Konfiguration verkompliziert => NUMA… hat mein Server nun ein oder mehrere NUMA-Nodes und noch schlimmer, der SQL Server führt eigenständig virtuelle NUMA Nodes ein 😉 Es bleibt also nichts anderes übrig, als diese Informationen vorher zu ermitteln, am besten direkt über den SQL Server, denn weiß es besser als der SQL Server selber.

select @@SERVERNAME, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), cpu_count, hyperthread_ratio, softnuma_configuration, softnuma_configuration_desc, socket_count, numa_node_count from sys.dm_os_sys_info

Servername	cpu_count	hyperthread_ratio	softnuma_configuration	softnuma_configuration_desc	socket_count	numa_node_count
DemoSQL	20	10	1	ON	2	4

Mittles dieser Abfrage kann man die Anzahl der relevanten NUMA-Nodes ermitteln:

• Quelle/Dokumentation: Konfigurieren der Serverkonfigurationsoption Max. Grad an Parallelität – SQL Server 2022 (16.x)