Freigeben über

Planhinweislisten

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Mit Planhinweislisten können Sie die Leistung von Abfragen optimieren, wenn Sie den Text der eigentlichen Abfrage in SQL Server nicht direkt ändern möchten oder können. Planhinweislisten beeinflussen die Abfrageoptimierung, indem Abfragehinweise oder ein fester Abfrageplan an die Abfragen angefügt werden. Die Verwendung von Planhinweislisten bietet sich z. B. an, wenn eine kleine Teilmenge von Abfragen in der Datenbankanwendung eines Drittanbieters nicht erwartungsgemäß funktioniert. In der Planhinweisliste geben Sie die Transact-SQL-Anweisung an, die optimiert werden soll, sowie entweder eine OPTION-Klausel mit den zu verwendenden Abfragehinweisen oder einen spezifischen Abfrageplan, der für die Optimierung der Abfrage verwendet werden soll. Wenn die Abfrage ausgeführt wird, vergleicht SQL Server die Transact-SQL-Anweisung mit der Planhinweisliste und fügt der Abfrage entweder zur Laufzeit die OPTION-Klausel hinzu oder verwendet den angegebenen Abfrageplan. Da der SQL Server-Abfrageoptimierer in der Regel den besten Ausführungsplan für eine Abfrage auswählt, wird empfohlen, dass erfahrene Entwickler und Datenbankadministratoren Planhinweislisten nur verwenden, wenn alle anderen Möglichkeiten sich als unbefriedigend erwiesen haben.

Hinweis

Mit Abfragespeicherhinweisen können Sie Abfragepläne auf einfache Weise strukturieren, ohne den Anwendungscode ändern zu müssen. Abfragespeicherhinweise sind einfacher als Planhinweislisten. Abfragespeicherhinweise sind in Azure SQL Database und Azure SQL Managed Instance sowie in SQL Server 2022 (16.x) und höher verfügbar.

Die maximale Anzahl der erstellbaren Planhinweislisten ist lediglich durch die verfügbaren Systemressourcen begrenzt. Planhinweislisten sollten jedoch nur begrenzt für unternehmenswichtige Abfragen verwendet werden, deren Leistung verbessert oder stabilisiert werden soll. Planhinweislisten sollten nicht verwendet werden, um die überwiegende Abfragelast einer bereitgestellten Anwendung zu beeinflussen.

Der durch dieses Feature erzwungene resultierende Ausführungsplan entspricht oder ähnelt dem Plan, der erzwungen wird. Da der resultierende Plan möglicherweise nicht mit dem von der Planhinweisliste angegebenen Plan identisch ist, kann die Leistung der Pläne variieren. In seltenen Fällen kann der Leistungsunterschied erheblich und negativ sein. In diesem Fall muss der Administrator den erzwungenen Plan entfernen.

Planhinweislisten können nicht in jeder Edition von Microsoft SQL Server verwendet werden. Eine Liste der Funktionen, die von den SQL Server-Editionen unterstützt werden, finden Sie unter Von den SQL Server 2016-Editionen unterstützte Funktionen. Planhinweislisten sind in jeder Edition sichtbar. Sie können auch in allen Versionen eine Datenbank anfügen, die Planhinweislisten enthält. Planhinweislisten bleiben beim Wiederherstellen oder Anfügen einer Datenbank in einer aktualisierten Version von SQL Server erhalten.

Typen von Planhinweislisten

Die folgenden Typen von Planhinweislisten können erstellt werden.

OBJECT-Planhinweisliste

OBJECT-Planhinweislisten dienen zum Abgleich von Abfragen, die im Kontext von gespeicherten Transact-SQL-Prozeduren, benutzerdefinierten Skalarfunktionen, benutzerdefinierten Tabellenwertfunktionen mit mehreren Anweisungen und DML-Triggern ausgeführt werden.

Angenommen, die folgende gespeicherte Prozedur, die den @Country_region-Parameter annimmt, existiert in einer Datenbankanwendung, die in der AdventureWorks2022-Datenbank bereitgestellt wird:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;

