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.

Select ohne TOP
Select ohne TOP
Select Top 100
Select Top 100

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.

SqlDependencys feuern nicht, obwohl sie korrekt initialisiert sind.

Es kann vorkommen, dass eine SqlDependency nicht gefeuert wird obwohl sie „korrekt“ initialisiert wurde und es änderungen auf der Datenbank gibt. Der Grund ist, dass auf seiten des MsSql-Servers fehler auftrahten, die nicht zum Client weiter gereicht werden. Über Sinn oder Unsinn kann man sich trefflich streiten, beheben muss man den Fehler jedoch 😉

Häuffigste Ursache eines solchen Fehlers ist das Einspielen eines (Produktiv-) Backups  in die Testumgebung. Dabei wird der DB-Owner nicht auf den User gesetzt der das Backup einspielt (es gibt dafür keine Option) und es wird auch nicht geprüft ob der DB-Owner existiert. Ist letzteres nicht der Fall, wird beim Anlegen einer Dependency versucht auf selbigen zu wechseln… mit folgendem Resultat:

„Cannot execute as the database principal because the principal „dbo“ does not exist, this type of principal cannot be impersonated, or you do not have permission.“

Dieser äußerst unschöne Umstand lässt sich dann meistens im Windows-EventLog nachlesen. Leider gibt es keine anderen Hinweise. Ist der Fehler einmal erkannt, lässt er sich leicht durch folgendes Statement beheben

USE targetDB
GO
sp_changedbowner 'targetUser'

Quelle:
Support-Microsoft