MS SQL Server 2005 - Gespeicherte Prozedur "Spontan bricht"

8

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 ...

  • Der SP hörte plötzlich auf zu arbeiten (er lief einfach weiter und kehrte nie zurück)
  • Wir haben den SP auf subtile Weise verändert und es hat wieder funktioniert.
  • Ein paar Tage später hörte es wieder auf zu arbeiten
  • Jemand sagte dann "wir haben das schon einmal gesehen, kompilieren Sie einfach den SP"
  • Ohne den Code zu ändern, haben wir den SP kompiliert, und es funktionierte
  • Ein paar Tage später hörte es wieder auf zu arbeiten


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 ...


  • Hat jemand das schon mal erlebt?
  • Hat jemand irgendwelche Vorschläge, wie man es überwinden kann?


Prost,
Dems.


BEARBEITEN:

Der pseduo-Code wäre wie folgt:

  • lies "a" von table_x
  • lies "b" von table_x
  • Wenn (a & lt; b) zurückgeben
  • BEGIN TRANSACTION
  • LÖSCHEN Sie table_y
  • INSERT INTO table_y & lt; 3 wählt unioned zusammen & gt;
  • aus
  • UPDATE tabelle_x
  • COMMIT TRANSACTION

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.

    
MatBailie 18.06.2009, 21:19
quelle

8 Antworten

1

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.

    
Ed Harper 19.06.2009, 07:33
quelle
3

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.

    
RBarryYoung 18.06.2009 21:37
quelle
3

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.

    
Cade Roux 18.06.2009 21:50
quelle
0

Offensichtlich ändert das Ändern der gespeicherten Prozedur (durch Neukompilieren) die Umstände, die zu der Sperre führten.

Versuchen Sie, den Fortschritt Ihres SP wie hier hier oder hier .

    
devio 18.06.2009 21:59
quelle
0

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?

    
Paddy 19.06.2009 07:41
quelle
0

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.

    
Mitch Wheat 19.06.2009 07:44
quelle
0

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.

    
Jeff Meatball Yang 20.06.2009 18:16
quelle
0

Wenn Sie diese Schritte ausführen:

%Vor%

Vielleicht möchten Sie dies stattdessen versuchen

%Vor%     
Raj 19.06.2009 02:41
quelle