Grundsätzlich gilt, dass Query Hints nur als letzter Ausweg verwendet werden sollen und man SQL Server die Informationen über die zu Grunde liegenden Daten liefern soll, sodass er in der Lage ist, selbst einen optimalen Ausführungsplan zu finden.
Das in diesem Artikel beschriebene Problem ist auf einem älteren Kundensystem mit SQL Server 2008R2 aufgetreten.
Als Beispiel nehme ich wie gewohnt eine Adventure Works DWH Datenbank von Codeplex, die ich mit dem Skript von Bob Duffy auf eine realistische Größe vergrößert habe.
Folgende Abfrage liefert die Umsätze aggregiert nach Ländern und Promotionen zurück.
SELECT dst.SalesTerritoryCountry ,dp.EnglishPromotionName ,SUM(fis.SalesAmount) AS SalesAmount FROM dbo.DimPromotion dp INNER JOIN dbo.DimSalesTerritory dst INNER JOIN dbo.FactInternetSalesBig fis ON fis.SalesTerritoryKey = dst.SalesTerritoryKey ON fis.PromotionKey = dp.PromotionKey GROUP BY dst.SalesTerritoryCountry, dp.EnglishPromotionName;
Für den gesamten Zeitraum, ohne jegliche Einschränkung, wird die Abfrage mit folgendem Ausführungsplan ausgeführt:
Dieser führt dann zu folgenden IO-Aktivitäten:
Table ‚DimSalesTerritory‘. Scan count 1, logical reads 2
Table ‚Worktable‘. Scan count 0, logical reads 0
Table ‚FactInternetSalesBig‘. Scan count 41, logical reads 230944
Table ‚Worktable‘. Scan count 0, logical reads 0
Table ‚DimPromotion‘. Scan count 0, logical reads 46
Im Großen und Ganzen ein üblicher Ausführungsplan für ein Sternschema: Parallelität, Bitmap-Indexes und Hash Match-Operatoren für Joins sind alle vorhanden.
Jetzt simulieren wir den Fall von nicht aktuellen Statistiken dadurch, dass wir für einen noch nicht vorhandenen Tag (OrderDateKey=20080801) 200K neue Datensätze einfügen. Das triggert noch kein automatisches Aktualisieren von Statistiken, da der Schwellenwert 500 Zeilen+20% der aktuellen Sätze nicht erreicht wird.
Der Ausführungsplan der Abfrage mit dem Filter auf OrderDateKey=20080801 sieht jetzt ganz anders aus:
SELECT dst.SalesTerritoryCountry ,dp.EnglishPromotionName ,SUM(fis.SalesAmount) AS SalesAmount FROM dbo.DimPromotion dp INNER JOIN dbo.DimSalesTerritory dst INNER JOIN dbo.FactInternetSalesBig fis ON fis.SalesTerritoryKey = dst.SalesTerritoryKey ON fis.PromotionKey = dp.PromotionKey WHERE fis.OrderDateKey=20080801 GROUP BY dst.SalesTerritoryCountry, dp.EnglishPromotionName
Keine Bitmap-Indexes und Zwischenaggregation mehr und aus dem ersten Hash Match (Join der Faktentabelle mit der DimPromotion) ist ein Nested Loops Operator geworden. Der Grund dafür ist selbstverständlich eine viel zu niedrige Kardinalitätsschätzung für die relevanten Datensätze der Faktentabelle (geschätzte Anzahl der Datensätze – 1, tatsächlich – 200000).
Die Auswirkung auf den IO-Zugriff ist nicht zu übersehen:
Table ‚FactInternetSalesBig‘. Scan count 41, logical reads 232835
Table ‚DimSalesTerritory‘. Scan count 0, logical reads 400000
Table ‚DimPromotion‘. Scan count 0, logical reads 400000
Table ‚Worktable‘. Scan count 0, logical reads 0
Idealerweise würde man in diesem Fall die Statistiken aktualisieren, aber nehmen wir an, dass man sich stattdessen für ein explizites Hash Join Hint entschieden hat:
SELECT dst.SalesTerritoryCountry ,dp.EnglishPromotionName ,SUM(fis.SalesAmount) AS SalesAmount FROM dbo.DimPromotion dp INNER HASH JOIN dbo.DimSalesTerritory dst INNER HASH JOIN dbo.FactInternetSalesBig fis ON fis.SalesTerritoryKey = dst.SalesTerritoryKey ON fis.PromotionKey = dp.PromotionKey WHERE fis.OrderDateKey=20080801 GROUP BY dst.SalesTerritoryCountry, dp.EnglishPromotionName
Auf den ersten Blick sieht das Ergebnis einigermaßen gut aus:
Table ‚DimPromotion‘. Scan count 1, logical reads 3
Table ‚DimSalesTerritory‘. Scan count 1, logical reads 2
Table ‚Worktable‘. Scan count 0, logical reads 0
Table ‚FactInternetSalesBig‘. Scan count 41, logical reads 232835
Table ‚Worktable‘. Scan count 0, logical reads 0
Doch was passiert, wenn wir uns nur für die Umsätze in Deutschland interessieren (dst.SalesTerritoryKey=8)? Mit dem zusätzlichen Filterprädikat verweigert der SQL Server die Ausführung der Abfrage mit dem Hash Join Hint:
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Die etwas kryptische Fehlermeldung hat eine einfache Erklärung. SQL Server versucht die Filterprädikate in dem Ausführungsplan möglichst weit nach unten zu schieben. In den meisten Fällen ist es eine sehr sinnvolle Performanzoptimierung, da nicht relevante Datensätze möglichst früh eliminiert werden. In diesem Fall führt die Optimierung aber dazu, dass das Filterprädikat in den Clustered Index Scan (DimSalesTerritory) integriert wird und der folgende (erzwungene) Hash Join-Operator keine Werte für die Hashoperation hat (die Spalte SalesTerritoryKey wird aus dem Datenfluss komplett eliminiert).
In diesem kleinen Beispiel wäre es möglich, den Join mit DimSalesTerritory komplett zu entfernen und stattdessen auf fis.SalesTerritoryKey=8 zu filtern. Das ist aber nicht immer machbar, wenn Join Hints in Views/Table Valued Functions „versteckt“ sind.
Die aktuelleren Versionen (SQL Server 2014/2016) sind übrigens nicht so leicht aus dem Tritt zu bringen. Das Filterprädikat wird immer noch weiter unten, in dem Clustered Index Scan Operator platziert, aber die für das Hash Join notwendige Spalte SalesTerritoryKey wird nicht aus dem Datenfluss entfernt und die Abfrage wird erfolgreich ausgeführt.
Eine weitere wenig bekannte Eigenschaft von Join Hints ist, dass sie auch die Join Reihenfolge festlegen (wie ein explizites FORCE ORDER Hint).
Wenn wir die Tabellenreihenfolge in der Abfrage mit Hints ändern,
SELECT dst.SalesTerritoryCountry ,dp.EnglishPromotionName ,SUM(fis.SalesAmount) AS SalesAmount FROM dbo.DimPromotion dp INNER HASH JOIN dbo.FactInternetSalesBig fis INNER HASH JOIN dbo.DimSalesTerritory dst ON fis.SalesTerritoryKey = dst.SalesTerritoryKey ON fis.PromotionKey = dp.PromotionKey WHERE OrderDateKey=20080801 GROUP BY dst.SalesTerritoryCountry, dp.EnglishPromotionName
wird sie einen anderen Ausführungsplan erzeugen
Dieser macht allerdings viel weniger Sinn als die ursprüngliche Fassung (Hash Build auf kleinen Dimensionstabellen, Probing auf der großen Faktentabelle ist CPU- und RAM-technisch eine viel effizientere Lösung).
Auch hier sind die aktuellen SQL Server Versionen benutzerfreundlicher und produzieren eine explizite Warnung:
Warning: The join order has been enforced because a local join hint is used.
Fazit:
Query Hints nehmen dem SQL Server die Flexibilität, eine deklarativ definierte Abfrage optimal auszuführen und bergen manchmal auch andere nicht offensichtliche Nebeneffekte. Manchmal ist ein Query Hint tatsächlich die einzige Lösung, vorher sollte man aber weniger invasive Möglichkeiten ausschließen.
Links:
http://blogs.msdn.com/b/craigfr/archive/2009/04/28/implied-predicates-and-query-hints.aspx
Sie sehen gerade einen Platzhalterinhalt von Facebook. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von Instagram. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von X. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr Informationen