Gehen Sie weiterhin davon aus, dass diese gespeicherte Prozedur kompiliert und für @Country_region = N'AU' (Australien) optimiert wurde. Aus Australien kommen jedoch nur relativ wenige Bestellungen. Wenn die Abfrage mit Parameterwerten für Länder oder Regionen mit mehr Bestellungen ausgeführt wird, verringert dies die Leistung. Da die meisten Bestellungen aus den USA kommen, würde ein für @Country_region = N'US' generierter Abfrageplan wahrscheinlich eine bessere Leistung für alle möglichen Werte des @Country_region -Parameters erbringen.

Sie könnten dieses Problem lösen, indem Sie die gespeicherte Prozedur so ändern, dass der Abfrage der OPTIMIZE FOR -Abfragehinweis hinzugefügt wird. Da sich die gespeicherte Prozedur jedoch in einer bereitgestellten Anwendung befindet, können Sie den Code der Anwendung nicht direkt ändern. Stattdessen können Sie in der AdventureWorks2022 -Datenbank die folgende Planhinweisliste erstellen.

sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,  
        Sales.Customer AS c,  
        Sales.SalesTerritory AS t  
        WHERE h.CustomerID = c.CustomerID   
            AND c.TerritoryID = t.TerritoryID  
            AND CountryRegionCode = @Country_region',  
@type = N'OBJECT',  
@module_or_batch = N'Sales.GetSalesOrderByCountry',  
@params = NULL,  
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

Wenn die in der sp_create_plan_guide -Anweisung angegebene Abfrage ausgeführt wird, wird sie vor der Optimierung so geändert, dass sie die OPTIMIZE FOR (@Country = N''US'') -Klausel enthält.

SQL-Planhinweisliste

Eine SQL-Plananleitung gleicht Abfragen ab, die im Kontext von eigenständigen Transact-SQL-Anweisungen und Batches ausgeführt werden, die nicht Teil eines Datenbankobjekts sind. SQL-basierte Planhinweislisten können auch zum Abgleich von Abfragen verwendet werden, die in einer bestimmten Form parametrisiert werden. SQL-Planhinweislisten werden für eigenständige Transact-SQL-Anweisungen und -Batches verwendet. Diese Anweisungen werden von einer Anwendung häufig mithilfe der gespeicherten Systemprozedur sp_executesql übermittelt. Betrachten Sie beispielsweise den folgenden eigenständigen Batch:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Um zu verhindern, dass ein paralleler Ausführungsplan für diese Abfrage generiert wird, erstellen Sie die folgende Planhinweisliste und legen den MAXDOP -Abfragehinweis im 1 -Parameter auf @hints fest.

sp_create_plan_guide   
@name = N'Guide2',   
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',  
@type = N'SQL',  
@module_or_batch = NULL,   
@params = NULL,   
@hints = N'OPTION (MAXDOP 1)';

Sehen Sie sich als weiteres Beispiel die folgende SQL-Anweisung an, die mit sp_executesql übermittelt wird.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;

Erstellen Sie die folgende Planhinweisliste, und verwenden Sie den Abfragehinweis OPTION (RECOMPILE) im Parameter @hints, um einen eindeutigen Plan für alle Ausführungen dieser Abfrage zu erstellen.

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

Wichtig

Die für das @module_or_batch -Argument und das @params -Argument der sp_create_plan guide -Anweisung angegebenen Werte müssen mit dem Text übereinstimmen, der in der Abfrage übermittelt wird. Weitere Informationen finden Sie unter sp_create_plan_guide (Transact-SQL) und Erstellen und Testen von Planhinweislisten mithilfe von SQL Server Profiler.

SQL-Planhinweislisten können auch für Abfragen erstellt werden, die in derselben Form parametrisiert werden, wenn die PARAMETERIZATION-Datenbankoption mithilfe von SET auf FORCED festgelegt wird oder wenn eine TEMPLATE-Planhinweisliste erstellt wird, die eine parametrisierte Abfrageklasse angibt.

TEMPLATE (Planhinweisliste)

Eine TEMPLATE-Planhinweisliste zur Übereinstimmung mit eigenständigen Abfragen, die in einer angegebenen Form parametrisiert werden. Diese Planhinweislisten werden verwendet, um die aktuelle PARAMETERIZATION-Datenbankoption für eine bestimmte Abfrageklasse zu überschreiben.

Sie können eine TEMPLATE-Planhinweisliste in folgenden Situationen erstellen:

  • Die Datenbankoption PARAMETERIZATION ist auf FORCED festgelegt, es gibt aber Abfragen, die nach den Regeln der einfachen Parametrisierung kompiliert werden sollen.

  • Die Datenbankoption PARAMETERIZATION ist auf SIMPLE festgelegt (die Standardeinstellung), für eine Klasse von Abfragen soll aber eine erzwungene Parametrisierung versucht werden.

