Was bedeutet SHOWPLAN?
Wenn ihr euch schon immer mal gefragt habt, was der SQL Server eigentlich macht zwischen dem Zeitpunkt wo ihr mit Angstschweiß auf der Stirn im Azure Data Studio auf F5 gedrückt habt und dem Zeitpunkt wo euch Ergebnisse eurer Abfrage angezeigt werden, dann ist SHOWPLAN die Lösung für euch. Eine Warnung sei aber vorweg geschickt: das Lesen von Ausführungsplänen ist eine Wissenschaft für sich und es gibt viele, die sich mit viel Hingabe dieser Wissenschaft gewidmet haben und das auch um einiges besser können, als euer Shogun hier (wir neigen unser Haupt in Ehrfurcht in Richtung des großen Ricken-San). Erwartet hier also keine großen Erkenntnisse, sondern eher einen Überblick, was es da so gibt. Sozusagen den Eingang des Kaninchenbaus.
Wie könnt ihr mit SHOWPLAN arbeiten?
Um uns die verschiedenen Pläne und Plan-Optionen anzusehen, schreiben wir zunächst eine Abfrage auf unserer WideWorldImporters-Datenbank:
SELECT
[StockItemID]
,[StockItemName]
,[SupplierName]
,[Brand]
,[Size]
,[LeadTimeDays]
,[QuantityPerOuter]
,[IsChillerStock]
,[TaxRate]
,[UnitPrice]
,[RecommendedRetailPrice]
,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
LEFT JOIN [Purchasing].[Suppliers] s
ON i.SupplierID = s.SupplierID
WHERE StockItemID = 200
Diese Abfrage liefert genau eine Zeile zurück. Finden wir nun heraus, wie der SQL Server zu dieser Zeile findet. Dafür schauen möchten wir uns den Ausführungsplan der Abfrage ansehen. Diesen können wir erhalten indem wir die SHOWPLAN_XML-Option aktivieren (und idealerweise direkt nach der Abfrage wieder deaktivieren), das sieht dann wie folgt aus:
SET SHOWPLAN_XML ON;
GO
SELECT
[StockItemID]
,[StockItemName]
,[SupplierName]
,[Brand]
,[Size]
,[LeadTimeDays]
,[QuantityPerOuter]
,[IsChillerStock]
,[TaxRate]
,[UnitPrice]
,[RecommendedRetailPrice]
,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
LEFT JOIN [Purchasing].[Suppliers] s
ON i.SupplierID = s.SupplierID
WHERE StockItemID = 200;
GO
SET SHOWPLAN_XML OFF;
GO
Im Ergebnisfenster seht ihr nun eine grafische Repräsentation des Ausführungsplans (im SQL Server Management Studio seht ihr zunächst ein Ergebnis mit einer XML-Zelle, wenn ihr auf diese Klickt, öffnet sich die grafische Darstellung, im Azure Data Studio wird die grafische Darstellung sofort geöffnet. Diese Darstellung zeigt, dass auf beiden Tabellen eine Clustered Index Seek-Operation ausgeführt wird, dass also im Clustered Index der jeweiligen Tabelle nach Zeilen gesucht wird und dass diese Zeilen dann in einem Nested Loop zusammengeführt werden. So weit so gut, doch wenn ihr nun diesen Plan möglicherweise detaillierter ansehen oder (ganz oder in Teilen) abspeichern möchtet, ist die XML-Darstellung trotz unterstütztem XML-Datentyp sehr unhandlich. Sehen wir also nach, was wir noch für Optionen haben. Statt den Plan als XML auszugeben, können wir ihn auch textuell in Tabellenform ausgeben lassen, indem wir nicht SHOWPLAN_XML sondern SHOWPLAN_ALL aktivieren:
SET SHOWPLAN_ALL ON;
GO
SELECT
[StockItemID]
,[StockItemName]
,[SupplierName]
,[Brand]
,[Size]
,[LeadTimeDays]
,[QuantityPerOuter]
,[IsChillerStock]
,[TaxRate]
,[UnitPrice]
,[RecommendedRetailPrice]
,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
LEFT JOIN [Purchasing].[Suppliers] s
ON i.SupplierID = s.SupplierID
WHERE StockItemID = 200;
GO
SET SHOWPLAN_ALL OFF;
GO
Diese Form der Darstellung ist in Anwendungen besser, in denen das XML nicht wie im SSMS oder im Azure Data Studio direkt in eine grafische Darstellung umgewandelt werden kann. Eine noch einfachere Darstellung mit weniger Spalten in der Ergebnismenge könnt ihr über die Option SHOWPLAN_TEXT aktivieren. Hier erhaltet ihr neben der textuellen Darstellung der einzelnen Schritte im Ausführungsplan noch das Originale Statement mitgeliefert, was unter Umständen sehr hilfreich sein kann.
Soweit ist das ganz nett, aber was bedeutet das alles? Um eine Idee davon zu erhalten, was hier geschieht, ändern wir unsere Abfrage ein wenig und suchen nicht mehr nach einer ID sondern nach einem Teil des Artikel-Namens:
SET SHOWPLAN_XML ON;
GO
SELECT
[StockItemID]
,[StockItemName]
,[SupplierName]
,[Brand]
,[Size]
,[LeadTimeDays]
,[QuantityPerOuter]
,[IsChillerStock]
,[TaxRate]
,[UnitPrice]
,[RecommendedRetailPrice]
,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
LEFT JOIN [Purchasing].[Suppliers] s
ON i.SupplierID = s.SupplierID
WHERE StockItemName like '%USB%';
GO
SET SHOWPLAN_XML OFF;
GO
Hier seht ihr dass sich der Ausführungsplan geändert hat, wo vorher zwei Clustered Index Seeks standen (also eine direkte Suche im B-Baum des Index) stehen nun Clustered Index Scans, der Index wird also in seiner vollen Breite gescannt (durchsucht). Um das zu erkennen, aktivieren wir die IO-Statistik-Ausgabe (wie in Woche 9 gezeigt) und vergleichen den Output beider Statements:
SET STATISTICS IO ON
SELECT
[StockItemID]
,[StockItemName]
,[SupplierName]
,[Brand]
,[Size]
,[LeadTimeDays]
,[QuantityPerOuter]
,[IsChillerStock]
,[TaxRate]
,[UnitPrice]
,[RecommendedRetailPrice]
,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
LEFT JOIN [Purchasing].[Suppliers] s
ON i.SupplierID = s.SupplierID
WHERE StockItemName like '%USB%';
SELECT
[StockItemID]
,[StockItemName]
,[SupplierName]
,[Brand]
,[Size]
,[LeadTimeDays]
,[QuantityPerOuter]
,[IsChillerStock]
,[TaxRate]
,[UnitPrice]
,[RecommendedRetailPrice]
,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
LEFT JOIN [Purchasing].[Suppliers] s
ON i.SupplierID = s.SupplierID
WHERE StockItemID = 200;
SET STATISTICS IO OFF
Wir sehen in diesem Fall, dass die erste Abfrage einen Scan durchführt mit 42 bzw. 16 gelesenen Seiten auf unseren Tabellen, während die zweite Abfrage einen Seek durchführt mit jeweils zwei gelesenen Seiten.
Für die Details zu Seeks, Scans und Ausführungsplänen, verweisen wir wie gesagt auf sehr viele sehr gute Vorträge von sehr guten Shogun sowie deren Bücher. Es gibt hier so unendlich viel zu lernen, auch für Ninjas wie uns.
Referenzen
- Dokumentation von Microsoft zu SHOWPLAN_ALL
- Dokumentation von Microsoft zu SHOWPLAN_XML
- Dokumentation von Microsoft zu SHOWPLAN_TEXT
