OPTION (RECOMPILE) ist immer schneller; Warum?

Ich begegnete einer ungeraden Situation, wo das Anhängen von OPTION (RECOMPILE) an meine Abfrage bewirkt, dass es in einer halben Sekunde läuft, während es auslässt, dass die Abfrage gut über fünf Minuten dauert.

Dies ist der Fall, wenn die Abfrage aus Query Analyzer oder aus meinem C # Programm über SqlCommand.ExecuteReader() . Aufruf (oder nicht anrufen) DBCC FREEPROCCACHE oder DBCC dropcleanbuffers macht keinen Unterschied; OPTION (RECOMPILE) werden immer sofort mit OPTION (RECOMPILE) und mehr als fünf Minuten ohne sie zurückgegeben. Die Abfrage wird immer mit denselben Parametern aufgerufen [für diesen Test].

Ich verwende SQL server 2008.

Ich bin ziemlich bequem mit dem Schreiben von SQL, aber habe noch nie einen OPTION Befehl in einer Abfrage vor und war nicht vertraut mit dem ganzen Konzept der Plan-Caches bis das Scannen der Beiträge auf diesem Forum. Mein Verständnis von den Posts ist, dass OPTION (RECOMPILE) eine teure Operation ist. Es schafft anscheinend eine neue Lookup-Strategie für die Abfrage. Warum also ist es dann, dass nachfolgende Abfragen, die die OPTION (RECOMPILE) auslassen, so langsam sind? Sollten nicht die nachfolgenden Abfragen von der Lookup-Strategie Gebrauch machen, die bei der vorherigen Aufforderung berechnet wurde, die den Neukompilierungshinweis enthielt?

Ist es sehr ungewöhnlich, eine Abfrage zu haben, die einen Neukompilierungshinweis auf jeden einzelnen Anruf erfordert?

Sorry für die Einstiegsfrage Frage, aber ich kann nicht wirklich Köpfe oder Schwänze von diesem machen.

UPDATE: Ich wurde gebeten, die Abfrage zu posten …

 select acctNo,min(date) earliestDate from( select acctNo,tradeDate as date from datafeed_trans where feedid=@feedID and feedDate=@feedDate union select acctNo,feedDate as date from datafeed_money where feedid=@feedID and feedDate=@feedDate union select acctNo,feedDate as date from datafeed_jnl where feedid=@feedID and feedDate=@feedDate )t1 group by t1.acctNo OPTION(RECOMPILE) 

Wenn ich den Test von Query Analyzer ausführe, setze ich folgende Zeilen vor:

 declare @feedID int select @feedID=20 declare @feedDate datetime select @feedDate='1/2/2009' 

Beim Aufruf von meinem C # -Programm werden die Parameter über die SqlCommand.Parameters Eigenschaft übergeben.

