Was ist eigentlich... MAXDOP?

Die gute oder die schlechte Nachricht zuerst? Ok, die gute: Das Grundprinzip von MAXDOP ist recht schnell erklärt. Die schlechte? Ach ja: Es gibt keine Einheitsformel, keine immer richtige Lösung. Die „richtige Lösung“ müssen wir durch Testen von Fall zu Fall finden. Aber erstmal langsam von Anfang an.

 

Das Grundprinzip von MAXDOP


MAXDOP steht für Maximal Degree of Parallelism und bedeutet nichts anderes, als dass mehrere CPUs parallel, also mehr oder weniger gleichzeitig, an einer Abfrage arbeiten dürfen. Mehr oder weniger ist ein wichtiger Punkt – die Aufgabenverteilung erfolgt nämlich nicht gleichmäßig.

Warum sollten überhaupt mehrere CPUs eine Abfrage bearbeiten? Ist das gut oder schlecht? Zur Veranschaulichung verwenden wir die Jelly Bean-Analogie.

Die Jelly Bean-Analogie

Stell dir vor, du müsstest eine Handvoll Jelly Beans zählen.


1,2,3,…9… schaffst du locker. Aber wie sieht’s damit aus:


Ein ganzer Haufen von Jelly Beans… Klar, schaffst du auch – aber es wird eine ganze Weile dauern. Geht doch viel schneller, wenn wir die Arbeit aufteilen – hol dir ein paar Freunde, und jeder bekommt ein paar Jelly Beans, die er/sie zählen soll.


Wahrscheinlich geht das schneller – aber jetzt haben wir einen zusätzlichen Arbeitsvorgang: Jeder muss sagen, wie viele Jelly Beans er/sie hat und wir müssen die einzelnen Summen zu einer Gesamtsumme addieren. Dieser zusätzliche Arbeitsschritt kostet wieder ein bisschen Zeit.

Außerdem haben nicht alle gleich viele Jelly Beans. (Wenn wir das schon vorab wüssten, bräuchten wir sie ja nicht mehr zu zählen.) Das heißt auch, dass einer schneller mit dem Zählen fertig ist und einer langsamer – aber um zu einer Endsumme zu kommen, müssen wir auf den Langsamsten warten.

 

Jelly Beans und SQL-Abfragen

…und was genau hat das jetzt mit MAXDOP zu tun?

Abhängig davon, wieviel Information bei einer Abfrage verarbeitet werden muss, kann es Sinn machen, die Arbeit auf mehrere CPUs aufzuteilen. Dabei heißt „mehr“ leider nicht immer auch gleich „besser“.

Wenn uns 1000 Freunde helfen, die Jelly Beans zu zählen, dann dauert es auch recht lange, bis wir alle Einzelsummen eingesammelt haben, um sie addieren zu können.


Mit den CPUs und den Abfragen ist es ähnlich: Die einzelnen Ergebnisse wieder miteinander zu verknüpfen braucht Zeit – und auch CPUs müssen auf den Langsamsten warten (also den, der die größte Datenmenge bewältigen muss).

 

 

MAXDOP im SSMS

Ok, wo also finde ich dieses mysteriöse MAXDOP-Setting?


Im Microsoft SQL Server Management Studio (SSMS) klicken wir im Object Explorer mit Rechtsklick auf unsere Datenbank. Dann öffnet sich ein Fenster mit Optionen:


Mit Klick auf den letzten Menüpunkt, Properties:


Wir wählen Advanced aus und scrollen zu „Parallelism“ hinunter:


Unter dem Reiter „Parallelism“ finden wir den Eintrag „Max Degree of Parallelism“… oder MAXDOP. Treffer!

 

MAXDOP Settings

Das Default-Setting für MAXDOP ist 0. Das ist etwas missverständlich, denn es heißt NICHT, wie man vielleicht vermuten würde, dass Null, also kein Parallelismus erlaubt ist, sondern es heißt vielmehr, dass alle verfügbaren CPUs verwendet werden dürfen!

Will man keinen Parallelismus zulassen, müsste man hier 1 einstellen, d.h. es darf nur ein CPU verwendet werden. In manchen Fällen kann das Sinn machen.

Für eine sinnvolle Beschränkung gibt man hier die gewünschte Anzahl an CPUs an, die man für eine Abfrage zur Verfügung stellen will, also beispielsweise 2 oder 4.

Wird Hyper-Threading verwendet, stehen uns hier doppelt so viele Scheduler zur Verfügung, wie physikalisch CPUs eingebaut sind. Als Richtwert könnte man es hier mit der Hälfte der verfügbaren Scheduler versuchen. Also beispielsweise 8 Scheduler -> MAXDOP: 4.

Wird NUMA verwendet, wird es noch komplexer. Hier wird empfohlen, nicht mehr als die Anzahl von Cores in einem NUMA-Node zu verwenden. Das ist aber schon wieder ein anderes Thema – die Microsoft-Dokumentation zum Thema findet sich hier.


