sp_create_plan_guide (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Erstellt eine Planhinweisliste für die Zuordnung von Abfragehinweisen oder tatsächlichen Abfrageplänen zu Abfragen in einer Datenbank. Weitere Informationen zu Planhinweislisten finden Sie unter Planhinweislisten.

Transact-SQL-Syntaxkonventionen

Syntax

sp_create_plan_guide [ @name = ] N'plan_guide_name'  
    , [ @stmt = ] N'statement_text'  
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'  
    , [ @module_or_batch = ]  
      {   
                    N'[ schema_name. ] object_name'  
        | N'batch_text'  
        | NULL  
      }  
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }   
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'   
                 | N'XML_showplan'  
                 | NULL }  

Argumente

[ @name = ] N'plan_guide_name'
Der Name der Planhinweisliste. Die Gültigkeit der Namen von Planhinweislisten beschränkt sich auf die aktuelle Datenbank. plan_guide_name muss die Regeln für Bezeichner erfüllen und kann nicht mit dem Nummernzeichen (#) beginnen. Die maximale Länge von plan_guide_name beträgt 124 Zeichen.

[ @stmt = ] N'statement_text'
Ist eine Transact-SQL-Anweisung, für die eine Plananleitung erstellt werden soll. Wenn der SQL Server Abfrageoptimierer eine Abfrage erkennt, die statement_text entspricht, wird plan_guide_name wirksam. Damit die Erstellung einer Plananleitung erfolgreich ist, müssen statement_text in dem kontext angezeigt werden, der durch die Parameter @type, @module_or_batch und @params angegeben wird.

statement_text müssen so bereitgestellt werden, dass der Abfrageoptimierer sie mit der entsprechenden Anweisung im Batch oder Modul abgleichen kann, die durch @module_or_batch und @params identifiziert wird. Weitere Informationen finden Sie im Abschnitt "Hinweise". Die Größe von statement_text wird nur durch den verfügbaren Arbeitsspeicher des Servers begrenzt.

[@type = ] N'{ OBJECT | SQL | TEMPLATE }'
Der Entitätstyp, in dem statement_text angezeigt wird. Dies gibt den Kontext für den Abgleich von statement_text mit plan_guide_name an.

OBJECT
Gibt an, statement_text im Kontext einer gespeicherten Transact-SQL-Prozedur, Skalarfunktion, Multistatement-Tabellenwertfunktion oder Transact-SQL DML-Trigger in der aktuellen Datenbank angezeigt wird.

SQL
Gibt an, statement_text im Kontext einer eigenständigen Anweisung oder eines Batches angezeigt wird, der über einen beliebigen Mechanismus an SQL Server übermittelt werden kann. Transact-SQL-Anweisungen, die von CLR-Objekten (Common Language Runtime) oder erweiterten gespeicherten Prozeduren oder mithilfe von EXEC N'sql_string' übermittelt werden, werden als Batches auf dem Server verarbeitet und sollten daher als @type = "SQL" identifiziert werden. Wird SQL angegeben, kann der Abfragehinweis PARAMETERIZATION { FORCED | SIMPLE } im @hints-Parameter nicht angegeben werden.

TEMPLATE
Gibt an, dass die Plananleitung für jede Abfrage gilt, die für das in statement_text angegebene Formular parametrisiert wird. Wenn TEMPLATE angegeben ist, kann nur der PARAMETERIZATION { FORCED | SIMPLE }-Abfragehinweis im @hints-Parameter angegeben werden. Weitere Informationen zu VORLAGENplanhandbüchern finden Sie unter Angeben des Verhaltens der Abfrageparameterisierung mithilfe von Planhandbüchern.

[@module_or_batch =] { N'[ schema_name. ] object_name' | N'batch_text' | NULL }
Gibt entweder den Namen des Objekts an, in dem statement_text angezeigt wird, oder den Batchtext, in dem statement_text angezeigt wird. Der Batchtext darf keineUSE-Datenbank-Anweisung enthalten.

Damit ein Planleitfaden mit einem batch übereinstimmt, der aus einer Anwendung übermittelt wird, muss batch_text im gleichen Format (Zeichen für Zeichen) bereitgestellt werden, wie er an SQL Server übermittelt wird. Es findet keine interne Konvertierung zur Vereinfachung dieses Abgleichs statt. Weitere Informationen finden Sie im Abschnitt mit Hinweisen.

[schema_name.] object_name gibt den Namen einer gespeicherten Transact-SQL-Prozedur, einer skalaren Funktion, einer Multistatement-Tabellenwertfunktion oder eines Transact-SQL-DML-Triggers an, der statement_text enthält. Wenn schema_name nicht angegeben ist, verwendet schema_name das Schema des aktuellen Benutzers. Wenn NULL angegeben ist und @type = "SQL", wird der Wert von @module_or_batch auf den Wert von @stmt festgelegt. Wenn @type = 'TEMPLATE**' ** ist, muss @module_or_batch NULL sein.

[ @params = ] { N'@parameter_name data_type [ ,... n ]' | NULL }
Gibt die Definitionen aller Parameter an, die in statement_text eingebettet sind. @params ist nur anwendbar, wenn eine der folgenden Aussagen zutrifft:

• @type = 'SQL' oder 'TEMPLATE'. Wenn 'TEMPLATE', darf @params nicht NULL sein.

• statement_text wird mit sp_executesql übermittelt und ein Wert für den @params-Parameter angegeben, oder SQL Server intern eine Anweisung übermittelt, nachdem sie parametrisiert wurde. Die Übermittlung parametrisierter Abfragen von Datenbank-APIs (einschließlich ODBC, OLE DB und ADO.NET) SQL Server als Aufrufe von oder an sp_executesql API-Servercursorroutinen. Daher können sie auch von SQL- oder TEMPLATE-Planhandbüchern abgeglichen werden.

@parameter_name data_type müssen im exakt gleichen Format bereitgestellt werden, wie sie an SQL Server entweder mithilfe sp_executesql von oder nach der Parametrisierung intern übermittelt werden. Weitere Informationen finden Sie im Abschnitt mit Hinweisen. Wenn der Batch keine Parameter enthält, muss NULL angegeben werden. Die Größe von @params wird nur durch den verfügbaren Arbeitsspeicher des Servers begrenzt.

[@hints = ] { N'OPTION (query_hint [ ,... n ] )' | N'XML_showplan' | NULL }
N'OPTION (query_hint [ ,... n ] )
Gibt eine OPTION-Klausel an, die an eine Abfrage angefügt werden soll, die @stmt entspricht. @hints muss syntaktisch mit einer OPTION-Klausel in einer SELECT-Anweisung identisch sein und kann eine beliebige gültige Sequenz von Abfragehinweisen enthalten.

N'XML_showplan'
Dies ist der Abfrageplan im XML-Format, der als Hinweis angewendet werden soll.

Es wird empfohlen, den XML-Showplan einer Variable zuzuweisen; andernfalls müssen Sie alle einfachen Anführungszeichen im Showplan abgrenzen, indem Sie ihnen ein weiteres einfaches Anführungszeichen voranstellen. Siehe Beispiel E.

NULL
Gibt an, dass ein vorhandener Hinweis, der in der OPTION-Klausel angegeben ist, nicht auf die Abfrage angewendet wird. Weitere Informationen finden Sie unter OPTION-Klausel (Transact-SQL).

Bemerkungen

Die Argumente für sp_create_plan_guide müssen in der angezeigten Reihenfolge bereitgestellt werden. Wenn Sie Werte für die Parameter von sp_create_plan_guideangeben, müssen entweder alle oder überhaupt keine Parameternamen explizit angegeben werden. Wird z. B. @name = angegeben, müssen auch @stmt = , @type =, usw. angegeben werden. Ebenso dürfen, wenn @name = nicht angegeben und nur der Parameterwert bereitgestellt wird, die übrigen Parameterwerte ebenfalls nicht angegeben und nur ihre Werte bereitgestellt werden. Argumentnamen dienen nur zu Beschreibungszwecken, zum besseren Verständnis der Syntax. SQL Server überprüft nicht, ob der angegebene Parametername mit dem Namen für den Parameter an der Position übereinstimmt, an der der Name verwendet wird.

Sie können mehr als eine Planhinweisliste des Typs OBJECT oder SQL für dieselbe Abfrage und den Batch oder das Modul erstellen. Es kann jedoch nur jeweils eine Planhinweisliste aktiviert sein.

Planhinweislisten vom Typ OBJECT können nicht für einen @module_or_batch-Wert erstellt werden, der auf eine gespeicherte Prozedur, Funktion oder einen DML-Trigger verweist, in der bzw. dem die WITH ENCRYPTION-Klausel angegeben wird oder die bzw. der temporär ist.

Das Löschen oder Ändern einer Funktion, einer gespeicherten Prozedur oder eines DML-Triggers, auf die bzw. den in einer Planhinweisliste verwiesen wird, verursacht einen Fehler. Auch der Versuch, eine Tabelle mit einem Trigger zu löschen, auf den eine Planhinweisliste verweist, führt zu einem Fehler.

Anforderungen für den Planleitfadenabgleich

Für Planleitlinien, die @type = "SQL" oder @type = "TEMPLATE" angeben, um eine Abfrage erfolgreich abzugleichen, werden die Werte für batch_text und @parameter_name data_type [,... n ] muss in exakt demselben Format bereitgestellt werden wie die von der Anmeldung übermittelten Gegenstücke. Dies bedeutet, dass Sie den Batchtext genau so bereitstellen müssen, wie er vom SQL Server Compiler empfangen wird. Um den tatsächlichen Batch- und Parametertext zu erfassen, können Sie SQL Server Profiler verwenden. Weitere Informationen finden Sie unter Verwenden SQL Server Profiler zum Erstellen und Testen von Planhandbüchern.

Wenn @type = "SQL" und @module_or_batch auf NULL festgelegt ist, wird der Wert von @module_or_batch auf den Wert von @stmt festgelegt. Dies bedeutet, dass der Wert für statement_text in genau demselben Format ( Zeichen für Zeichen) bereitgestellt werden muss, wie er an SQL Server übermittelt wird. Es findet keine interne Konvertierung zur Vereinfachung dieses Abgleichs statt.

Wenn SQL Server dem Wert von statement_text mit batch_text und @parameter_name data_type [,... n ], oder wenn @type = **'**OBJECT' auf den Text der entsprechenden Abfrage in object_name, werden die folgenden Zeichenfolgenelemente nicht berücksichtigt:

• Leerzeichen (Tabstopps, Leerzeichen, Wagenrücklauf oder Zeilenvorschub) innerhalb der Zeichenfolge.

• Kommentare (-- oder /* */).

• Nachfolgende Semikolons

Beispielsweise können SQL Server die statement_text Zeichenfolge N'SELECT * FROM T WHERE a = 10' mit den folgenden batch_text zuordnen:

N'SELECT *
FROM T
WHERE a = 10' 

Dieselbe Zeichenfolge wird jedoch nicht mit diesem batch_text abgeglichen:

N'SELECT * FROM T WHERE b = 10'

SQL Server ignoriert die Wagenrücklauf-, Zeilenvorschub- und Leerzeichen innerhalb der ersten Abfrage. In der zweiten Abfrage wird die Sequenz WHERE b = 10 nicht auf die gleiche Art interpretiert wie WHERE a = 10. Bei der Feststellung der Übereinstimmung wird nach Groß- und Kleinschreibung sowie nach Akzenten unterschieden (selbst wenn die Sortierung der Datenbank die Groß-/Kleinschreibung nicht berücksichtigt), mit Ausnahme von Schlüsselwörtern, bei denen keine Unterscheidung nach Groß-/Kleinschreibung stattfindet. Der Abgleich ist empfindlich auf Leerzeichen. Bei der Feststellung der Übereinstimmung wird nicht nach verkürzten Formen von Schlüsselwörtern unterschieden. So werden beispielsweise die Schlüsselwörter EXECUTE, EXEC und execute als gleichwertig angesehen.

Auswirkungen der Planleitlinie 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.

Berechtigungen

Zum Erstellen eines Planleitfadens vom Typ OBJECT ist die Berechtigung für das Objekt erforderlich ALTER , auf das verwiesen wird. Zum Erstellen eines Planleitfadens vom Typ SQL oder TEMPLATE ist die Berechtigung für die aktuelle Datenbank erforderlich ALTER .

Beispiele

A. Erstellen einer Planhinweisliste vom Typ OBJECT für eine Abfrage in einer gespeicherten Prozedur

Im folgenden Beispiel wird eine Planhinweisliste erstellt, die einer im Kontext einer anwendungsbasierten gespeicherten Prozedur ausgeführten Abfrage zugeordnet wird, und der OPTIMIZE FOR-Hinweis auf die Abfrage angewendet.

Dies ist die gespeicherte Prozedur:

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL  
    DROP PROCEDURE Sales.GetSalesOrderByCountry;  
GO  
CREATE PROCEDURE Sales.GetSalesOrderByCountry   
    (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h   
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID  
    INNER JOIN Sales.SalesTerritory AS t   
        ON c.TerritoryID = t.TerritoryID  
    WHERE t.CountryRegionCode = @Country_region;  
END  
GO  

Dies ist die für die Abfrage in der gespeicherten Prozedur erstellte Planhinweisliste:

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

B. Erstellen einer Planhinweisliste vom Typ SQL für eine eigenständige Abfrage

Im folgenden Beispiel wird eine Plananleitung erstellt, um eine Abfrage in einem Batch abzugleichen, der von einer Anwendung übermittelt wird, die die sp_executesql gespeicherte Systemprozedur verwendet.

Dies ist der Batch:

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

Erstellen Sie die folgende Planhinweisliste, damit kein zweiter Plan für die parallele Ausführung für diese Abfrage generiert wird:

EXEC sp_create_plan_guide   
    @name = N'Guide1',   
    @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)';  

C. Erstellen einer Planhinweisliste vom Typ TEMPLATE für die parametrisierte Form einer Abfrage

Im folgenden Beispiel wird eine Planleitlinie erstellt, die mit jeder Abfrage übereinstimmt, die an ein angegebenes Formular parametrisiert wird, und SQL Server anleitet, die Parametrisierung der Abfrage zu erzwingen. Die folgenden beiden Abfragen sind syntaktisch gleichwertig, unterscheiden sich jedoch in ihren konstanten Literalwerten.

SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45639;  
  
SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45640;  

Dies ist die Planhinweisliste für die parametrisierte Form der Abfrage:

EXEC sp_create_plan_guide   
    @name = N'TemplateGuide1',  
    @stmt = N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
                  ON h.SalesOrderID = d.SalesOrderID  
              WHERE h.SalesOrderID = @0',  
    @type = N'TEMPLATE',  
    @module_or_batch = NULL,  
    @params = N'@0 int',  
    @hints = N'OPTION(PARAMETERIZATION FORCED)';  

Im vorhergehenden Beispiel entspricht der Wert des @stmt -Parameters der parametrisierten Form der Abfrage. Die einzig zuverlässige Möglichkeit, diesen Wert für die Verwendung in sp_create_plan_guide abzurufen, ist die gespeicherte Systemprozedur sp_get_query_template . Mithilfe des folgenden Skripts können Sie die parametrisierte Abfrage abrufen und anschließend eine Planhinweisliste für die Abfrage erstellen.

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
          ON h.SalesOrderID = d.SalesOrderID  
      WHERE h.SalesOrderID = 45639;',  
    @stmt OUTPUT,   
    @params OUTPUT  
EXEC sp_create_plan_guide N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';  

D: Erstellen einer Planhinweisliste für eine Abfrage, die über eine API-Cursoranforderung übermittelt wird

Planhinweislisten können Übereinstimmungen für Abfragen feststellen, die von API-Servercursorroutinen übermittelt werden. Zu diesen Routinen gehören sp_cursorprepare, sp_cursorprepexec und sp_cursoropen. Anwendungen, die die ADO-, OLE DB- und ODBC-APIs verwenden, interagieren häufig mit SQL Server mithilfe von API-Servercursors. Sie können den Aufruf von API-Servercursorroutinen in SQL Server Profiler Ablaufverfolgungen sehen, indem Sie das Ablaufverfolgungsereignis RPC:Starting profiler anzeigen.

Angenommen, die folgenden Daten werden in einem RPC:Starting-Ablaufverfolgungsereignis für eine Abfrage angezeigt, die mithilfe einer Planhinweisliste optimiert werden soll:

DECLARE @p1 int;  
SET @p1=-1;  
DECLARE @p2 int;  
SET @p2=0;  
DECLARE @p5 int;  
SET @p5=4104;  
DECLARE @p6 int;  
SET @p6=8193;  
DECLARE @p7 int;  
SET @p7=0;  
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'  
SELECT @p1, @p2, @p5, @p6, @p7;  

Sie stellen fest, dass im Plan für die SELECT-Abfrage im Aufruf von sp_cursorprepexec ein Mergejoin verwendet wird, Sie möchten jedoch ein Hashjoin verwenden. Die mithilfe von sp_cursorprepexec übermittelte Abfrage ist parametrisiert, einschließlich einer Abfragezeichenfolge und einer Parameterzeichenfolge. Sie können die folgende Planhinweisliste erstellen, um die Wahl des Plans zu ändern, indem die Abfrage- und Parameterzeichenfolgen, Zeichen für Zeichen, so wie sie angezeigt werden, im Aufruf von sp_cursorprepexec verwendet werden.

EXEC sp_create_plan_guide   
    @name = N'APICursorGuide',  
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h   
              INNER JOIN Sales.SalesOrderDetail AS d   
                ON h.SalesOrderID = d.SalesOrderID   
              WHERE h.OrderDate BETWEEN @P1 AND @P2',  
    @type = N'SQL',  
    @module_or_batch = NULL,  
    @params = N'@P1 varchar(255),@P2 varchar(255)',  
    @hints = N'OPTION(HASH JOIN)';  

Diese Planhinweisliste wirkt sich auf nachfolgende Ausführungen dieser Abfrage durch die Anwendung aus, und ein Hashjoin wird zur Verarbeitung der Abfrage verwendet.

E. Erstellen einer Planhinweisliste durch Abrufen des XML-Showplans aus einem zwischengespeicherten Plan

Im folgenden Beispiel wird eine Planhinweisliste für eine einfache Ad-hoc-SQL-Anweisung erstellt. Der gewünschte Abfrageplan für diese Anweisung wird in der Planhinweisliste durch die direkte Angabe des XML-Showplans für die Abfrage im @hints -Parameter bereitgestellt. Im Beispiel wird zunächst die SQL-Anweisung ausgeführt, um einen Plan im Plancache zu erzeugen. Dabei wird davon ausgegangen, dass der erzeugte Plan dem gewünschten Plan entspricht und keine weitere Optimierung der Abfrage erforderlich ist. Der XML-Showplan wird durch eine Abfrage der dynamischen Verwaltungssichten sys.dm_exec_query_stats, sys.dm_exec_sql_textund sys.dm_exec_text_query_plan sys.dm_exec_query_stats abgerufen und der Variablen @xml_showplan zugewiesen. Die @xml_showplan -Variable wird dann im sp_create_plan_guide -Parameter an die @hints -Anweisung übergeben. Alternativ können Sie die gespeicherte Prozedur sp_create_plan_guide_from_handle verwenden, um eine Planhinweisliste aus einem Abfrageplan im Plancache zu erstellen.

USE AdventureWorks2022;  
GO  
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;  
GO  
DECLARE @xml_showplan nvarchar(max);  
SET @xml_showplan = (SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');  
  
EXEC sp_create_plan_guide   
    @name = N'Guide1_from_XML_showplan',   
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',   
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = NULL,   
    @hints =@xml_showplan;  
GO  

Weitere Informationen

Planhinweislisten
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
Gespeicherte Prozeduren für die Datenbank-Engine (Transact-SQL)
Gespeicherte Systemprozeduren (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)
sp_get_query_template (Transact-SQL)