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:
… und gleich darüber können wir auch die aktuelle
MAXDOP-Einstellung sehen.
Properties
Wenn wir im Execution Plan unsere Abfrage auswählen und dann
auf F4 drücken, öffnet sich das Properties-Fenster. Hier können wir unter
anderem sehen, wie viele Zeilen von jedem CPU gelesen werden mussten.
In diesem Fall liegt ein Unterschied von fast 10.000 Zeilen
zwischen dem „Fleißigsten“ und dem „Faulsten“. Entsprechend gravierender können
die Unterschiede bei Abfragen aus größeren Datenmengen werden (und entsprechend
länger muss auf den Langsamsten gewartet werden).
Von Fall zu Fall entscheiden
Wie eingangs schon erwähnt, gibt es keine Einheitslösung. Wo
der „sweet spot“ liegt, müssen wir jeweils durch Testen herausfinden. Im
Execution Plan können wir sehen, ob Parallelismus verwendet wurde; in der
Statistik können wir uns ansehen, wieviel CPU-Zeit die Query verbraucht hat und
wie lange die gesamte Query gedauert hat.
Wie viele CPUs verwendet werden dürfen, können wir leicht einstellen; die Herausforderung ist
vielmehr, zu entscheiden, wie viele verwendet werden sollten.
Auch eine Überlegung wert: das Verhältnis zwischen CPU-Time
und Query-Time. Nicht in jedem Fall ist das Ziel, die Zeit der Abfrage um noch
ein paar Millisekunden schneller zu machen – wenn dadurch die CPU-Zeit
wesentlich in die Höhe schießt, bedeutet das, dass zwar meine Abfrage recht
schnell ist, dass aber möglicherweise nicht mehr genug Ressourcen für andere
Prozesse zur Verfügung stehen. Ich hätte dann also eine Abfrage optimiert,
blockiere aber alle anderen währenddessen.
Weiterführende Infos, MAXDOP für Fortgeschrittene sozusagen,
gibt es z.B. in diesem Blogartikel von Matteo Lorini.
Viel Spaß beim Tes… nein, das traue ich mich nicht zu sagen…
viel Spaß beim Ausprobieren!
Und vielleicht sehen wir uns ja in einem unserer Kurse zumThema SQL!