Planhinweislisten

Mit Plananleitungen können Sie die Leistung von Abfragen optimieren, wenn Sie den Text der tatsächlichen Abfrage in SQL Server 2014 nicht direkt ändern können oder möchten. 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.

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.

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 @Countryregion _-Parameter akzeptiert, befindet sich in einer Datenbankanwendung, die für die AdventureWorks2012-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. Da es jedoch relativ wenige Verkaufsaufträge gibt, die aus Australien stammen, sinkt die Leistung, wenn die Abfrage unter Verwendung von Parameterwerten von Ländern/Regionen mit mehr Verkaufsaufträgen ausgeführt wird. Da die meisten Verkaufsaufträge aus dem USA stammen, würde ein Abfrageplan, der für @Country_region = N'US' generiert wird, wahrscheinlich besser für alle möglichen Werte des @Country_region-Parameters funktionieren.

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 AdventureWorks2012 -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
SQL-Planhinweislisten dienen zum Abgleich von Abfragen, 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)';  

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 AdventureWorks2012 die aktuelle Datenbank ist und die folgende Abfrage ausgeführt wird:

SELECT FirstName, LastName FROM Person.Person;

Dann können nur in der AdventureWorks2012 -Datenbank vorhandene Planhinweislisten mit dieser Abfrage verglichen werden. Wenn jedoch AdventureWorks2012 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 Argumente @module_or_batch und @params 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 gesetzt wird, wird der Wert von @module_or_batch auf den Wert von @stmt festgelegt. Dies bedeutet, dass der Wert für statement_text Zeichen für Zeichen in exakt dem gleichen Format bereitgestellt werden muss, in dem 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.

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.

Related Tasks

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)