Erzwingen des optimierten Plans mit dem Abfragespeicher

Artikel
05/30/2023

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

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. Optimierte Planzwingung ist Teil der intelligenten Abfrageverarbeitungsfamilie von Features. Optimierte Planzwingung reduziert den Kompilierungsaufwand für wiederholte erzwungene Abfragen und erfordert, dass der Abfragespeicher aktiviert und im Modus "Schreibzugriff" aktiviert ist. 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 den Kompilierungsprozess das erste Mal durchläuft, bestimmt ein Schwellenwert basierend auf der geschätzten Zeit, die für die Optimierung benötigt wird (gemäß der Eingabestruktur des Abfrageoptimierers), ob ein Replay-Optimierungsskript erstellt wird.

Nach Abschluss der Kompilierung wird anhand mehrerer Laufzeitmetriken bewertet, ob die vorherige Schätzung korrekt war. Wurde die Überschreitung des Schwellenwerts bestätigt, wird das Replay-Optimierungsskript beibehalten. 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. Ein geschätzter Wert der relativen Zeit für die Wiedergabe des Optimierungsskripts wird verglichen mit der Zeit, die die Ausführung des regulären Optimierungsprozesses benötigt. Diese Werte basieren auf der Anzahl der im Replay-Optimierungsskript gespeicherten Optimierungsaufgaben sowie der Anzahl der bei einer normalen Kompilierung ausgeführten Optimierungsaufgaben. 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:

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.
Anweisungen mit dem RECOMPILE-Hinweis und verteilten Abfragen sind nicht geeignet.

Wenn der Abfragespeicher jedoch eigenständig einen Abfrageplan erfasst, der als nicht geeignet für das Erzwingen des optimierten Plans bewertet wurde, wird das Replay-Optimierungsskript für eine zweite Neukompilierung dieser Abfrage erstellt, für die die Standardereignisse für die Neukompilierung gelten. Weitere Informationen zur Neukompilierung finden Sie unter Neukompilieren von Ausführungsplänen.

Selbst wenn ein Replay-Optimierungsskript generiert wurde, wird es im Abfragespeicher möglicherweise nicht beibehalten, wenn die für den Abfragespeicher konfigurierten Erfassungsrichtlinienkriterien nicht erfüllt werden. Dies gilt insbesondere für die Anzahl der Ausführungen dieser Anweisung und die kumulierten Kompilierungs- und Ausführungszeiten. In diesem Fall wird das ungültige Replay-Optimierungsskript 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. Ist die Planerzwingung für eine Datenbank aktiviert, können Sie sie für einzelne Abfragen mithilfe des Abfragehinweises DISABLE_OPTIMIZED_PLAN_FORCING deaktivieren. Sie können die Erzwingung des optimierten Plans auch für einen Abfrageplan deaktivieren, der im Abfragespeicher erzwungen wird.

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

Optimierte Planzwingung ist standardmäßig für neue Datenbanken aktiviert, die in SQL Server 2022 (16.x) und höher erstellt wurden. 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, verfügen über einen optimierten Plan, der standardmäßig aktiviert ist.

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 AbfragehinweisesDISABLE_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 gespeicherte sp_query_store_force_plan Prozedur erforderlich. Der zusätzliche Parameter wird aufgerufen replica_group_id. Standardmäßig hat die Primäre replica_group_id den Wert 1 (1), auch wenn keine konfigurierten sekundären Replikate vorhanden sind.

Hier finden Sie ein Beispiel zum Anwenden der entsprechenden Parameter auf die gespeicherte sp_query_store_force_plan-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 unter sys.query_store_plan (Transact-SQL).

Beispiele

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. Weitere Informationen zur Aktivierung des Abfragespeichers finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).

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, das Erzwingen eines optimierten Plans im Abfragespeicher zu deaktivieren. Dies kann verwendet werden, um optimierte Planzwingung für einen bestimmten erzwungenen Plan für ein bestimmtes Replikat zu deaktivieren. Der Wert 1 – @replica_group_id=1 wird verwendet, um das Feature für das primäre Replikat zu deaktivieren.

EXEC 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 unter sp_query_store_force_plan (Transact-SQL).

D. Auswählen aller Abfragen, für die die Erzwingung des optimierten Plans im Abfragespeicher deaktiviert wurde

Im folgenden Beispiel werden alle Pläne abgefragt, die im Abfragespeicher erzwungen wurden und für die is_optimized_plan_forcing_disabled auf 1 festgelegt wurde. 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 AbfragehinweisDISABLE_OPTIMIZED_PLAN_FORCING deaktiviert. In diesem Beispiel wird die AdventureWorks-Beispieldatenbank verwendet.

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

Nächste Schritte

Informationen zum Abfragespeicher und der Erzwingung des optimierten Plans erhalten Sie in den folgenden Artikeln: