PlanhinweislistenPlan Guides

Mit Planhinweislisten können Sie die Leistung von Abfragen optimieren, wenn Sie den Text der eigentlichen Abfrage in SQL Server 2017SQL Server 2017nicht direkt ändern möchten oder können.Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2017SQL Server 2017. Planhinweislisten beeinflussen die Abfrageoptimierung, indem Abfragehinweise oder ein fester Abfrageplan an die Abfragen angefügt werden.Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. Die Verwendung von Planhinweislisten bietet sich z. B. an, wenn eine kleine Teilmenge von Abfragen in der Datenbankanwendung eines Drittanbieters nicht erwartungsgemäß funktioniert.Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected. 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.In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. Wenn die Abfrage ausgeführt wird, vergleicht SQL ServerSQL 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.When the query executes, SQL ServerSQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.

Die maximale Anzahl der erstellbaren Planhinweislisten ist lediglich durch die verfügbaren Systemressourcen begrenzt.The total number of plan guides you can create is limited only by available system resources. Planhinweislisten sollten jedoch nur begrenzt für unternehmenswichtige Abfragen verwendet werden, deren Leistung verbessert oder stabilisiert werden soll.Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. Planhinweislisten sollten nicht verwendet werden, um die überwiegende Abfragelast einer bereitgestellten Anwendung zu beeinflussen.Plan guides should not be used to influence most of the query load of a deployed application.

Hinweis

Planhinweislisten können nicht in jeder Edition von MicrosoftMicrosoft SQL ServerSQL Server.Plan guides cannot be used in every edition of MicrosoftMicrosoft SQL ServerSQL Server. Eine Liste der Funktionen, die von den SQL ServerSQL Server-Editionen unterstützt werden, finden Sie unter Von den SQL Server 2016-Editionen unterstützte Funktionen.For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016. Planhinweislisten sind in jeder Edition sichtbar.Plan guides are visible in any edition. Sie können auch in allen Versionen eine Datenbank anfügen, die Planhinweislisten enthält.You can also attach a database that contains plan guides to any edition. Planhinweislisten bleiben beim Wiederherstellen oder Anfügen einer Datenbank in einer aktualisierten Version von SQL ServerSQL Servererhalten.Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server.

Typen von PlanhinweislistenTypes of Plan Guides

Die folgenden Typen von Planhinweislisten können erstellt werden.The following types of plan guides can be created.

OBJECT-PlanhinweislisteOBJECT plan guide
OBJECT-Planhinweislisten dienen zum Abgleich von Abfragen, die im Kontext von gespeicherten Transact-SQLTransact-SQL -Prozeduren, benutzerdefinierten Skalarfunktionen, benutzerdefinierten Tabellenwertfunktionen mit mehreren Anweisungen und von DML-Triggern ausgeführt werden.An OBJECT plan guide matches queries that execute in the context of Transact-SQLTransact-SQL stored procedures, scalar user-defined functions, multi-statement table-valued user-defined functions, and DML triggers.

Angenommen, die folgende gespeicherte Prozedur, die den @Countryregion -Parameter annimmt, existiert in einer Datenbankanwendung, die in der AdventureWorks2012AdventureWorks2012 -Datenbank bereitgestellt wird:Suppose the following stored procedure, which takes the @Countryregion parameter, is in a database application that is deployed against the AdventureWorks2012AdventureWorks2012 database:

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 @Countryregion = N'AU' (Australien) optimiert wurde.Assume that this stored procedure has been compiled and optimized for @Countryregion = N'AU' (Australia). 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.However, because there are relatively few sales orders that originate from Australia, performance decreases when the query executes using parameter values of countries with more sales orders. 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.Because the most sales orders originate in the United States, a query plan that is generated for @Country_region = N'US' would likely perform better for all possible values of the @Country_region parameter.

Sie könnten dieses Problem lösen, indem Sie die gespeicherte Prozedur so ändern, dass der Abfrage der OPTIMIZE FOR -Abfragehinweis hinzugefügt wird.You could address this problem by modifying the stored procedure to add the OPTIMIZE FOR query hint to the query. Da sich die gespeicherte Prozedur jedoch in einer bereitgestellten Anwendung befindet, können Sie den Code der Anwendung nicht direkt ändern.However, because the stored procedure is in a deployed application, you cannot directly modify the application code. Stattdessen können Sie in der AdventureWorks2012AdventureWorks2012 -Datenbank die folgende Planhinweisliste erstellen.Instead, you can create the following plan guide in the AdventureWorks2012AdventureWorks2012 database.

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.When the query specified in the sp_create_plan_guide statement executes, the query is modified before optimization to include the OPTIMIZE FOR (@Country = N''US'') clause.