MAXDOP in eine Query integrieren

Wir können eine MAXDOP-Anweisung auch direkt an eine Query anhängen – Vorsicht: das gilt nur für die jeweilige Query und überschreibt das Server-MAXDOP-Setting.

Das würde dann beispielsweise so aussehen:

USE AdventureWorks2017

GO

SELECT *

FROM Sales.SalesOrderDetail

ORDER BY ProductID

OPTION (MAXDOP 2)

GO


Cost Threshold for Parallelism

Indirekt können wir MAXDOP auch über die Cost Threshold for Parallelism beeinflussen. Dieses Setting finden wir unter Advanced > Parallelism über dem Max Degree of Parallelism. Jede Abfrage hat bestimmte „Kosten“. Da es für diesen Kostenwert keine Einheit gibt, wird er oft scherzhaft als SQL-Dollar bezeichnet. (Tatsächlich soll es sich dabei um die Zeit handeln, die diese Abfrage auf einem bestimmten Computer eines bestimmten Microsoft Mitarbeiters in den 90ern gedauert hätte.)

Per Default steht das Setting für Cost Threshold for Parallelism auf 5; die meisten Queries haben aber weitaus höhere „Kosten“. Ist 5 eingestellt, dürfen die meisten Abfragen gleich Parallelismus verwenden; setzt man diesen Schwellwert höher, beispielsweise auf 50, dürfen nur kostspieligere Abfragen auch parallel arbeiten.

Auch das erfordert Tests und einen Blick in die Execution Plans – wieviel kosten unsere Abfragen so im Schnitt? Danach können wir uns bei der Einstellung der Cost Threshold richten.

 

 

Wie können wir testen?

Ok, jetzt wissen wir, wo wir das MAXDOP-Setting verändern können. Wie können wir aber testen, was es uns bringt?

Zum einen brauchen wir dazu eine Datenbank mit einer realistischen Befüllung mit Daten – klar: Wenn wir nur mit wenigen Testdaten Abfrage-Zeiten auslesen wollen, wird das Ergebnis massiv verfälscht.

Zum anderen müssen wir nur ein bisschen durch das SSMS navigieren.

 

Statistik einschalten

Die Statistik einzuschalten ist schon einmal eine gute Idee:

set statistics io, time on

Damit erhalten wir, wenn wir eine Query ausführen, zusätzliche Informationen im „Messages-Tab“ des Ergebnisfensters:


Wir können hier ablesen, wie lange es gedauert hat, bis eine Abfrage ausgeführt wurde (elapsed time), sowie die „CPU time“. Ist die CPU-Time höher als die Geschwindigkeit der gesamten Abfrage, kam offenbar Parallelismus zum Einsatz: Die CPU-Time sagt uns, wie lange mehrere CPUs in Summe an der Abfrage gearbeitet haben.

Beim Testen mit unterschiedlichen MAXDOP-Einstellungen geben uns diese Werte wertvolle Hinweise darauf, mit welchen Einstellungen wir im jeweiligen Fall am besten unterwegs sind.

 

Execution Plan verwenden

Der Execution Plan gibt uns eine Übersicht darüber, wie denn unsere Abfrage so abläuft. Auch hier finden wir Informationen über MAXDOP. Wir können einen Estimated oder Actual Execution Plan verwenden; wie der Name schon sagt, bekommen wir bei ersterem eine informierte Schätzung, bei letzterem eine Analyse der ausgeführten Abfrage. Aber Achtung: die Erstellung des Execution Plans kostet auch etwas Zeit – die Abfragezeit wird dadurch also leicht verfälscht!

Tastenkombination

Einschalten können wir die Pläne über Tastenkombination, Menütabs oder Toolbar; mit STRG + L bekommen wir den Estimated, mit STRG + M den Actual Execution Plan.

Menüleiste

In der Menüleiste wählen wir „Query“ und können uns dort zwischen Estimated oder Actual Execution Plan entscheiden:


Toolbar

In der Toolbar gibt es zwei Icons, die wir für Estimated oder Actual Execution Plan anklicken können:



Execution Plan lesen

Ein Execution Plan für eine (nicht besonders gute) Abfrage könnte etwa so aussehen:


Dabei sagen uns die Doppelpfeile im gelben Feld, dass hier Parallelismus verwendet wurde, und unter „Parallelism“ sehen wir, dass das Zusammenführen der Informationen (Gather Streams) in diesem Fall 19% unserer Rechenzeit verbraucht hat! Vielleicht war in diesem Fall Parallelismus doch nicht ganz so schlau.

Wenn wir im Execution Plan mit der Maus über das Select gehen, sehen wir unter „Estimated Subtree Cost“ die geschätzten Kosten der Abfrage: