Wie man mit TOP den SQL Server lahmlegt

Eine „häufig“ genutzte Funktionalität des TSQL Befehlssatzes ist das TOP Statement. Es schränkt das Ergebnis Resultset auf eine vorgegebene Anzahl an Zeilen ein. Sinnvoll ist das ganze wenn man mehre gleichwertige Treffer in einer Ergebnissmenge hat, und nur einen Teil davon braucht.

Beispiel: von 1000 Läufern werden nur die selektiert, die die Strecke in einer vorgegebene Zeit zurück gelegt haben, sortiert nach der benötigten zeit aufsteigend. Aufs Treppchen kommen jedoch nur drei Läufer.

Der TSQL – Select dazu:

SELECT TOP 3 t.nachname,t.vorname,t.zeit
  FROM teilnehmer t
  JOIN rennen r on (t.rennen = r.rennen)
  JOIN strecke s on (r.strecke = s.strecke)
 WHERE t.Zeit < s.ZeitMax
   AND r.Veranstalltung = @veranstaltung
 ORDER BY t.ZEIT asc

Die landläufige "Halbwahrheit" dazu ist, dass TOP X nicht in den Execution Plan eingreift. Folglich der Select normal ausgeführt wird und nur die Ergebnis-Menge auf die ersten X Reihen beschränkt wird. Das ist gefährliches Halbwissen! Hier sind zwei Bilder die "eindrucksvoll" belegen, dass man sich mit einem TOP den ganzen Server lahmlegen kann.

Dem geübten SQL-Auge dürfte sofort (wenn man die Bilder in groß betrachtet) auffallen das ohne TOP Hash-Match-Joins genutzt werden, wohingegen bei dem TOP 100 Nested-Loop-Joins genutzt werden. Was man im Bild nur erahnen kann, ist der Umstand, das mit dieser Konstellation jeder MS-SQL Server in die Knie zu bekommen ist.

Nested-Loops skalieren ganz beschissen, nämlich linear. Was kein Problem ist, wenn die zu bearbeitende Treffermenge klein ist. Hier ist der Problem begraben. Dem TOP entnimmt der QueryOptimizer eine Information, eine Vorhersage wie viele Treffer zu erwarten sind. Der Select wird nicht ausgeführt und dann die Treffermenge beschnitten, sondern der Select wird solange ausgeführt bis die gewünschte Treffermenge erreicht ist oder der Select keine Ergebnisse mehr liefert.

Normalerweise ist dieses "Denken" auch richtig. Wenn die Treffermenge normalerweise 10k Trefferr enthält und darauf ein TOP 1 angewendet wird ist es Schwachsinnig erst die 10K Treffer zu ermitteln und dann 9999 davon weg zu schmeißen.

Umgekehrt kann man sagen, das für einen Treffer selten ein kompletter Full-Table-Scann bei allen beteiligten Tabellen benötigt wird. So wird je nach "Where"-Bedingung aus der Lead-Tabelle der erste Datensatz ermittelt und so lange an die Folgeoperationen durch gereicht, bis er in der Treffermenge landet oder Aufgrund einer Bedingung ausscheidet. Das wird solange wiederholt bis de gewünschte Trefferzahl erreicht wurde. Im Beispiel benötigte es im "Gutfall" für einen Treffer 70 "Reads" aus der Lead-Tabelle (die Tabelle ganz rechts).

Was passiert aber, wenn Aufgrund einer Sortierung die komplette Lead-Tabelle gelesen werden muss? Das bekommt der Optimizer mit und wechselt sofort auf Hash-Match-Joins. Leider erkennt der Optimizer nicht, dass es auch andere Gründe geben kann, warum die Treffermenge rapide hochgehen kann. Der ungünstigste Fall ist folgender:
Die letzte Operation vor dem "TOP"-Operator führt einen Filter aus, der alle gelieferten Datensätze ausschließt. So müssen alle vorherigen Tabellen komplett gescannt werden und alle ermittelten Datensätze durch die Nested-Loops Operatoren bearbeitet werden. Bei Tabellen mit 10k und mehr Datensätzen sprengt das jeglichen verfügbaren Speicher. Der Server geht augenblicklich in die Knie. Wenn man Glück hat, ist die Instanz in CPU und Speicherverbrauch eingeschränkt, so dass die anderen Instanzen unbeeinflusst bleiben. Wenn nicht, dürfte man seinem Kunden gegenüber in Erklärungsnot geraten...

Die Gegenmaßnahmen sind recht einfach:

• Ausschließen das es zu solchen Extrem-Situationen kommt, wo ein Datensatz erst den ganzen ExecutionPlan durchlaufen muss um dann ausgeschlossen zu werden.
• Keep it Simple - Selects mit Top sollten ohne große Where-Bedingung auskommen.
• Dem QueryOptimizer Hilfestellung über query-Hints geben.