Erzwingen des optimierten Plans mit dem Abfragespeicher

  • Artikel

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

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. Erzwingung des optimierten Plans reduziert den Kompilierungsaufwand für wiederholte erzwungene Abfragen und erfordert, dass die Abfragespeicher aktiviert und im Modus „Lese-/Schreibzugriff“ aktiviert werden. 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.

Implementieren der Erzwingung des optimierten Plans

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 zur Wiedergabe des Skripts für die Replay-Optimierung wird mit der Zeit verglichen, die für die Ausführung des normalen 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. Kann durch die Wiedergabe des Optimierungsskripts die Kompilierungszeit deutlich verringert werden, wird das Replay-Optimierungsskript beibehalten.

Überlegungen

Ist das Feature zur Erzwingung des optimierten Plans aktiviert, lauten die Eignungskriterien dafür wie folgt:

  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 verteilten Abfragen sind nicht berechtigt.

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 Replay-Optimierung generiert wurde, wird es möglicherweise nicht im Abfragespeicher beibehalten, wenn die Abfragespeicher konfigurierten Aufnahmerichtlinienkriterien nicht erfüllt sind, insbesondere die Anzahl der Ausführungen dieser Anweisung und der kumulierten Kompilierungs- und Ausführungszeiten. In diesem Fall wird das skript für die ungültige Optimierungswiedergabe asynchron aus dem Arbeitsspeicher entfernt.

Aktivieren und Deaktivieren der Erzwingung des optimierten Plans

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 eines optimierten Plans für einen Abfrageplan deaktivieren, der in Abfragespeicher erzwungen wird.

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

Das Erzwingen des optimierten Plans ist für neue Datenbanken, die in SQL Server 2022 (16.x) und höher erstellt wurden, standardmäßig aktiviert. Für Datenbanken, die die Erzwingung des optimierten Plans nutzen, muss der Abfragespeicher aktiviert werden. 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 der Erzwingung des optimierten Plans mit einem Abfragehinweis

Ist das Feature 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 Abfragehinweises DISABLE_OPTIMIZED_PLAN_FORCING deaktivieren.

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

Erzwingen eines Plans mit dem Abfragespeicher und Deaktivieren der Erzwingung des optimierten Plans

Die sp_query_store_force_plan-Prozedur enthält einen disable_optimized_plan_forcing-Parameter. Um diesen Parameter verwenden zu können, ist ein zusätzlicher Parameter für die sp_query_store_force_plan gespeicherte Prozedur erforderlich. Der zusätzliche Parameter wird aufgerufen @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.

Beispiele

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

A. Aktivieren des Abfragespeichers und der Erzwingung des optimierten Plans für eine Datenbank

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. Auswählen aller Abfragen mit einem Replay-Optimierungsskript

Mit dem folgenden Beispielcode werden alle „query_ids“ ausgewählt, die über ein Replay-Optimierungsskript im Abfragespeicher verfügen. 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. Erzwingen eines Plans und Deaktivieren der Erzwingung des optimierten Plans im Abfragespeicher

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 sp_query_store_force_plan gespeicherte Prozedur erwartet, dass der @replica_group_id Parameter als dritter Parameterwert übergeben wird, wenn versucht wird, den optimierten Plan in Abfragespeicher zu deaktivieren. Damit können Sie die Erzwingung des optimierten Plans 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: Auswählen aller Abfragen, für die die Erzwingung des optimierten Plans im Abfragespeicher deaktiviert wurde

Im folgenden Beispiel werden alle Pläne abfragen, die in Abfragespeicher erzwungen wurden, auf die is_optimized_plan_forcing_disabled festgelegt 1ist. 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 der Erzwingung des optimierten Plans für eine Abfrage

Im folgenden Beispiel wird die Erzwingung des optimierten Plans für eine Abfrage mit dem Abfragehinweis DISABLE_OPTIMIZED_PLAN_FORCING 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

Zugehöriger Inhalt