| 

.NET C# Java Javascript Exception

4
Hi zusammen,
ich habe foldendes Problem. In meiner DB existieren mehrere Tablevaluedfunctions. Innerhalb einer Funktion werden mehrere CTEs erstellt, eine Abfrage die diese CTEs in eine Tablevariable schreibt, und diese Tablevariable zurück gibt.

Das Problem ist nun, dass sich zu einem bisher unbekannten Zeitpunkt die Abfragezeit auf diese TVF extrem erhöht (von 0 auf 15 sekunden). Wenn ich dann den Code der Function direkt ausführe, bekomme ich das Ergebnis in 0 Sekunden. Erstelle ich die Function neu (einfach ein Alter Function mit selbem Code) ist sie wieder performant, also bei 0 Sekunden. Daraus schließe ich, dass der SQL-Server beim erstellen der TVF einen Abfrageplan festlegt der sich im Laufe der Zeit immer weniger gut eignet.

Ich habe bereits einen Wartungsjob eingerichtet der jeden Tag alle TVFs neu erstellt (morgens um 04:00 Uhr). Nach der neu Erstellung sind bis zum Performanceeinbruch ca. 600 Datensätze in der zentralen Tabelle der TVF manipuliert\hinzugefügt\entfernt worden. Meiner Meinung nach sollte sich das nicht allzu stark auf den Queryplan auswirken...

Das ganze spielt sich innerhalb eines MSSQL-Server 2008 R2 Standard Edition ab.

Danke!
26.11.2013
Sweatdiver 126 1 6
1 Antwort
3
Hallo Sweatdiver,

das Problem ist folgendes. Beim Anlegen der TFV, löschen des Procedure-Caches bzw. beim Aufruf von spRecompile wird ein optimaler Ausführungsplan ohne Berücksichtigung der Parameter erstellt (also aus deiner Sicht ein guter Ausführungsplan). Etwa aller 500 Datenmanipulationen (bei größeren Tabellen kann der Wert auch höher sein) werden die Statistiken neu berechnet werden. Der SQL-Server berechnet nun, basierend auf den Statistiken einen neuen Ausführungsplan wobei er diesesmal die Parameter mit berücksichtigt. Der Ausführungsplan ist nun nicht mehr generisch sondern speziell auf die Parameterkombination und Werte zugeschnitten und somit aus deiner Sicht ineffizens. Durch neues erzeugen der TFV, löschen des Procedure-Caches bzw. beim Aufruf von spRecompile beginnt das ganze wieder von vorn.

Beim direkten aufruf des TFV-Codes wird wiederum ein neuer Ausführungsplan für dieses Statement erstellt, der optiomal auf die ("Parameter")-konstellation zugeschnitten ist.

Dein Ansatz die TFV nachts neu zu erzeugen ist schonmal garnicht schlecht (machen wir auch so, aber aus anderen Gründen).

Option (keepfixed plan)

Zwingt den Abfrageoptimierer, die Abfrage aufgrund von Änderungen in den Statistiken nicht erneut zu kompilieren. Durch Angeben von KEEPFIXED PLAN wird sichergestellt, dass eine Abfrage nur dann erneut kompiliert wird, wenn das Schema der zugrunde liegenden Tabellen geändert wird oder für diese Tabellen sp_recompile ausgeführt wird.

Das sollte genau das sein was du brauchst und den solltest du an jedes Statement schreiben was auf die Table-Variable zugreift.

insert into T ... option(keepfixed plan);

update T set .... option(keepfixed plan);

delete from T ... option(keepfixed plan);

select * from T option(keepfixed plan);


Nachteil an dem Vorgegehn ist, das der Ausführungsplan sich nie ändern würde. Auf die Dauer gesehen kann das schlecht sein, weil der Ausführungsplan nicht mehr zu den statisiken passt.

OPTIMIZE FOR UNKNOWN

Weist den Abfrageoptimierer an, beim Kompilieren und Optimieren der Abfrage für alle lokalen Variablen, einschließlich der Parameter, die mit erzwungener Parametrisierung erstellt werden, statistische Daten statt der Anfangswerte zu verwenden.


Das wäre die bessere alternative.

insert into T ... option(OPTIMIZE FOR UNKNOWN);

update T set .... option(OPTIMIZE FOR UNKNOWN);

delete from T ... option(OPTIMIZE FOR UNKNOWN);

select * from T option(OPTIMIZE FOR UNKNOWN);


Eventuell ist es auch notwendig die Aufrufe der TVF mit diesem Query-Hint zu versehen. Das kann ich leider nicht beurteilen weil ich die genaue Struktur deiner Statments und wo genau das Problem aufritt nicht kennen.

Ich hoffe ich konnte dir helfen. Bitte halte mich auf dem laufenden ob das dein Problem beseitigen konnte.

Nachtrag 2: Kurz noch ein Hinweis: "Parameter-Sniffing" nennt sich das ganze.
26.11.2013
Floyd 14,6k 3 9
Floyd 14,6k 3 9
[Edit] Nachtrag "OPTIMIZE FOR UNKNOWN"
Floyd 26.11.2013
Hi, sehr aufschlussreich dein Beitrag! Danke schonmal. Da dieses Problem nur bei EINEM unserer Kunden auftritt, wird es jetzt ein wenig dauern um festzustellen ob das Problem gelöst wird. Wir müssen jetzt den Programmcode ändern, testen, Releasen und dann weiter sehen... trotzdem schonmal danke!
Sweatdiver 26.11.2013
Hi Sweatdiver, nur so aus neugier. Konntet ihr das Problem lösen?
Floyd 27.01.2014
1
@Floyd, ja, hat geklappt. Nachdem wir kleinere Schwierigkeiten mit unterschiedlichen MSSQL-Server-Versionen bei unseren Kunden lösen konnten, klappt das jetzt hervorragend. Danke nochmal!
Sweatdiver 12.04.2014

Stelle deine Mssql-Frage jetzt!