Voraussetzungen für den Planhinweislistenabgleich

Planhinweislisten beziehen sich auf die Datenbank, in der sie erstellt werden. Daher können nur die Planhinweislisten gegen die Abfrage geprüft werden, die in der zum Zeitpunkt der Ausführung einer Abfrage aktuellen Datenbank vorhanden sind. Beispiel: Wenn AdventureWorks2022 die aktuelle Datenbank ist und die folgende Abfrage ausgeführt wird:

SELECT FirstName, LastName FROM Person.Person;

Dann können nur in der AdventureWorks2022 -Datenbank vorhandene Planhinweislisten mit dieser Abfrage verglichen werden. Wenn jedoch AdventureWorks2022 die aktuelle Datenbank ist und die folgenden Anweisungen ausgeführt werden:

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

Dann können nur in DB1 vorhandene Planhinweislisten mit dieser Abfrage verglichen werden, weil die Abfrage im Kontext von DB1ausgeführt wird.

Bei SQL- und TEMPLATE-basierten Planhinweislisten vergleicht SQL Server die Werte der @module_or_batch- und @params-Argumente und mit einer Abfrage, indem die beiden Werte Zeichen für Zeichen verglichen werden. Das bedeutet, dass Sie den Text genau so bereitstellen müssen, wie er von SQL Server im tatsächlichen Batch empfangen wird.

Wenn @type = „SQL“ und @module_or_batch auf NULL festgelegt ist, wird der Wert des Werts @module_or_batch auf den Wert von @stmt. Dies bedeutet, dass der Wert für statement_text im identischen Format, Zeichen für Zeichen angegeben werden muss, wie er an SQL Server übermittelt wird. Es findet keine interne Konvertierung zur Vereinfachung dieses Abgleichs statt.

Wenn für eine Anweisung sowohl eine reguläre Planhinweisliste (SQL oder OBJECT) als auch eine TEMPLATE-Planhinweisliste gelten können, wird nur die reguläre Planhinweisliste verwendet.

Hinweis

Der Batch, der die Anweisung enthält, für die Sie eine Planhinweisliste erstellen wollen, darf keine USE database -Anweisung enthalten.

Auswirkungen von Planhinweislisten auf den Plancache

Wenn Sie eine Planhinweisliste für ein Modul erstellen, wird der Abfrageplan für dieses Modul aus dem Plancache entfernt. Wenn Sie eine Planhinweisliste des Typs OBJECT oder SQL für einen Batch erstellen, wird der Abfrageplan für einen Batch mit demselben Hashwert entfernt. Wenn Sie eine Planhinweisliste des Typs TEMPLATE erstellen, werden alle Batches mit einer Anweisung aus dem Plancache in dieser Datenbank entfernt.

Aufgabe Thema
Beschreibt, wie eine Planhinweisliste erstellt wird. Erstellen einer neuen Planhinweisliste
Beschreibt, wie eine Planhinweisliste für parametrisierte Abfragen erstellt wird. Erstellen einer Planhinweisliste für parametrisierte Abfragen
Beschreibt, wie das Abfrageparametrisierungs-Verhalten mit Planhinweislisten gesteuert wird. Angeben des Abfrageparametrisierungsverhaltens mithilfe von Planhinweislisten
Beschreibt, wie ein fester Abfrageplan in eine Planhinweisliste eingeschlossen wird. Anwenden eines festen Abfrageplans auf eine Planhinweisliste
Beschreibt, wie Abfragehinweise in einer Planhinweisliste angegeben werden. Anfügen von Abfragehinweisen an eine Planhinweisliste
Beschreibt, wie Planhinweislisten-Eigenschaften angezeigt werden. Anzeigen der Eigenschaften der Planhinweisliste
Beschreibt, wie SQL Server Profiler zum Erstellen und Testen von Testplanhinweislisten verwendet wird. Verwenden von SQL Server Profiler zum Erstellen und Testen von Planhinweislisten
Beschreibt, wie Planhinweislisten überprüft werden. Überprüfen von Planhinweislisten nach einem Upgrade

Weitere Informationen

sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)