Ich verstehe, dass die Option WITH RECOMPILE den Optimierer zwingt, den Abfrageplan für gespeicherte Prozeduren neu zu erstellen, aber wann würden Sie wollen, dass dies geschieht?
Welche Faustregeln gibt es, wann man die Option WITH RECOMPILE verwenden sollte und wann nicht?
Wie hoch sind die effektiven Kosten, wenn man sie einfach auf jede Sprocketeinheit aufbringt?
Wie bereits gesagt wurde, sollten Sie nicht einfach die WITH RECOMPILE in jedem gespeicherten Proc aus Gewohnheit. Damit würden Sie einen der Hauptvorteile von Stored Procedures eliminieren: die Tatsache, dass der Abfrageplan gespeichert wird.
Warum ist das möglicherweise eine große Sache? Die Berechnung eines Abfrageplans ist viel intensiver als die Kompilierung von normalem prozeduralem Code. Denn die Syntax einer SQL-Anweisung gibt nur an was Sie wollen, und nicht (im Allgemeinen) wie Das ermöglicht der Datenbank ein hohes Maß an Flexibilität bei der Erstellung des physischen Plans (d. h. der Schritt-für-Schritt-Anweisungen für die tatsächliche Erfassung und Änderung von Daten). Es gibt viele "Tricks", die der Präprozessor für Datenbankabfragen anwenden und Entscheidungen treffen kann - in welcher Reihenfolge die Tabellen verbunden werden sollen, welche Indizes verwendet werden sollen, ob WHERE Klauseln vor oder nach Joins usw.
Bei einer einfachen SELECT-Anweisung macht das vielleicht keinen Unterschied, aber bei jeder nicht-trivialen Abfrage wird die Datenbank einige Zeit brauchen (gemessen in Millisekunden, im Gegensatz zu den üblichen Mikrosekunden), um einen optimalen Plan zu erstellen. Bei wirklich komplexen Abfragen kann sie nicht einmal eine optimal Plan zu erstellen, muss er lediglich Heuristiken verwenden, um einen ziemlich gut Plan. Indem Sie ihn also zwingen, jedes Mal neu zu kompilieren, teilen Sie ihm mit, dass er diesen Prozess immer wieder durchlaufen muss, selbst wenn der Plan, den er zuvor erhalten hat, vollkommen gut war.
Je nach Anbieter sollte es automatische Auslöser für die Neukompilierung von Abfrageplänen geben - wenn sich beispielsweise die Statistiken einer Tabelle erheblich ändern (z. B. wenn das Histogramm der Werte in einer bestimmten Spalte anfangs gleichmäßig verteilt ist und im Laufe der Zeit stark verzerrt wird), dann sollte die DB dies bemerken und den Plan neu kompilieren. Aber im Allgemeinen sind die Implementierer einer Datenbank in dieser Hinsicht schlauer als Sie selbst.
Wie bei allem, was mit Leistung zu tun hat, sollten Sie nicht im Dunkeln tappen; finden Sie heraus, wo die Engpässe sind, die 90 % Ihrer Leistung kosten, und lösen Sie sie zuerst.
Danke, Ian. Ich habe WITH RECOMPILE nicht verwendet. Bei meinem letzten Job(!?) war es in der Standard-Sproc-Vorlage enthalten, aber ich war mir nie ganz sicher, warum. Ich habe in letzter Zeit mehr Zeit mit der Optimierung verbracht und wollte mir diese Option einmal genauer ansehen. Vielen Dank für die vielen Details!
Ich würde es nicht als "perfekt gut" bezeichnen. Wenn die Abfrage in einer gespeicherten Prozedur enthalten ist und einen Parameter enthält, verwendet der SQL-Server Parameter-Sniffing, was dazu führen kann, dass ein "schlechter" Ausführungsplan zwischengespeichert wird (falls Sie das Parameter-Sniffing nicht deaktiviert haben).
Es ist KEINE gute Idee, sie in jede gespeicherte Prozedur einzubauen, denn das Kompilieren eines Abfrageplans ist ein relativ teurer Vorgang, und Sie werden keinen Nutzen daraus ziehen, dass die Abfragepläne zwischengespeichert und wiederverwendet werden.
Der Fall einer dynamischen Where-Klausel, die innerhalb einer gespeicherten Prozedur aufgebaut ist, kann mit sp_executesql um die TSQL auszuführen, anstatt die WITH RECOMPILE an die gespeicherte Prozedur.
Eine andere Lösung (ab SQL Server 2005) ist die Verwendung von hint mit spezifischen Parametern unter Verwendung der OPTIMIZE FOR tipp. Dies funktioniert gut, wenn die Werte in den Zeilen statisch sind.
SQL Server 2008 hat eine neue wenig bekannte Eigenschaft genannt " OPTIMIZE FOR UNKNOWN ":
Dieser Hinweis weist den Abfrageoptimierer an an, die Standardalgorithmen zu verwenden, die er Algorithmen zu verwenden, die er immer verwendet hat, wenn keine Parameterwerte an die Abfrage übergeben worden wären. In diesem Fall wird der Optimierer alle verfügbaren statistischen Daten, um um zu ermitteln, welche Werte der Werte der lokalen Variablen, die zur den Abfrageplan zu generieren, anstatt auf die spezifischen Parameterwerte, die von der Anwendung an die der Abfrage durch die Anwendung übergeben wurden.
Gute Informationen über OPTIMIZE FOR UNKNOWN. Ich würde jedoch alle Hinweise in dieselbe Kategorie wie WITH RECOMPILE einordnen. Für mich hat das immer bedeutet, dass ich eine schlechte Abfrage habe, die ich überarbeiten muss, bis ich beides nicht mehr brauche.
@dorfier: Hinweise haben ihre Berechtigung, und ja, sie sollten nur verwendet werden, wenn sie unbedingt erforderlich sind. Ich habe Abfragen gesehen, die gut geschrieben waren, aber unter "Parameter-Sniffing" litten, wodurch der falsche Abfrageplan zwischengespeichert wurde. Das lag an einer großen Anzahl von optionalen Parametern, die eine große Bandbreite von Werten annehmen.
Im Allgemeinen eine viel bessere Alternative zu WITH RECOMPILE es OPTION(RECOMPILE) wie Sie in der nachstehenden Erklärung sehen können, die aus die Antwort auf diese Frage hier
Wenn ein Problem mit der Parameter-Sensitivität auftritt, ist ein häufiger Ratschlag in Foren und auf Q&A-Seiten ist, "recompile" zu verwenden (unter der Annahme, dass die andere, bereits vorgestellte Tuning-Optionen ungeeignet sind). Leider, wird dieser Ratschlag oft fälschlicherweise dahingehend interpretiert, dass die Option WITH RECOMPILE Option zur gespeicherten Prozedur hinzuzufügen.
Die Verwendung von WITH RECOMPILE bringt uns effektiv zum SQL Server 2000 zurück, wo die gesamte gespeicherte Prozedur bei jeder Ausführung neu kompiliert wird. Ausführung neu kompiliert wird. Eine bessere Alternative auf SQL Server 2005 und höher ist den Abfragehinweis OPTION (RECOMPILE) nur für die Anweisung zu verwenden, die die unter dem Problem des Parameter-Sniffing leidet. Dieser Abfragehinweis führt führt nur zu einer Neukompilierung der problematischen Anweisung; die Ausführungspläne für andere Anweisungen innerhalb der gespeicherten Prozedur werden zwischengespeichert und normal weiterverwendet.
Die Verwendung von WITH RECOMPILE bedeutet auch, dass der kompilierte Plan für die gespeicherte Prozedur nicht zwischengespeichert wird. Infolgedessen werden keine Leistungsinformationen in DMVs wie sys.dm_exec_query_stats gepflegt. Die Verwendung des Abfrage hint bedeutet stattdessen, dass ein kompilierter Plan zwischengespeichert werden kann und Leistungsinformationen Leistungsinformationen in den DMVs verfügbar sind (obwohl sie auf die letzte Ausführung beschränkt, nur für die betroffene Anweisung).
Für Instanzen mit mindestens SQL Server 2008 Build 2746 (Service Pack 1 mit kumulativem Update 5), hat die Verwendung von OPTION (RECOMPILE) einen weiteren signifikanten Vorteil gegenüber WITH RECOMPILE: nur OPTION (RECOMPILE) ermöglicht die Optimierung der Parametereinbettung.
Sie sollte nur verwendet werden, wenn Tests mit repräsentativen Daten und Kontext zeigen, dass der Verzicht darauf zu ungültigen Abfrageplänen führt (was auch immer die möglichen Gründe sein mögen). Gehen Sie nicht von vornherein davon aus (ohne zu testen), dass ein SP nicht richtig optimieren wird.
Einzige Ausnahme für den manuellen Aufruf (d.h. nicht in den SP einprogrammieren): Wenn Sie wissen, dass Sie den Charakter der Zieltabellen wesentlich verändert haben, z.B. TRUNCATE, Bulk Loads, etc.
Dies ist eine weitere Gelegenheit zur vorzeitigen Optimierung.
Hinweis: Ich habe viele Punkte. Wenn ein Neuling die gleiche Antwort unten einreicht und Sie damit einverstanden sind, bewerten Sie die Antwort hoch.
@le dorfier: Da bin ich anderer Meinung. Es sollte nur verwendet werden, wenn es keine anderen Optionen gibt. Ich würde auf jeden Fall nicht empfehlen, sie während der Tests zu verwenden, da die Testdaten wahrscheinlich nicht repräsentativ für die Produktionsumgebung sind.
Dann war ich unklar und wir sind uns einig. Ich meinte, dass, wenn die Tests wiederholt anomale Optimierungen bei repräsentativen Daten zeigen, Sie sie möglicherweise einbauen müssen (und dann nur so lange, bis Sie herausgefunden haben, was die Anomalien verursacht). Es ist jetzt eine Frage im Spiel, bei der das der Fall ist.
CodeJaeger ist eine Gemeinschaft für Programmierer, die täglich Hilfe erhalten..
Wir haben viele Inhalte, und Sie können auch Ihre eigenen Fragen stellen oder die Fragen anderer Leute lösen.
