Die DBA-Version eines XKCD-Klassikers.

Wenn Sie feststellen, dass das Schnüffeln von Parametern ein Problem darstellt, müssen Sie testen, ob die Implementierung von ‚Recompile‘-Hinweisen zur Planqualität beiträgt. (Nicht sicher, was Parameter Sniffing ist? Erfahren Sie in diesem Blogbeitrag oder in diesem 30-minütigen kostenlosen Video.) Sie müssen entscheiden: Welchen Hinweis oder Befehl verwenden Sie und wo legen Sie ihn ab? Welche Kompromisse machen Sie, wenn es darum geht, Ihren SQL Server in Zukunft leistungsoptimieren zu können?

In diesem Beitrag werden allgemeine Optionen beschrieben, mit denen Sie SQL Server dazu bringen (oder schlagen) können, einen neuen Ausführungsplan zu generieren. Ich werde einige Vor- und Nachteile für jede Methode geben und erklären, was nützlich ist und was es wert ist, vermieden zu werden.Um die Dinge relativ einfach zu halten, diskutiere ich nur, wie dies heute für gespeicherte Prozeduren gilt – dieser Beitrag behandelt keine anderen Formen parametrisierter (oder nicht parametrisierter) Abfragen. Wenn Sie in diesen Bereichen große Fragen haben, können Sie dies für einen zukünftigen Beitrag in den Kommentaren vorschlagen.Haftungsausschluss: Hinweise zum erneuten Kompilieren können Ihre Leistung beeinträchtigen, indem Sie Ihre CPUs bei falscher Verwendung in Brand setzen. Mit Vorsicht behandeln!

Nützliche dynamische Verwaltungsansichten

Wenn ich über Auswirkungen auf den Ausführungsplancache spreche, beziehe ich mich auf zwei DMVs:

• sys.dm_exec_query_stats – Diese DMV ist hilfreich, um die wichtigsten Anweisungen auf Ihrem SQL Server anzuzeigen, unabhängig davon, ob sie Teil einer Prozedur sind oder nicht. Schauen Sie sich hier eine Beispielabfrage an.
• sys.dm_exec_procedure_stats – Diese DMV ist nur in SQL Server 2008 und höher verfügbar. Es verfolgt Ausführungsmetriken für gespeicherte Prozeduren. Schauen Sie sich eine Beispielabfrage in Books Online an.

Für beide DMVs ist ein Ausführungsplan im Cache mit der Anzeige von Ausführungsmetriken verbunden: Anzahl der gesamten Ausführungen, Gesamt- und durchschnittliche CPU, logische Lesevorgänge usw. Wenn ein Ausführungsplan aufgrund von Neukompilierung, Speicherdruck, Neustart oder anderen Aktionen aus dem Cache entfernt wird, werden auch die zugehörigen Ausführungsmetriken entfernt.

Ausführen einer Prozedur mit einem Rekompilierungshinweis zur Laufzeit

Eine Option, die ich für schnelles und einfaches Testen liebe, ist die Möglichkeit, eine gespeicherte Prozedur mit einem Rekompilierungshinweis zur Ausführungszeit aufzurufen. Dies ist großartig, da Sie keinen kompilierten Code ändern müssen. Ihr Hinweis gilt auch nur für das, was Sie zur Laufzeit tun.

Um dies zu tun, verwenden Sie einfach eine Syntax wie diese:

However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. Sie können weiterhin Parameter für Aufrufe von Unterprozeduren innerhalb der gespeicherten Prozedur selbst schnüffeln.

Also, während ich das manchmal zum Testen mag, wenn Sie irgendeine Verschachtelung in Ihren gespeicherten Prozeduren haben, kann es Ihnen nicht helfen, einen wirklich frischen Ausführungsplan zu bekommen.

Verwenden der gespeicherten Systemprozedur sp_recompile

Dies ist manchmal nützlich, wenn Sie während eines Produktionsvorfalls eingreifen müssen, wenn Sie einen schlechten Plan haben, der wiederholt von eingehenden Sitzungen verwendet wird. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):

Wenn dies ausgeführt wird, werden zugehörige Pläne nicht sofort aus dem Ausführungsplancache von SQL Server entfernt. Stattdessen geschieht die Magie, wenn das nächste Mal Abfragen ausgeführt werden, die auf das neu kompilierte Objekt verweisen. Zu diesem Zeitpunkt werden vorhandene Ausführungsstatistiken in sys.dm_exec_query_stats für Anweisungen im Plan zurückgesetzt.

Ausführungszahlen in sys.dm_exec_procedure_stats werden nicht unbedingt bei der nächsten Verwendung zurückgesetzt, wenn Sie eine Tabelle markieren, die von einer gespeicherten Prozedur zur Neukompilierung verwendet wird. Wenn Sie jedoch die gespeicherte Prozedur selbst für die Neukompilierung markieren, wird sie bei der nächsten Verwendung zurückgesetzt. (Versuchen Sie nicht, dies auswendig zu lernen, lesen Sie einfach den nächsten Absatz.)

Es gibt große Nachteile mit diesem. Dieser Befehl erfordert hohe Berechtigungen — der Benutzer, der ihn ausführt, benötigt ‚alter‘-Berechtigungen für die Tabelle. Es erfordert auch ein hohes Maß an Schloss zu vervollständigen. Auf ausgelasteten Systemen habe ich gesehen, dass dieser Befehl an einigen bösen Blockierungsketten teilgenommen hat. Sei vorsichtig damit!

Dies kann zwar gelegentlich zur Fehlerbehebung und zum Testen nützlich sein, sollte jedoch nicht Teil des Produktionscodes sein.