SQL-PlanhinweislisteSQL plan guide
SQL-Planhinweislisten dienen zum Abgleich von Abfragen, die im Kontext von eigenständigen Transact-SQLTransact-SQL -Anweisungen und -Batches, die nicht Teil eines Datenbankobjekts sind, ausgeführt werden.An SQL plan guide matches queries that execute in the context of stand-alone Transact-SQLTransact-SQL statements and batches that are not part of a database object. SQL-basierte Planhinweislisten können auch zum Abgleich von Abfragen verwendet werden, die in einer bestimmten Form parametrisiert werden.SQL-based plan guides can also be used to match queries that parameterize to a specified form. SQL-Planhinweislisten werden für eigenständige Transact-SQLTransact-SQL -Anweisungen und -Batches verwendet.SQL plan guides apply to stand-alone Transact-SQLTransact-SQL statements and batches. Diese Anweisungen werden von einer Anwendung häufig mithilfe der gespeicherten Systemprozedur sp_executesql übermittelt.Frequently, these statements are submitted by an application by using the sp_executesql system stored procedure. Betrachten Sie beispielsweise den folgenden eigenständigen Batch:For example, consider the following stand-alone 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.To prevent a parallel execution plan from being generated on this query, create the following plan guide and set the MAXDOP query hint to 1 in the @hints parameter.

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)';  
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.The values that are supplied for the @module_or_batch and @params arguments of the sp_create_plan guide statement must match the corresponding text submitted in the actual query. Weitere Informationen finden Sie unter sp_create_plan_guide (Transact-SQL) -Argument und das Verwenden von SQL Server Profiler zum Erstellen und Testen von Planhinweislistennicht direkt ändern möchten oder können.For more information, see sp_create_plan_guide (Transact-SQL) and Use SQL Server Profiler to Create and Test Plan Guides.

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.SQL plan guides can also be created on queries that parameterize to the same form when the PARAMETERIZATION database option is SET to FORCED, or when a TEMPLATE plan guide is created specifying that a parameterized class of queries.

TEMPLATE (Planhinweisliste)TEMPLATE plan guide
Eine TEMPLATE-Planhinweisliste zur Übereinstimmung mit eigenständigen Abfragen, die in einer angegebenen Form parametrisiert werden.A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. Diese Planhinweislisten werden verwendet, um die aktuelle PARAMETERIZATION-Datenbankoption für eine bestimmte Abfrageklasse zu überschreiben.These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

Sie können eine TEMPLATE-Planhinweisliste in folgenden Situationen erstellen:You can create a TEMPLATE plan guide in either of the following situations:

  • Die Datenbankoption PARAMETERIZATION ist auf FORCED festgelegt, es gibt aber Abfragen, die nach den Regeln der einfachen Parametrisierung kompiliert werden sollen.The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of simple parameterization.

  • Die Datenbankoption PARAMETERIZATION ist auf SIMPLE festgelegt (die Standardeinstellung), für eine Klasse von Abfragen soll aber eine erzwungene Parametrisierung versucht werden.The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want forced parameterization to be tried on a class of queries.

Voraussetzungen für den PlanhinweislistenabgleichPlan Guide Matching Requirements

Planhinweislisten beziehen sich auf die Datenbank, in der sie erstellt werden.Plan guides are scoped to the database in which they are created. 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.Therefore, only plan guides that are in the database that is current when a query executes can be matched to the query. Beispiel: Wenn AdventureWorks2012AdventureWorks2012 die aktuelle Datenbank ist und die folgende Abfrage ausgeführt wird:For example, if AdventureWorks2012AdventureWorks2012 is the current database and the following query executes:

SELECT FirstName, LastName FROM Person.Person;

