Erzwingen des optimierten Plans mit dem Abfragespeicher
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:
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 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 1
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 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