an Profis: TOP 1 schießt Server ab

verwendetes Datenbanksystem: SQL 2005 alle aktuellen Patches (spielt aber keine rolle, da es auf fast allen sql 2005 instanzen nachzustellen ist)

Ich verspreche gleich am Anfang, dass ich der reißererischen Überschrifft Fakten folgen lasse, aber erstmal eine Umgebungsbeschreibung.

Es geht um einen simplen select in der folgenden Form

  SELECT TOP 1 [someatributes]
     FROM [a]
     INNER JOIN [b] ON (b.foreign = a.primary)
     INNER JOIN [c] ON (c.foreign = b.primary)
     INNER JOIN [d] ON (d.foreign = c.primary)
   WHERE [some constraints)

man kann diskutieren ob ein Join über so "viele" tabellen sinnvoll ist, aber der dahinter leigende physikalische ablauf wurde uns vorgegeben und steht seitens des kunden nicht zur debatte.

von der treffermenge her kann man sagen dass C und D die meisten "Treffer" haben (>10000) und über a und b auf wenige (<100) reduziert werden.
Sowohl im SELECT als auch in der WHERE-Clause werden nicht indizierte werte abgefragt.

In der WHERE-Clause erfolgt der zusätzlich ein aufruf einer Scalar-Funktion die im prinzip folgendes macht:

SELECT x,y,z FROM [d] WHERE primary = [somevalue]
IF (x = [] AND y=[] AND z=[]) return true
ELSE return false

Die Randbedingunen:

Dieser select läuft in einer Produktiven anlage mit massig daten in 80% der fälle in unter 100ms durch... in 5% der fälle braucht es 5-10 sekunden in den restlichen 15 bricht der Server mit Timeout ab...

Man kann sich vorstellen das wir (das team in dem ich arbeite) doch einige zeit an diesem Rätsel zu knuppern hatten, denn dieser Select war lange zeit nicht auf unserer "Zielliste"

Gestern hatten wir nun das glück eine Dump der anlage abzuziehen auf der dieser Select Probleme macht. Die Testdatenbank läuft isoliert und ich war nur mit dem Management Studio drauf.

Das Problem:
In der Where-Clause befindet sich unter anderem folgender Ausdruck

  d.Attribute=[NUMBER]

für number setzte ich im Test 6 Werten (Numerische Zahlen) ein (U V W X Y Z). Im Falle von U-Y lieferte der Select innerhalb 10-50ms ein Ergebniss. Bei Z brach der Server nach rund 30 Minuten und auf 20GB hochgeschossener TempDB mit der Meldung ab, er habe nicht mehr genügend Platz um die Anfrage auszuführen....

im ersten schritt entfernte ich die Skalar-Funktion. Mit durchschlagendem Erfolg: der Select lief in ca 10 sek durch... nun sind 10 Sek gegenüber 30 Min zwar wahnsinn, aber immer noch um den Faktor 1000 langsamer als im "normalfall"

ein blick auf den exekution-plan brachte auch schnell licht ins dunkel.

Führ mal alle 6 Test nochmal aus (ohne skalar-funktion) sieht man, dass im falle U-Y das DB-System getreu unserer erwartung erst die Tabellen mit den wenigsten Treffern "joint" (a und b) und dann auf die "großen" Treffermengen losgeht (c und d). Anschließend führt er die komplexeren Filter aus.

Bei Z wurde ich stuzig: hier geht er den weg genau anders rum. Es wird zuerst D und c im Cross gejoint und erst dann versucht zu filtern... Der joinerfolgt dabi auch im NestedLoop (wie bei U-Y auch), das spiel setzt sich dann mit B und zum schluss A fort. Erst ganz am schluss wird die Treffermenge dann erheblich reduziert... Das der Select dann "ewig" braucht ist einleutend.

Es erklärt aber immer noch nicht warum die Skalar-Funktion den Server abschießt...

also das Skalar wieder rein und das Top mal raus... um zu sehen was die selects denn für gesamt Treffermengen liefern. Plötzlich liefern alle selects binnen 10ms Treffer im berech 50-100 Rows....

schaut man sich den ExecutionPlan an, sieht er jetzt auch noch ein ticken besser aus. Stat vorher 5 NestedLoops und unterscheidlicher Abfragereinfolge werden nun nur noch 3 HashMaps gebildet und das unabhängig von U-Z...

man kann mittels Select options auch beim TOP1 hash - Joins erzwingen... und so den Befehl immer "optimal" ausführen lassen

option (optimize for (@attribute = Value)/*, keepfixed plan*/)
option (hash join)
option (merge join)
option (loop join)

SELECT TOP 1 [someatributes]
     FROM [a]
     INNER JOIN [b] ON (b.foreign = a.primary)
     INNER JOIN [c] ON (c.foreign = b.primary)
     INNER JOIN [d] ON (d.foreign = c.primary)
   WHERE [some constraints)
  OPTION (hash join)

was mich nun aber verwundert, was macht TOP 1 das es den normalen ExecutionPlan so zerschießt, dass das DB-System nur noch Cross-Joins durchführt?

wie es scheint sind wir nicht die einzigen mit dem problem eine kleine Google suche fand ähnliche Probleme, aber selsten antworten auf das warum...

Hallo Raptor 2101,

hast Du schon mal den Indexoptimierungs-Assistenten laufen lassen?
Häufig sind solche Probleme auf falsche oder fehlende Indizes oder Statistiken zurückzuführen. Besonders korrekte Statistiken könnten dem SQL Server helfen, einen besseren Ausführungsplan zu erstellen.

Bevor man mit optimize options hantiert, sollte man den Optimierungsassistenten laufen lassen. Also Profiler anwerfen, ein Trace erzeugen und dieses dann vom Optimierungsassistenten durchnudeln lassen. Wenn der keine Lösung finden, dann nochmal hier posten.

Danke für den Tipp:

Statistiken werden Täglich rebuildet, was aber nicht viel bringt, da sich die datenbestände ständig und schnell ändern.

das mit dem Indexoptimierungs-Assistenten haben wir schon ausprobiert, dann indezieren wir bis zu 12 attribute (zzusaätzlich zu den keys), das würde das problem beheben, aber performant ist das ganze dann nicht mehr...

...das würde das problem beheben, aber performant ist das ganze dann nicht mehr...

Warum?

Indizes beschleunigen für gewöhnlich den Zugriff, es sei denn die Daten werden öfter geändert, als gelesen (was in den meisten Fällen nicht zutrifft).

Bei diesem "Geschäfftsprozess" leider schon, fast jedes lesen führt kurz darauf zu einer veränderung