Erzwingen des optimierten Plans mit dem Abfragespeicher

Gilt für: SQL Server 2022 (16.x) Azure SQL-DatenbankSQL-Datenbank in Microsoft Fabric

Die Abfrageoptimierung ist ein mehrstufiger Prozess, bei dem ein „möglichst guter“ Abfrageausführungsplan generiert werden soll. In einigen Fällen kann die Abfragekompilierung (ein Bestandteil der Abfrageoptimierung) einen Großteil der gesamten Ausführungszeit der Abfrage einnehmen und beträchtliche Systemressourcen in Anspruch nehmen. Das Erzwingen des optimierten Plans gehört zu den Features der intelligenten Abfrageverarbeitung. Die Erzwingung optimierter Pläne reduziert den Kompilierungsaufwand für sich wiederholende erzwungene Abfragen und setzt voraus, dass der Abfrage-Speicher aktiviert ist und sich im Modus „Lesen/Schreiben“ befindet. Nachdem der Abfrageausführungsplan generiert wurde, werden bestimmte Kompilierungsschritte für eine Wiederverwendung als Replay-Optimierungsskript gespeichert. Ein Replay-Optimierungsskript wird als Teil des komprimierten Showplan-XML im Abfragespeicher in einem ausgeblendeten Attribut vom Typ OptimizationReplay gespeichert.

Implementierung der Erzwingung optimierter Pläne

Wenn eine Abfrage zum ersten Mal den Kompilierungsprozess durchläuft, bestimmt ein Schwellenwert basierend auf der Schätzung der für die Optimierung aufgewendeten Zeit (basierend auf der Abfrageoptimierereingabestruktur) ob ein Optimierungs-Replay-Skript erstellt wird.

Nach Abschluss der Kompilierung wird anhand mehrerer Laufzeitmetriken bewertet, ob die vorherige Schätzung korrekt war. Wenn die Datenbank-Engine bestätigt, dass der Schwellenwert überschritten wurde, ist das Skript für die Wiedergabe der Optimierung für persistenzfähig. Diese Laufzeitmetriken umfassen die Anzahl der verwendeten Objekte, die Anzahl der Verknüpfungen, die Anzahl der während der Optimierung ausgeführten Optimierungsaufgaben und die tatsächliche Optimierungsdauer.

Der potenzielle Nutzen eines Replay-Optimierungsskripts wird auch in Relation zum Mehraufwand gesetzt, der durch das Speichern des Replay-Optimierungsskripts entsteht. Eine Schätzung der relativen Zeit für die erneute Ausführung des Optimierungswiedergabeskripts wird mit der Zeit verglichen, die für die Ausführung des regulären Optimierungsprozesses aufgewendet wurde. Diese Schätzung basiert auf der Anzahl der Optimierungsaufgaben, die im Skript für die Replay-Optimierung gespeichert sind, und der Anzahl der Optimierungsaufgaben, die während der normalen Kompilierung ausgeführt werden. Wenn die Wiedergabe des Optimierungs-Wiedergabeskripts erhebliche Vorteile bei der Verringerung der Kompilierungszeit zeigt, wird das Optimierungs-Wiedergabeskript gespeichert.

Considerations

Wenn die Funktion zur Erzwingung optimierter Pläne aktiviert ist, gelten für die Erzwingung optimierter Pläne die folgenden Eignungskriterien:

  1. Geeignet sind nur Abfragepläne, die die vollständige Optimierung durchlaufen. Dies kann durch das Vorhandensein der Eigenschaft StatementOptmLevel="FULL" überprüft werden können.

  2. Anweisungen mit RECOMPILE-Hinweis und verteilte Abfragen kommen nicht infrage.

Wenn die Abfragespeicher jedoch unabhängig einen Abfrageplan erfasst, der durch optimierte Planzwingung ausgelegt wurde, wird das Replay-Skript für die Optimierung für eine zweite Neukompilierung derselben Abfrage erstellt, vorbehaltlich der Standardmäßigen Neukompilierungsereignisse. Weitere Informationen zur Neukompilierung finden Sie unter Neukompilieren von Ausführungsplänen.

Selbst wenn ein Skript für die Optimierungswiedergabe generiert wurde, wird es möglicherweise nicht im Abfragespeicher gespeichert, wenn die in Abfragespeicher konfigurierten Kriterien der Erfassungsrichtlinien nicht erfüllt sind, insbesondere die Anzahl der Ausführungen dieser Anweisung sowie ihre kumulierte Kompilierungs- und Ausführungszeit. In diesem Fall wird das skript für die ungültige Optimierungswiedergabe asynchron aus dem Arbeitsspeicher entfernt.

Aktivieren und Deaktivieren des Erzwingens optimierter Pläne

Sie können die Erzwingung des optimierten Plans für eine Datenbank aktivieren oder deaktivieren. Wenn das Erzwingen eines optimierten Plans für eine Datenbank aktiviert ist, können Sie ihn für einzelne Abfragen mithilfe des DISABLE_OPTIMIZED_PLAN_FORCING Abfragehinweiss deaktivieren. Sie können auch das Erzwingen optimierter Pläne für einen Abfrageplan deaktivieren, der im Abfragespeicher erzwungen wird.

Aktivieren oder Deaktivieren der Erzwingung des optimierten Plans für eine Datenbank