Für die Zwecke dieser Diskussion können Sie davon ausgehen, dass sich die Parameter nie ändern, so dass wir ausschließen können sub-optimalen Parameter riechen als Ursache.

    Es gibt timeen, die mit OPTION(RECOMPILE) sinnvoll sind. In meiner Erfahrung ist das einzige Mal, dass dies eine praktikable Option ist, wenn Sie dynamische SQL verwenden. Bevor Sie erforschen, ob dies in Ihrer Situation Sinn macht, würde ich Ihnen empfehlen, Ihre Statistiken wieder aufzubuild. Dies geschieht durch Ausführen der folgenden:

     EXEC sp_updatestats 

    Und dann deinen Ausführungsplan neu zu erstellen. Dies wird sicherstellen, dass, wenn Ihr Ausführungsplan erstellt wird, wird es die neuesten Informationen verwenden.

    Durch Hinzufügen von OPTION(RECOMPILE) der Ausführungsplan jedes Mal neu erstellt, wenn Ihre Abfrage ausgeführt wird. Ich habe noch nie gehört, dass beschrieben, wie creates a new lookup strategy aber vielleicht sind wir nur mit verschiedenen Bedingungen für die gleiche Sache.

    Wenn eine gespeicherte Prozedur erstellt wird (ich vermute, dass Sie ad-hoc sql von .NET anrufen, aber wenn Sie eine parametrisierte Abfrage verwenden, dann endet dies ein gespeicherter Proc-Aufruf ) SQL server versucht, den effektivsten Ausführungsplan für diese Abfrage zu ermitteln basierend auf den data in Ihrer database und den eingegebenen Parametern ( Parameter Sniffing ), und dann Caches dieser Plan. Dies bedeutet, dass, wenn Sie die Abfrage erstellen, wo es 10 datasätze in Ihrer database gibt und dann ausführen, wenn es 100.000.000 datasätze der zwischengespeicherte Ausführungsplan nicht mehr am effektivsten ist.

    Zusammenfassend – ich sehe keinen Grund, dass OPTION(RECOMPILE) hier ein Vorteil wäre. Ich vermute, Sie müssen nur Ihre Statistiken und Ihren Ausführungsplan aktualisieren. Der Wiederaufbau von Statistiken kann ein wesentlicher Bestandteil der DBA-Arbeit sein, je nach Ihrer Situation. Wenn du nach dem Aktualisieren deiner Stats noch Probleme hast, würde ich vorschlagen, beide Ausführungspläne zu veröffentlichen.

    Und um deine Frage zu beantworten – ja, ich würde sagen, es ist sehr ungewöhnlich für deine beste Option, den Ausführungsplan jedes Mal neu zu kompilieren, wenn du die Abfrage ausführt.

    Oft, wenn es einen drastischen Unterschied von laufen, um eine Abfrage laufen, finde ich, dass es oft eine von 5 Fragen ist.

    1. STATISTIKEN – Statistiken sind veraltet. Eine database speichert Statistiken über den Bereich und die Verteilung der Arten von Werten in verschiedenen Spalten auf Tabellen und Indizes. Dies hilft der Abfrage-Engine, um einen "Plan" des Angriffs zu entwickeln, wie es die Abfrage ausführen wird, zum Beispiel die Art der Methode, die es verwendet, um Schlüssel zwischen Tabellen mit einem Hash oder Blick durch den gesamten Satz. Sie können Update Statistics auf der gesamten database oder nur bestimmte Tabellen oder Indizes aufrufen. Dies verlangsamt die Abfrage von einem Lauf zum anderen, denn wenn die Statistik nicht mehr aktuell ist, ist es wahrscheinlich, dass der Abfrageplan für die neu eingefügten oder geänderten data für die gleiche Abfrage nicht optimal ist (später noch näher erläutert). Es kann nicht richtig sein, Statistiken sofort auf einer Produktionsdatenbank zu aktualisieren, da es irgendwelche Overhead, langsamer und Verzögerung je nach der Menge der data zum Sample geben wird. Sie können auch einen vollständigen Scan oder Sampling verwenden, um Statistics zu aktualisieren. Wenn du den Abfrageplan betrachtest, kannst du dann auch die Statistiken über die verwendeten Indizes mit dem Befehl DBCC SHOW_STATISTICS (tablename, indexname) ansehen . Dies zeigt Ihnen die Verteilung und Bereiche der Schlüssel, die der Abfrageplan verwendet wird, um seinen Ansatz zu basieren.

    2. PARAMETER SNIFFING – Der Abfrageplan, der zwischengespeichert wird, ist nicht optimal für die einzelnen Parameter, die Sie übergeben, obwohl sich die Abfrage selbst nicht geändert hat. Wenn Sie z. B. einen Parameter übergeben, der nur 10 von 1.000.000 Zeilen abruft, dann kann der angeforderte Abfrageplan einen Hash Join verwenden. Wenn jedoch der Parameter, den Sie übergeben, 750.000 der 1.000.000 Zeilen verwendet, kann der Plan erstellt werden Index-Scan oder Tabellen-Scan. In einer solchen Situation können Sie der SQL-statement mitteilen, die Option OPTION (RECOMPILE) oder ein SP zu verwenden, um WITH RECOMPILE zu verwenden. Um die Engine zu sagen, ist dies ein "Single Use Plan" und kein Cached Plan zu verwenden, der wahrscheinlich nicht gilt. Es gibt keine Regel, wie man diese Entscheidung treffen kann, es hängt davon ab, wie die Abfrage von Benutzern genutzt wird.

    3. INDEXES – Es ist möglich, dass sich die Abfrage nicht geändert hat, aber eine Veränderung an anderer Stelle wie das Entfernen eines sehr nützlichen Index hat die Abfrage verlangsamt.

    4. ROWS CHANGED – Die Zeilen, die Sie abfragen, drastisch ändert sich von Call to Call. Normalerweise werden die Statistiken in diesen Fällen automatisch aktualisiert. Allerdings, wenn Sie dynamische SQL erstellen oder SQL in einer engen loop aufrufen, gibt es eine Möglichkeit, dass Sie einen veralteten Abfrageplan auf der falschen drastischen Anzahl von Zeilen oder Statistiken verwenden. In diesem Fall ist OPTION (RECOMPILE) nützlich.

    5. DIE LOGIK Die Logik, deine Abfrage ist nicht mehr effizient, es war gut für eine kleine Anzahl von Reihen, aber nicht mehr skaliert. Dies beinhaltet in der Regel eine genauere Analyse des Abfrageplans. Zum Beispiel können Sie nicht mehr Dinge in loser Schüttung, sondern müssen Chunk Dinge und kleinere Commits, oder Ihr Cross-Produkt war gut für einen kleineren Satz, aber jetzt nimmt CPU und memory, wie es Skalen größer, das kann auch wahr sein für Mit DISTINCT rufen Sie eine function für jede Zeile auf, Ihre Key-Matches verwenden keinen Index wegen der CASTING-Typ-Konvertierung oder NULLS oder functionen … Zu viele Möglichkeiten hier.

    Im Allgemeinen, wenn Sie eine Abfrage schreiben, sollten Sie einige geistige image von etwa, wie bestimmte data in Ihrem Tisch verteilt werden. Eine Spalte zum Beispiel kann eine gleichmäßig verteilte Anzahl von verschiedenen Werten haben, oder es kann schief sein, 80% der time haben einen bestimmten Satz von Werten, ob die Verteilung häufig über die time variiert oder ziemlich static ist. Dies gibt Ihnen eine bessere Vorstellung davon, wie man eine effiziente Abfrage aufbaut. Aber auch beim Debugging der Abfrageleistung haben wir eine Basis für den Aufbau einer Hypothese, warum es langsam oder ineffizient ist.

    Um die hervorragende list (gegeben durch @CodeCowboyOrg) von Situationen hinzuzufügen, in denen OPTION (RECOMPILE) sehr hilfreich sein kann,

    1. Tabellenvariablen . Wenn Sie Tabellenvariablen verwenden, gibt es keine vorgefertigten Statistiken für die Tabellenvariable, die oft zu großen Unterschieden zwischen geschätzten und tatsächlichen Zeilen im Abfrageplan führen. Die Verwendung von OPTION (RECOMPILE) bei Abfragen mit Tabellenvariablen ermöglicht die Erstellung eines Abfrageplans, der eine viel bessere Schätzung der betreffenden Zeilennummern aufweist. Ich hatte eine besonders kritische Verwendung einer Tischvariablen, die unbrauchbar war, und die ich aufgeben wollte, bis ich OPTION (RECOMPILE) hinzugefügt habe. Die Laufzeit verging von Stunden bis zu wenigen Minuten. Das ist wohl ungewöhnlich, aber auf jeden Fall, wenn man Tischvariablen benutzt und an der Optimierung arbeitet, lohnt es sich zu sehen, ob OPTION (RECOMPILE) einen Unterschied macht.

    Die ersten Aktionen vor dem Ausführen von Abfragen ist es, die Indizes und Statistiken zu defragmentieren / neu zu erstellen, andernfalls verschwenden Sie Ihre time.

    Sie müssen den Ausführungsplan überprüfen, um zu sehen, ob es stabil ist (ist das gleiche, wenn Sie die Parameter ändern), wenn nicht, müssen Sie einen Deckelindex erstellen (in diesem Fall für jede Tabelle) (wissendes System können Sie eine erstellen ist auch für andere Abfragen nützlich).

    als Beispiel: create index idx01_datafeed_trans Auf datafeed_trans (feedid, feedDate) INCLUDE (acctNo, tradeDate)

    Wenn der Plan stabil ist oder Sie ihn stabilisieren können, können Sie den Satz mit sp_executesql ('sql Satz') ausführen, um einen festen Ausführungsplan zu speichern und zu verwenden.

    Wenn der Plan instabil ist, müssen Sie eine Ad-hoc-statement oder EXEC ('sql Satz') verwenden, um jedes Mal einen Ausführungsplan auszuwerten und zu erstellen. (oder eine gespeicherte Prozedur "mit Rekompilierung").

    Ich hoffe es hilft.