Hinweise in Kopfzeilen gespeicherter Prozeduren NEU KOMPILIEREN

Wenn Sie Prozeduren haben, mit denen Sie neue Pläne generieren möchten, beginnen Sie mit der Verwendung von Hinweisen innerhalb der Prozedur. Die Option, die die meisten Leute zuerst entdecken, besteht darin, ‚WITH RECOMPILE‘ im Header der gespeicherten Prozedur wie folgt zu verwenden:

Die Verwendung von RECOMPILE im Header der gespeicherten Prozedur ist ziemlich drastisch — die Prozedur speichert keinen Ausführungsplan zwischen, wenn sie ausgeführt wird. Dies bedeutet:

• Keine Ausführungspläne im Cache zum Überprüfen
• Keine Ausführungsstatistiken in sys.dm_exec_query_stats aufgezeichnet
• Keine Ausführungsstatistiken in sys.dm_exec_procedure_stats aufgezeichnet

Wow, das ist ein Mist. Wenn Sie die Auswirkungen dieser Prozedur auf Ihren Server identifizieren müssen, müssen Sie eine Art Trace- oder Extended Events-Sitzung ausführen und die Ergebnisse sammeln und interpretieren. Das geht nicht schnell und Laufspuren können die Leistung beeinträchtigen.

Ein bisschen negativ. Aus diesem Grund mag ich diese Wahl überhaupt nicht.

Hinweise zu einzelnen Anweisungen NEU KOMPILIEREN

Diese Option ist etwas aufwendiger, zahlt sich aber im Laufe der Zeit viel besser aus. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:

Auch wenn Sie den Hinweis für alle Anweisungen in der Prozedur und nicht im Header verwenden müssen, hat dies immer noch Vorteile! Indem Sie den Recompile-Hinweis auf Anweisungsebene in den proc einfügen, erhalten Sie auf magische Weise:

• Begrenzte Ausführungspläne im Cache zur Überprüfung (letzte Ausführung)
• Begrenzte Ausführungsstatistiken, die in sys.dm_exec_query_stats aufgezeichnet wurden. Sie erhalten nur Statistiken für die letzte Ausführung, aber die plan_generation_num Spalte wird erhöht, was Ihnen zumindest einen Einblick gibt, dass etwas nicht in Ordnung ist.
• In sys.dm_exec_procedure_stats aufgezeichnete Ausführungsstatistiken

Die Tatsache, dass Sie einige Informationen in diesen DMVs erhalten, kann im Laufe der Zeit sehr nützlich sein. Das Anwenden von Recompile-Hinweisen nur auf die Anweisungen, die sie benötigen, ist ebenfalls verantwortungsbewusster – es senkt das Risiko, sich im Laufe der Zeit mit CPU-Burn zu verbrennen.

DBCC FREEPROCCACHE – die nukleare Option

Dies ist nicht unbedingt ein Hinweis auf die Neukompilierung – aber es verursacht sicherlich eine Neukompilierung. SQL Server hat einen Befehl, den Sie verwenden können, um es zu sagen, „Neu beginnen mit frischen Ausführungsplänen“. Es wird normalerweise so verwendet:

This command makes everything start fresh– for every query:

• Die CPU–Auslastung kann steigen, wenn neue Pläne kompiliert werden
• Ausführungsstatistiken werden in sys.dm_exec_query_stats (sofort) gelöscht
• Ausführungsstatistiken werden in sys.dm_exec_procedure_stats (sofort) gelöscht

Dieser Befehl ist nicht böse – es gibt eine Zeit und einen Ort in der Fehlerbehebung, wenn Sie ihn verwenden, können Sie schnell zur Ursache eines Leistungsproblems gelangen. Da es sich jedoch auf den gesamten Cache auswirkt und sich auch auf die Leistung auswirken kann, müssen Sie bei der Verwendung sehr vorsichtig sein. Ich empfehle NICHT, diesen Befehl in regulären Jobs, automatisierten Prozessen oder Produktionscode zu verwenden.

Ab SQL Server 2008 können Sie diesen Befehl etwas sanfter verwenden – Sie können einen bestimmten Plan mit dem „Planhandle“ oder „sql Handle“ aus dem Cache entfernen – aber natürlich müssen Sie herausfinden, was das Handle ist und warum Sie es entfernen möchten. Dies könnte in einigen Nischensituationen nützlich sein, aber in der Praxis kommt es nicht viel auf. Sie können auch einen Ressourcengouverneur-Pool löschen, aber Sie müssten den Ressourcengouverneur verwenden.

Was bedeutet das alles?

Wenn Sie testen, gibt es in Ihrem Toolkit einen Platz für die Prozedur „EXEC procedure WITH RECOMPILE“ und die Prozedur sp_recompile . Verwenden Sie sie mit Vorsicht und achten Sie auf mögliche Auswirkungen, insbesondere mit sp_recompile.

Wenn Sie Hinweise in Code implementieren, haben Sie keine Scheuklappen für die Neukompilierung aktiviert – Sie können ‚OPTIMIEREN FÜR‘ Hinweise manchmal auch erfolgreich verwenden (obwohl Sie manchmal für mittelmäßig optimieren). Und manchmal kann dynamisches SQL Ihnen auch helfen.

Wenn Sie jedoch Hinweise zur Neukompilierung verwenden, behalten Sie Ihre Neukompilierungen bitte auf Anweisungsebene bei – und nicht in den Überschriften Ihrer Prozeduren.

Um mehr zu erfahren, lesen Sie unseren Kurs Grundlagen des Parameter-Sniffings.