Das Erzwingen optimierter Pläne ist für neue Datenbanken, die in SQL Server 2022 (16.x) und höheren Versionen erstellt wurden, standardmäßig aktiviert. Der Abfragespeicher muss für jede Datenbank aktiviert sein, in der die optimierte Plandurchsetzung verwendet wird. Aktualisierte Instanzen mit vorhandenen Datenbanken oder Datenbanken, die aus einer niedrigeren Version von SQL Server wiederhergestellt wurden, haben eine optimierte Planzwingung standardmäßig aktiviert.

Verwenden Sie die datenbankweit gültige Konfiguration ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON, um die Erzwingung des optimierten Plans auf Datenbankebene zu aktivieren. Ist der Abfragespeicher noch nicht aktiviert, müssen Sie diesen aktivieren. Beispielcode finden Sie in Beispiel A, weitere Informationen zum Abfragespeicher erhalten Sie unter Überwachen der Leistung mithilfe des Abfragespeichers.

Verwenden Sie die datenbankweit gültige Konfiguration ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF, um die Erzwingung des optimierten Plans auf Datenbankebene zu deaktivieren.

Deaktivieren von „Erzwingen optimierter Pläne“ mit einem Abfragehinweis

Ist die Funktion zum Erzwingen des optimierten Plans für eine Datenbank aktiviert, können Sie die Erzwingung des optimierten Plans für eine einzelne Abfrage mithilfe des DISABLE_OPTIMIZED_PLAN_FORCINGAbfragehinweises deaktivieren.

Ein Beispiel für die Anwendung dieses Abfragehinweises finden Sie in Beispiel E.

Einen Plan mit Abfragespeicher erzwingen, aber die optimierte Planerzwingung deaktivieren

Die sp_query_store_force_plan-Prozedur enthält einen disable_optimized_plan_forcing-Parameter. Um diesen Parameter verwenden zu können, wird von der Stored Procedure sp_query_store_force_plan ein zusätzlicher Parameter benötigt. Der zusätzliche Parameter heißt @replica_group_id. Standardmäßig weist die Primäre @replica_group_id den Wert 1 (1) auch dann auf, wenn keine konfigurierten sekundären Replikate vorhanden sind.

Hier finden Sie ein Beispiel zum Anwenden der entsprechenden Parameter auf die sp_query_store_force_plan gespeicherte Prozedur in Beispiel C.

Die Katalogsicht sys.query_store_plan enthält Spalten mit der Angabe, ob der Plan über ein zugeordnetes Replay-Optimierungsskript verfügt, und fügt einer vorhandenen Spalte mit den Fehlerursachen für das zugeordnete Replay-Optimierungsskript einen neuen Status hinzu. Weitere Informationen finden Sie in sys.query_store_plan.

Examples

Die Codebeispiele in diesem Artikel verwenden die AdventureWorks2025- oder AdventureWorksDW2025 Beispieldatenbank, die Sie von der Microsoft SQL Server Samples and Community Projects Homepage herunterladen können.

A. Abfragespeicher und optimierte Planerzwingung für eine Datenbank aktivieren

Mit dem folgenden Code wird für eine Datenbank zunächst der Abfragespeicher und anschließend die Erzwingung des optimierten Plans aktiviert. Erfahren Sie mehr über Optionen zum Aktivieren von Abfragespeicher in ALTER DATABASE SET Optionen.

Stellen Sie vor der Ausführung des Codes eine Verbindung mit der entsprechenden Benutzerdatenbank her.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. Wählen Sie alle Abfragen aus, für die ein Optimierungswiedergabeskript vorhanden ist.

Der folgende Beispielcode wählt alle query_ids aus, für die im Abfragespeicher ein Optimierungswiedergabeskript vorhanden ist. Stellen Sie vor der Ausführung des Beispielcodes eine Verbindung mit der entsprechenden Benutzerdatenbank her.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. Einen Plan erzwingen und die optimierte Planerzwingung in Abfragespeicher deaktivieren

Mit dem folgenden Code wird im Abfragespeicher ein Plan erzwungen und gleichzeitig die Erzwingung des optimierten Plans deaktiviert. Ersetzen Sie vor der Ausführung des folgenden Codes @query_id und @plan_id durch Werte, die für Ihre Instanz geeignet sind. Die gespeicherte Prozedur sp_query_store_force_plan erwartet, dass der Parameter @replica_group_id als dritter Parameterwert übergeben wird, wenn versucht wird, die optimierte Planerzwingung in Abfragespeicher zu deaktivieren. Damit können Sie die optimierte Planerzwingung für einen bestimmten erzwungenen Plan auf einem bestimmten Replikat deaktivieren. Ein Wert von @replica_group_id = 1 wird verwendet, um das Feature im primären Replikat zu deaktivieren.

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

Weitere Informationen finden Sie in sp_query_store_force_plan.

D. Alle Abfragen auswählen, bei denen die optimierte Planerzwingung im Abfragespeicher deaktiviert ist

Im folgenden Beispiel werden alle Pläne abgefragt, die im Abfragespeicher erzwungen wurden und bei denen is_optimized_plan_forcing_disabled auf 1 festgelegt ist. Stellen Sie vor der Ausführung des Codes eine Verbindung mit der entsprechenden Benutzerdatenbank her.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. Deaktivieren Sie das Erzwingen eines optimierten Ausführungsplans für eine Abfrage

Im folgenden Beispiel wird die Erzwingung optimierter Pläne für eine Abfrage mithilfe des DISABLE_OPTIMIZED_PLAN_FORCINGAbfragehinweises deaktiviert.

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

Verwandte Inhalte

  • Last updated on