Dann können nur in der AdventureWorks2012AdventureWorks2012 -Datenbank vorhandene Planhinweislisten mit dieser Abfrage verglichen werden.Only plan guides in the AdventureWorks2012AdventureWorks2012 database are eligible to be matched to this query. Wenn jedoch AdventureWorks2012AdventureWorks2012 die aktuelle Datenbank ist und die folgenden Anweisungen ausgeführt werden:However, if AdventureWorks2012AdventureWorks2012 is the current database and the following statements are run:

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.Only plan guides in DB1 are eligible to be matched to the query because the query is executing in the context of DB1.

Bei SQL- und TEMPLATE-basierten Planhinweislisten vergleicht SQL ServerSQL Server die Werte der Argumente @module_or_batch und @params mit einer Abfrage, indem die beiden Werte Zeichen für Zeichen verglichen werden.For SQL- or TEMPLATE-based plan guides, SQL ServerSQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character. Das bedeutet, dass Sie den Text genau so bereitstellen müssen, wie er von SQL ServerSQL Server im tatsächlichen Batch empfangen wird.This means you must provide the text exactly as SQL ServerSQL Server receives it in the actual batch.

Wenn @type = 'SQL' und @module_or_batch auf NULL gesetzt wird, wird der Wert von @module_or_batch auf den Wert von @stmt festgelegt.When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt.</span></span> 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 ServerSQL Serverübermittelt wird.This means that the value for statement_text must be provided in the identical format, character-for-character, as it is submitted to SQL ServerSQL Server. Es findet keine interne Konvertierung zur Vereinfachung dieses Abgleichs statt.No internal conversion is performed to facilitate this match.

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.When both a regular (SQL or OBJECT) plan guide and a TEMPLATE plan guide can apply to a statement, only the regular plan guide will be used.

Hinweis

Der Batch, der die Anweisung enthält, für die Sie eine Planhinweisliste erstellen wollen, darf keine USE database -Anweisung enthalten.The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

Auswirkungen von Planhinweislisten auf den PlancachePlan Guide Effect on the Plan Cache

Wenn Sie eine Planhinweisliste für ein Modul erstellen, wird der Abfrageplan für dieses Modul aus dem Plancache entfernt.Creating a plan guide on a module removes the query plan for that module from the plan cache. 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.Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. Wenn Sie eine Planhinweisliste des Typs TEMPLATE erstellen, werden alle Batches mit einer Anweisung aus dem Plancache in dieser Datenbank entfernt.Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

TaskTask ThemaTopic
Beschreibt, wie eine Planhinweisliste erstellt wird.Describes how to create a plan guide. Erstellen einer neuen PlanhinweislisteCreate a New Plan Guide
Beschreibt, wie eine Planhinweisliste für parametrisierte Abfragen erstellt wird.Describes how to create a plan guide for parameterized queries. Erstellen einer Planhinweisliste für parametrisierte AbfragenCreate a Plan Guide for Parameterized Queries
Beschreibt, wie das Abfrageparametrisierungs-Verhalten mit Planhinweislisten gesteuert wird.Describes how to control query parameterization behavior by using plan guides. Angeben des Abfrageparametrisierungsverhaltens mithilfe von PlanhinweislistenSpecify Query Parameterization Behavior by Using Plan Guides
Beschreibt, wie ein fester Abfrageplan in eine Planhinweisliste eingeschlossen wird.Describes how to include a fixed query plan in a plan guide. Anwenden eines festen Abfrageplans auf eine PlanhinweislisteApply a Fixed Query Plan to a Plan Guide
Beschreibt, wie Abfragehinweise in einer Planhinweisliste angegeben werden.Describes how to specify query hints in a plan guide. Anfügen von Abfragehinweisen an eine PlanhinweislisteAttach Query Hints to a Plan Guide
Beschreibt, wie Planhinweislisten-Eigenschaften angezeigt werden.Describes how to view plan guide properties. Anzeigen der Eigenschaften der PlanhinweislisteView Plan Guide Properties
Beschreibt, wie SQL Server Profiler zum Erstellen und Testen von Testplanhinweislisten verwendet wird.Describes how to use SQL Server Profiler to create and test plan guides. Verwenden von SQL Server Profiler zum Erstellen und Testen von PlanhinweislistenUse SQL Server Profiler to Create and Test Plan Guides
Beschreibt, wie Planhinweislisten überprüft werden.Describes how to validate plan guides. Überprüfen von Planhinweislisten nach einem UpgradeValidate Plan Guides After Upgrade

Siehe auchSee Also

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