Warum sollten Tabellenhinweise (table hints) vermieden werden?

Immer wieder sehe ich bei der Analyse von Anwendungen, dass mit sogenannten Tabellenhinweisen (table hints) gearbeitet wird. Meist ist die Begründung, dass der “doofe” SQL Server einfach nicht verstanden hat, dass es doch extra einen nicht-gruppierten Index für die Abfrage gibt. Den hat der doch einfach ignoriert! Und von anderen Entwicklungsumgebungen sei man ja schließlich auch gewöhnt die Verwendung von Indizes explizit anzugeben. Wo ist also das Problem?

Nun ja, das Problem ist, dass die Erfahrung gezeigt hat, dass wenn soweit alles in Ordnung ist, der SQL Server ziemlich genau weiß, was er da so macht bei der Aufbereitung von Abfragen. Und wenn er einen Index nicht nutzen möchte, dann hat er in der Regel einen verdammt guten Grund dafür. Dummerweise wird dieser nicht angeben.

Der häufigste Grund dafür, dass der SQL Server einen Index nicht verwendet, ist die Vermeidung von unnötiger Arbeit. Der SQL Server kann nämlich auf Basis der sogenannten Statistiken ziemlich genau abschätzen wie viele Datensätze er berühren muss für die Verarbeitung einer Abfrage. Daher ist es extrem wichtig, dass die Statistiken aktuell sind! Also nicht einfach in den Optionen für eine Datenbank deaktivieren, wenn Ihr nicht noch zumindest einen entsprechenden Job dafür aufgesetzt habt! Ist wirklich wichtig!

Was meine ich nun mit unnötiger Arbeit? Dazu ein einfaches Beispiel.

Wir nehmen als Datenbank mal nicht AdventureWorks, sondern die neue Contoso BI. Da ist ein klein wenig mehr Futter drin. Die Tabelle FactOnlineSales bringt es zumindest auf 12.627.608 Datensätze. Das ist zwar noch nicht wirklich viel, aber für unser Beispiel reichst auf jeden Fall. Auf “statistics io on” bin ich schon mal kurz eingegangen. Damit sind die IO-Operationen gemeint und nicht die Statistiken in den Indizes!

set statistics io on;

select SUM(SalesAmount)
from FactOnlineSales
where DateKey between '31.12.2007' and '01.01.2009';

Als Wert für die “logische Lesevorgänge” bekomme ich 46648. Den gilt es zu nun minimieren. Die Tabelle hat zurzeit nur einen gruppierten Index. Der scheint zwar auch alles andere als optimal gewählt zu sein, aber das ein anderes Thema. Im Ausführungsplan sehen wir, dass ein “Clustered Index Scan” durchgeführt wird.

Nun fügen wir einen nicht-gruppierten Index hinzu für DateKey. Das macht doch Sinn, schließlich suchen wir ja nach DateKey.

Und nun? Wir führen die Query nochmal aus und bekommen wieder ein ähnliches Ergebnis. Im Ausführungsplan ist immer noch ein “Clustered Index Scan”. Hier ist nun der Moment, wo der eine oder andere schwach wird und dem SQL Server nun den Index aufzwingen will.

select SUM(SalesAmount)
from FactOnlineSales with (index(ix_datekey)) 
where DateKey between '31.12.2007' and '01.01.2009';

So, jetzt aber! Im Ausführungsplan sehen wir nun endlich, dass der Index verwendet wird. Und was kommt raus? 11186313 logische Lesevorgänge!!! Ist mein SQL Server kaputt? :-)

Natürlich nicht! Wir sehen jetzt nur, warum der SQL Server den Index bisher vermieden hat. Der Index war nämlich alles andere als optimal für diese Abfrage. Zwar nutzt der SQL Server nun den Index, aber der eigentliche Wert, der SalesAmount, steckt ja noch im gruppierten Index. Daher fliegt unsere Abfrage nun immer eine schöne Schleife zwischen nicht-gruppierten und gruppierten Index. Das kostet uns so richtig Leseoperationen und damit Zeit. Wir nennen sowas eine Schlüsselsuche (bookmark lookup).

Das Coole daran ist, dass hat der SQL Server aufgrund der Statistiken kommen sehen und daher entschieden, dass ein simpler Scan auf dem gruppierten Index am Ende günstiger wird. Daher mein Hinweis, dass der SQL Server schon ziemlich genau weiß, warum er einen Index mal nimmt und mal nicht. Ehrlich!

Und unsere Query? Haben wir denn nun keine Chance diese mit einem Index zu unterstützen? Aber sicher doch!

Wir nehmen wieder unseren nicht-gruppierten Index und fügen als eingeschlossene Spalte den SalesAmount hinzu. Und los. Jetzt bekomme wir nur noch 10512 logische Lesevorgänge. Und ganz wichtig, im Ausführungsplan sehen wir nur noch einen Index Seek auf unserem nicht-gruppierten Index.

Da der SQL Server nun alle notwenigen Spalten in unserem Index findet, gibt es keine Notwendigkeit mehr für den Ausflug in den gruppierten Index. Das ist bewusst eingesetzt Redundanz, wobei wir um die Pflege nicht kümmern müssen. Ach ja, und wir brauchten dafür natürlich keinen Tabellenhinweis!

Indizes mit eingeschlossenen Spalten sind zwar äußerst nützlich, aber bitte übertreibt jetzt nicht die Verwendung! Die Pflege kostet im Hintergrund immer Zeit beim Schreiben.

Und letzte Worte zu Tabellenhinweisen: Ja, es gibt ganz, ganz wenige Sonderfälle in denen die Verwendung Sinn macht. Aber glaubt mir, die sind wirklich selten!!!