0Kommentare
LeonardH
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 vonAnfang an.
MAXDOP steht für Maximal Degree of Parallelism und bedeutet nichts anderes, als dassmehrere CPUs parallel, also mehr oderweniger gleichzeitig, an einer Abfrage arbeiten dürfen. Mehr oder weniger istein 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 dieArbeit aufteilen – hol dir ein paar Freunde, und jeder bekommt ein paar JellyBeans, die er/sie zählen soll. Wahrscheinlich geht das schneller – aber jetzt haben wireinen zusätzlichen Arbeitsvorgang: Jeder muss sagen, wie viele Jelly Beans er/siehat und wir müssen die einzelnen Summen zu einer Gesamtsumme addieren. Dieserzusätzliche Arbeitsschritt kostet wieder ein bisschen Zeit. Außerdem haben nicht alle gleich viele Jelly Beans. (Wenn wirdas schon vorab wüssten, bräuchten wir sie ja nicht mehr zu zählen.) Das heißtauch, dass einer schneller mit dem Zählen fertig ist und einer langsamer – aberum zu einer Endsumme zu kommen, müssen wir auf den Langsamsten warten. …und was genau hat das jetzt mit MAXDOP zu tun? Abhängig davon, wieviel Information bei einer Abfrageverarbeitet 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, danndauert es auch recht lange, bis wir alle Einzelsummen eingesammelt haben, umsie addieren zu können.Jelly Beans und SQL-Abfragen
Mit den CPUs und den Abfragen ist es ähnlich: Die einzelnenErgebnisse wieder miteinander zu verknüpfen braucht Zeit – und auch CPUs müssenauf den Langsamsten warten (also den, der die größte Datenmenge bewältigen muss).
Ok, wo also finde ich dieses mysteriöse MAXDOP-Setting?
Im Microsoft SQL Server Management Studio (SSMS) klicken wir imObject Explorer mit Rechtsklick auf unsere Datenbank. Dann öffnet sich einFenster 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 „MaxDegree of Parallelism“… oder MAXDOP. Treffer!
MAXDOP Settings
Das Default-Setting für MAXDOP ist 0. Das ist etwasmissverständlich, denn es heißt NICHT, wie man vielleicht vermuten würde, dassNull, also kein Parallelismus erlaubt ist, sondern es heißt vielmehr, dass alleverfügbaren CPUs verwendet werden dürfen!
Will man keinen Parallelismus zulassen, müsste man hier 1einstellen, d.h. es darf nur ein CPU verwendet werden. In manchen Fällen kanndas Sinn machen.
Für eine sinnvolle Beschränkung gibt man hier die gewünschteAnzahl an CPUs an, die man für eine Abfrage zur Verfügung stellen will, alsobeispielsweise 2 oder 4.
Wird Hyper-Threading verwendet, stehen uns hier doppelt soviele Scheduler zur Verfügung, wie physikalisch CPUs eingebaut sind. AlsRichtwert könnte man es hier mit der Hälfte der verfügbaren Schedulerversuchen. Also beispielsweise 8 Scheduler -> MAXDOP: 4.
Wird NUMA verwendet, wird es noch komplexer. Hier wirdempfohlen, nicht mehr als die Anzahl von Cores in einem NUMA-Node zu verwenden.Das ist aber schon wieder ein anderes Thema – die Microsoft-Dokumentation zumThema findet sich hier.
MAXDOP in eine Query integrieren
Wir können eine MAXDOP-Anweisung auch direkt an eine Queryanhängen – Vorsicht: das gilt nur für die jeweilige Query und überschreibt dasServer-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 findenwir unter Advanced > Parallelism über dem Max Degree of Parallelism. Jede Abfragehat bestimmte „Kosten“. Da es für diesen Kostenwert keine Einheit gibt, wird eroft scherzhaft als SQL-Dollar bezeichnet. (Tatsächlich soll es sich dabei umdie Zeit handeln, die diese Abfrage auf einem bestimmten Computer einesbestimmten Microsoft Mitarbeiters in den 90ern gedauert hätte.)
Per Default steht das Setting für Cost Threshold for Parallelism auf 5; die meisten Queries habenaber weitaus höhere „Kosten“. Ist 5 eingestellt, dürfen die meisten Abfragengleich Parallelismus verwenden; setzt man diesen Schwellwert höher,beispielsweise auf 50, dürfen nur kostspieligere Abfragen auch parallelarbeiten.
Auch das erfordert Tests und einen Blick in die Execution Plans – wieviel kosten unsereAbfragen so im Schnitt? Danach können wir uns bei der Einstellung der Cost Threshold richten.
Ok, jetzt wissen wir, wo wir das MAXDOP-Setting verändernkönnen. Wie können wir aber testen, was es uns bringt?
Zum einen brauchen wir dazu eine Datenbank mit einerrealistischen Befüllung mit Daten – klar: Wenn wir nur mit wenigen Testdaten Abfrage-Zeitenauslesen 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ätzlicheInformationen im „Messages-Tab“ des Ergebnisfensters:
Wir können hier ablesen, wie lange es gedauert hat, bis eine Abfrageausgeführt wurde (elapsed time),sowie die „CPU time“. Ist die CPU-Time höher als die Geschwindigkeit dergesamten Abfrage, kam offenbar Parallelismus zum Einsatz: Die CPU-Time sagtuns, wie lange mehrere CPUs in Summe an der Abfrage gearbeitet haben.
Beim Testen mit unterschiedlichen MAXDOP-Einstellungen gebenuns diese Werte wertvolle Hinweise darauf, mit welchen Einstellungen wir imjeweiligen Fall am besten unterwegs sind.
Execution Plan verwenden
Der Execution Plan gibt uns eine Übersicht darüber, wie dennunsere Abfrage so abläuft. Auch hier finden wir Informationen über MAXDOP. Wirkönnen einen Estimated oder Actual Execution Plan verwenden; wie derName schon sagt, bekommen wir bei ersterem eine informierte Schätzung, beiletzterem eine Analyse der ausgeführten Abfrage. Aber Achtung: die Erstellungdes Execution Plans kostet auch etwas Zeit – die Abfragezeit wird dadurch alsoleicht 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 dortzwischen Estimated oder Actual Execution Plan entscheiden:
Toolbar
In der Toolbar gibt es zwei Icons, die wir für Estimated oderActual Execution Plan anklicken können:
Execution Plan lesen
Ein Execution Plan für eine (nicht besonders gute) Abfragekönnte etwa so aussehen:
Dabei sagen uns die Doppelpfeile im gelben Feld, dass hierParallelismus verwendet wurde, und unter „Parallelism“ sehen wir, dass das Zusammenführender Informationen (Gather Streams) indiesem Fall 19% unserer Rechenzeit verbraucht hat! Vielleicht war in diesemFall Parallelismus doch nicht ganz so schlau.
Wenn wir im Execution Plan mit der Maus über das Selectgehen, sehen wir unter „Estimated SubtreeCost“ die geschätzten Kosten der Abfrage:
… und gleich darüber können wir auch die aktuelleMAXDOP-Einstellung sehen.
Properties
Wenn wir im Execution Plan unsere Abfrage auswählen und dannauf F4 drücken, öffnet sich das Properties-Fenster. Hier können wir unteranderem sehen, wie viele Zeilen von jedem CPU gelesen werden mussten.
In diesem Fall liegt ein Unterschied von fast 10.000 Zeilenzwischen dem „Fleißigsten“ und dem „Faulsten“. Entsprechend gravierender könnendie Unterschiede bei Abfragen aus größeren Datenmengen werden (und entsprechendlänger muss auf den Langsamsten gewartet werden).
Von Fall zu Fall entscheiden
Wie eingangs schon erwähnt, gibt es keine Einheitslösung. Woder „sweet spot“ liegt, müssen wir jeweils durch Testen herausfinden. ImExecution Plan können wir sehen, ob Parallelismus verwendet wurde; in derStatistik können wir uns ansehen, wieviel CPU-Zeit die Query verbraucht hat undwie lange die gesamte Query gedauert hat.
Wie viele CPUs verwendet werden dürfen, können wir leicht einstellen; die Herausforderung istvielmehr, zu entscheiden, wie viele verwendet werden sollten.
Auch eine Überlegung wert: das Verhältnis zwischen CPU-Timeund Query-Time. Nicht in jedem Fall ist das Ziel, die Zeit der Abfrage um nochein paar Millisekunden schneller zu machen – wenn dadurch die CPU-Zeitwesentlich in die Höhe schießt, bedeutet das, dass zwar meine Abfrage rechtschnell ist, dass aber möglicherweise nicht mehr genug Ressourcen für andereProzesse 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!
By LeonardH on Oktober 31, 2019 at 11:51
Tagged: SQL, SQL Server Management Studio, SQL Server
