Ein Client hat beim Ausführen einer gespeicherten Prozedur wiederholt sehr seltsames Verhalten gemeldet.
Sie haben Code, der von einer zwischengespeicherten Umsetzung eines flüchtigen Datensatzes abläuft. Ein gespeicherter Prozess wurde geschrieben, um den Datensatz bei Bedarf neu zu verarbeiten, wenn:
1. Der Datensatz hat sich seit der letzten Wiederaufbereitung geändert
2. Der Datensatz wurde für 5 Minuten nicht geändert
(Die zweite Bedingung stoppt eine massive wiederholte Neuberechnung während Zeiten der Änderung.)
Dies funktionierte für ein paar Wochen gut, der SP brauchte 1-2 Sekunden, um die Neuverarbeitung abzuschließen, und es tat es nur, wenn es erforderlich war. Dann ...
Dies hat sich jetzt viele, viele Male wiederholt. Der SP hört plötzlich auf zu arbeiten, kehrt nie zurück und der Client verlässt die Zeit. (Wir haben versucht, es durch das Managementstudio zu führen und die Abfrage nach 15 Minuten abgebrochen.)
Aber jedes Mal, wenn wir den SP neu kompilieren, funktioniert es plötzlich wieder.
Ich habe WITH RECOMPILE noch nicht mit den entsprechenden EXEC-Anweisungen ausprobiert, aber das möchte ich nicht unbedingt. Es wird hundertmal pro Stunde aufgerufen und macht normalerweise nichts (es verarbeitet die Daten nur einige Male pro Tag). Wenn es möglich ist, möchte ich den Overhead der Neukompilierung eines relativ komplizierten SP vermeiden, "um etwas zu vermeiden, was" nicht passieren sollte ...
Prost,
Dems.
BEARBEITEN:
Der pseduo-Code wäre wie folgt:
Die Auswahlen sind "nicht hübsch", aber wenn sie in-line ausgeführt werden, werden sie in kürzester Zeit ausgeführt. Einschließlich, wenn der SP es ablehnt. Und der Profiler zeigt, dass es der INSERT ist, bei dem der SP "aussetzt"
Es gibt keine Parameter für den SP und sp_lock zeigt nichts an, was den Prozess blockiert.
Wie andere bereits gesagt haben, verursacht etwas über die Art und Weise, wie sich die Daten oder die Quellentabellenstatistiken ändern, dass der zwischengespeicherte Abfrageplan veraltet wird.
WITH RECOMPILE wird wahrscheinlich die schnellste Lösung sein - benutze SET STATISTICS TIME ON , um herauszufinden, was die Rekompilierungskosten tatsächlich sind, bevor du sie sofort wieder ablehnst.
Wenn das immer noch keine akzeptable Lösung ist, ist es wahrscheinlich die beste Option, die insert-Anweisung zu refaktorieren.
Sie sagen nicht, ob Sie UNION oder UNION ALL in Ihrer insert-Anweisung verwenden. Ich habe festgestellt, dass INSERT INTO mit UNION einige bizarre Abfragepläne erzeugt, insbesondere bei SQL 2005-Versionen vor SP2.
Rajs Vorschlag des Fallenlassens und
Erneutes Erstellen der Zieltabelle mit
SELECT INTO ist ein Weg zu gehen.
Sie könnten auch versuchen, jedes von
die drei Quellabfragen in ihre eigenen
temporäre Tabelle, dann UNION diese temporären Tabellen
zusammen in der Einfügung.
Alternativ können Sie auch eine
Kombination dieser Vorschläge -
setze die Ergebnisse der Union in a
temporäre Tabelle mit SELECT INTO ,
dann füge von diesem in das Ziel ein
Tabelle.
Ich habe gesehen, dass alle diese Ansätze Leistungsprobleme in ähnlichen Szenarien lösen. Tests zeigen, welches die besten Ergebnisse mit den Daten liefert, die Sie haben.
Dies ist die Grundfläche des Parameter-Sniffing. Ja, der erste Schritt ist, RECOMPILE auszuprobieren, obwohl es 2005 nicht immer so funktioniert, wie Sie es wollen.
Aktualisierung: Ich würde versuchen, Statement-Level auf der INSERT trotzdem neu zu kompilieren, da dies ein Statistikproblem sein könnte (ach ja, überprüfen Sie, ob die automatische Statistikaktualisierung aktiviert ist).
Wenn dies nicht zum Parameter-Sniffing passt, vergleichen Sie den tatsächlichen Abfrageplan mit dem Zeitpunkt, an dem er richtig funktioniert und ab dem Zeitpunkt, an dem er immer ausgeführt wird (verwenden Sie den geschätzten Plan, wenn Sie den tatsächlichen Abfrageplan nicht erhalten). Sie überprüfen, ob sich der Plan ändert oder nicht.
Ich stimme der Parameter-Sniffing-Diagnose vollkommen zu. Wenn Sie Eingabeparameter für den SP haben, die variieren (oder auch wenn sie nicht variieren) - stellen Sie sicher, dass Sie sie mit einer lokalen Variablen maskieren und die lokale Variable im SP verwenden.
Sie können auch WITH RECOMPILE verwenden, wenn sich die Menge ändert, aber der Abfrageplan nicht mehr gut ist.
In SQL Server 2008 können Sie das Feature OPTIMIZE FOR UNKNOWN verwenden.
Wenn Ihr Prozess das Auffüllen einer Tabelle und dann die Verwendung dieser Tabelle in einem anderen Vorgang umfasst, empfehle ich, den Prozess in separate SPs aufzuteilen und sie einzeln WITH RECOMPILE aufzurufen. Ich denke, die zu Beginn des Prozesses erstellten Pläne können manchmal sehr schlecht sein (so schlecht, dass sie nicht abgeschlossen werden), wenn Sie eine Tabelle auffüllen und dann die Ergebnisse dieser Tabelle verwenden, um eine Operation auszuführen. Denn zum Zeitpunkt des ursprünglichen Plans war die Tabelle viel anders als nach dem ersten Einfügen.
Ich stimme der obigen Antwort in einem Kommentar zu. Das klingt wie eine nicht geschlossene Transaktion, insbesondere wenn Sie die select-Anweisung noch immer aus dem Abfrageanalysator heraus ausführen können.
Klingt sehr ähnlich wie es eine offene Transaktion mit einem ausstehenden Lösch für table_y gibt und die Einfügung kann an dieser Stelle nicht passieren.
Wenn Ihr SP blockiert, können Sie eine Einfügung in table_y durchführen?
Haben Sie einen Indexwartungsjob?
Sind Ihre Statistiken aktuell? Eine Möglichkeit zu sagen ist, prüfen Sie die geschätzten und tatsächlichen Abfragepläne für große Variationen.
Wie andere schon gesagt haben, klingt dies sehr wahrscheinlich nach einer nicht festgeschriebenen Transaktion.
Meine beste Schätzung:
Sie sollten sicherstellen, dass table_y vollständig und schnell gelöscht werden kann.
Wenn es andere gespeicherte Prozeduren oder externe Codeabschnitte gibt, die jemals Transaktionen in dieser Tabelle enthalten, warten Sie vielleicht ewig. (Sie können Fehler aus und nie die Transaktion schließen)
Noch ein Hinweis: Versuchen Sie, falls möglich, truncate zu verwenden. Es verwendet weniger Ressourcen als ein Löschvorgang ohne where-Klausel:
%Vor%Sobald ein Fehler in Ihrer EIGENEN Transaktion auftritt, werden alle folgenden Aufrufe (scheinbar alle 5 Minuten) ebenfalls "hängen", es sei denn, Sie behandeln Ihren Fehler:
%Vor%Der allererste Fehler gibt Ihnen Informationen über den tatsächlichen Fehler. Es ist nur ein sekundärer Effekt, es in eigenen Tests zu sehen.
Tags und Links sql sql-server sql-server-2005 stored-procedures tsql