Was ist MAXDOP?
Euch ist beim Ansehen von Ausführungsplänen im SQL Server sicher schon einmal aufgefallen, dass immer wieder an bestimmten Operationen zwei kleine Pfeile auf gelbem Grund auftreten (im SQL Server Management Studio) und die Operation “Parallelism (Gather Streams)”. Was das bedeutet ist, vereinfacht gesagt, dass der SQL Server an einigen Stellen entscheidet, bestimmte Operationen parallel in mehreren Threads auszuführen. Wie viele Prozessoren der SQL Server dafür verwenden kann, könnt ihr über den MAXDOPAbfragehinweis steuern. MAXDOP Steht dabei für “Max Degree Of Parallelism”, also den maximalen Grad der Parallelität.
Wie könnt ihr den MAXDOP setzen?
Der MAXDOP ist ein Abfragehinweis – und wie bei allen Abfragehinweisen gilt es hier vorsichtig zu sein, denn ihr überschreibt damit das Verhalten, das entweder die Entwickler der relationalen Datenbankengine oder euer Datenbank-Administrator vorgesehen haben. Und wenn ihr das tut, dann solltet ihr zumindest einen guten Grund dafür haben.
Soviel der Warnung, doch nun zum Setzen des MAXDOP. Generell gibt es für den Server oder die Datenbank beim Setup festgelegte Werte für den MAXDOP, diese können durchaus unterschiedlich sein, wobei der Wert für die Datenbank dann den für den Server überschreibt. Beide Werte könnt ihr wiederum bei der Abfrage überschreiben. Dafür setzt ihr ans Ende eurer Abfrage ein OPTION (MAXDOP x) mit einem ganzzahligen Wert x. Besonderheiten besitzen der Wert 0, bei dem die Abfrage explizit angewiesen wird, so viele Kerne zu verwenden, wie zur Verfügung stehen und der Wert 1, bei dem die Abfrage nur einen Kern verwendet, Parallelisierung also explizit abgeschaltet wird.
Nehmen wir uns nun eine Abfrage vor, bei der es zu einer Parallelisierung kommt. Dafür fragen wir in der WideWorldImporters Datenbank die OrderLines-Tabelle im Sales-Schema ab und lassen uns den Abfrageplan für die Abfrage ausgeben:
SET SHOWPLAN_XML ON;
GO
SELECT
[OrderLineID]
,[OrderID]
,[StockItemID]
,[Description]
,[PackageTypeID]
,[Quantity]
,[UnitPrice]
,[TaxRate]
,[PickedQuantity]
,[PickingCompletedWhen]
,[LastEditedBy]
,[LastEditedWhen]
FROM [Sales].[OrderLines]
Hier seht ihr nur einen Scan des geclusterten Indexes, was durchaus stimmig ist, da hier ja die ganze Tabelle abgefragt wird. Passen wir die Abfrage nun so an, dass wir parallelisierte Operationen sehen. Dafür sortieren wir die Ausfrage nach einer Spalte, die nicht führend ist im geclusterten Index:
SELECT
[OrderLineID]
,[OrderID]
,[StockItemID]
,[Description]
,[PackageTypeID]
,[Quantity]
,[UnitPrice]
,[TaxRate]
,[PickedQuantity]
,[PickingCompletedWhen]
,[LastEditedBy]
,[LastEditedWhen]
FROM [Sales].[OrderLines]
ORDER BY [StockItemID]
Nun seht ihr im Ausführungsplan den Scan in paralleler Ausführung (wenn eure Datenbank nicht so konfiguriert ist, dass sie keine Parallelität zulässt), was ihr am gelben Kreis mit den kleinen Pfeilen an der Operation erkennen könnt (im SSMS) oder am grünen Pfeil (im Azure Data Studio). Außerdem sind zwei weitere Operationen zum Ausführungsplan hinzugekommen: eine Sort-Operation (logisch, da wir ja die Ergebnismenge sortieren möchten) und eine “Parallelism (Gather Streams)”-Operation bei der die Resultate der verschiedenen parallel ausgeführten Operationen wieder zur Ergebnismenge zusammengeführt werden. Außerdem seht ihr im XML des Ausführungsplans ein Attribut “EstimatedAvailableDegreeOfParallelism”, das euch sagt, was der Query Optimizer schätzt, wie viele parallele Prozesse verwendet werden können.
Versuchen wir nun, das Verhalten zu verändern und die parallele Ausführung zu unterbinden, indem wir den MAXDOP auf 1 setzen:
SELECT
[OrderLineID]
,[OrderID]
,[StockItemID]
,[Description]
,[PackageTypeID]
,[Quantity]
,[UnitPrice]
,[TaxRate]
,[PickedQuantity]
,[PickingCompletedWhen]
,[LastEditedBy]
,[LastEditedWhen]
FROM [Sales].[OrderLines]
ORDER BY [StockItemID]
OPTION (MAXDOP 1)
Im Ausführungsplan seht ihr nun keine Pfeile mehr am Scan des geclusterten Index und auch keine Parallelism-Operation mehr. Die Abfrage wird nicht mehr parallel ausgeführt. Auch im Quellcode des Plans seht ihr nun, dass der EstimatedAvailableDegreeOfParallelism auf 1 gesetzt wurde. Ihr könnt also das Verhalten eures Servers mit dem MAXDOP-Abfragehinweis beeinflussen und steuern, ob bestimmte Abfragen parallel auf mehreren Prozessoren verarbeitet werden. Insgesamt steigen dadurch natürlich die CPU-Kosten, da die Parallelisierung einen Overhead verursacht, in Summe sinkt aber oftmals die Ausführungszeit, da diese höheren Kosten in mehreren Threads gleichzeitig abgearbeitet werden. Wie eingangs erwähnt solltet ihr bei der Verwendung derartiger Hinweise vorsichtig sein, da sich im Normalfall sowohl die Entwickler des SQL Server als auch die Systemadministratoren beim Setzen der Parameter etwas gedacht haben, doch habt ihr die Möglichkeit, besonders ressourcenhungrige Abfragen, die viele CPU-Operationen benötigen und zu Zeiten laufen, in denen auch andere Prozesse auf dem Server aktiv sind, etwas “freundlicher” zu gestalten, indem ihr verhindert, dass sie alle Prozessorkerne blockieren, die zur Verfügung stehen. Oder eben dafür zu sorgen, dass sie möglichst schnell laufen indem sie sich alle Ressourcen schnappen, die irgendwie verfügbar sind. Ihr habt die Wahl.
