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