sp_create_plan_guide (Transact-SQL)sp_create_plan_guide (Transact-SQL)

GILT FÜR: jaSQL Server (ab 2008) jaAzure SQL-DatenbankjaAzure SQL Data Warehouse neinParallel Data WarehouseAPPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Erstellt eine Planhinweisliste für die Zuordnung von Abfragehinweisen oder tatsächlichen Abfrageplänen zu Abfragen in einer Datenbank.Creates a plan guide for associating query hints or actual query plans with queries in a database. Weitere Informationen zu Planhinweislisten finden Sie unter Planhinweislisten.For more information about plan guides, see Plan Guides.

Themenlinksymbol Transact-SQL Syntax Conventions (Transact-SQL-Syntaxkonventionen)Topic link icon Transact-SQL Syntax Conventions

SyntaxSyntax


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 }  

ArgumenteArguments

[ @Name =] N'Plan_guide_name"[ @name = ] N'plan_guide_name'
Der Name der Planhinweisliste.Is the name of the plan guide. Die Gültigkeit der Namen von Planhinweislisten beschränkt sich auf die aktuelle Datenbank.Plan guide names are scoped to the current database. Plan_guide_name müssen entsprechen den Regeln für Bezeichner und kann nicht gestartet, mit dem Nummernzeichen (#).plan_guide_name must comply with the rules for identifiers and cannot start with the number sign (#). Die maximale Länge des Plan_guide_name Zeichenanzahl von 124 ist.The maximum length of plan_guide_name is 124 characters.

[ @Stmt =] N'Statement_text"[ @stmt = ] N'statement_text'
Ist eine Transact-SQLTransact-SQL-Anweisung, für die eine Planhinweisliste erstellt werden soll.Is a Transact-SQLTransact-SQL statement against which to create a plan guide. Wenn die SQL ServerSQL Server -Abfrageoptimierer erkennt eine Abfrage, die entspricht Statement_text, Plan_guide_name wirksam.When the SQL ServerSQL Server query optimizer recognizes a query that matches statement_text, plan_guide_name takes effect. Für die Erstellung einer Planhinweisliste erfolgreich ausgeführt werden kann Statement_text muss angezeigt werden, in dem vom angegebenen Kontext den @Typ @Module_or_batch, und @Params-Parameter.For the creation of a plan guide to succeed, statement_text must appear in the context specified by the @type, @module_or_batch, and @params parameters.

Statement_text muss angegeben werden, auf eine Weise, die der Abfrageoptimierer in Übereinstimmung mit der entsprechenden Anweisung im Batch oder Modul, das durch ermöglicht @Module_or_batch und @Params.statement_text must be provided in a way that allows for the query optimizer to match it with the corresponding statement supplied within the batch or module identified by @module_or_batch and @params. Weitere Informationen finden Sie im Abschnitt "Hinweise".For more information, see the "Remarks" section. Die Größe des Statement_text wird nur durch den verfügbaren Arbeitsspeicher des Servers beschränkt.The size of statement_text is limited only by available memory of the server.

[@Typ =] N'{OBJECT | SQL | VORLAGE} "[@type = ]N'{ OBJECT | SQL | TEMPLATE }'
Ist der Typ der Entität, in der Statement_text angezeigt wird.Is the type of entity in which statement_text appears. Dies gibt den Kontext für den Abgleich Statement_text zu Plan_guide_name.This specifies the context for matching statement_text to plan_guide_name.

OBJECTOBJECT
Gibt an Statement_text angezeigt wird, im Rahmen einer Transact-SQLTransact-SQL gespeicherte Prozedur, Skalarfunktion, aus mehreren Anweisungen bestehenden Funktion mit Tabellenrückgabe oder Transact-SQLTransact-SQL DML-Trigger in der aktuellen Datenbank.Indicates statement_text appears in the context of a Transact-SQLTransact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQLTransact-SQL DML trigger in the current database.

SQLSQL
Gibt an Statement_text angezeigt wird, im Kontext einer eigenständigen Anweisung oder der Batch, die an gesendet werden kann SQL ServerSQL Server auf beliebige Weise.Indicates statement_text appears in the context of a stand-alone statement or batch that can be submitted to SQL ServerSQL Server through any mechanism. Transact-SQLTransact-SQL Anweisungen, die gesendet werden, durch die common Language Runtime (CLR)-Objekte oder erweiterte gespeicherte Prozeduren oder mithilfe von EXEC N'Sql_string", werden als Batches auf dem Server verarbeitet und sollte daher angegeben werden, als @Typ = 'SQL'. statements submitted by common language runtime (CLR) objects or extended stored procedures, or by using EXEC N'*sql_string*', are processed as batches on the server and, therefore, should be identified as @type *=* 'SQL'. Wenn SQL angegeben kann, der Abfragehinweis PARAMETERIZATION {FORCED | EINFACHE} kann nicht angegeben werden, der @Hinweise Parameter.If SQL is specified, the query hint PARAMETERIZATION { FORCED | SIMPLE } cannot be specified in the @hints parameter.

TEMPLATETEMPLATE
Gibt an, die Planhinweisliste angewendet wird, um jede Abfrage, die auf die im angegebenen Format parametrisiert Statement_text.Indicates the plan guide applies to any query that parameterizes to the form indicated in statement_text. Wenn TEMPLATE angegeben ist, nur der PARAMETERIZATION {FORCED | EINFACHE}-Abfragehinweis kann angegeben werden, der @Hinweise Parameter.If TEMPLATE is specified, only the PARAMETERIZATION { FORCED | SIMPLE } query hint can be specified in the @hints parameter. Weitere Informationen zu TEMPLATE-Planhinweislisten, finden Sie unter angeben des Abfrageparametrisierungsverhaltens mithilfe von Planhinweislisten.For more information about TEMPLATE plan guides, see Specify Query Parameterization Behavior by Using Plan Guides.

[@Module_or_batch =] {N'[ Schema_name.[@module_or_batch =]{ N'[ schema_name. ] Object_name"| N'Batch_text"| NULL}] 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.Specifies either the name of the object in which statement_text appears, or the batch text in which statement_text appears. Der Batchtext darf keine Verwendung enthaltenDatenbank Anweisung.The batch text cannot include a USEdatabase statement.

Damit eine Planhinweisliste entsprechend einen Batch zugeordnet, die aus einer Anwendung übermittelt Batch_text muss angegeben werden, im gleichen Format, Zeichen für Zeichen, wie beim Übermitteln SQL ServerSQL Server.For a plan guide to match a batch submitted from an application, batch_text must be provided in the same 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. Weitere Informationen finden Sie im Abschnitt mit Hinweisen.For more information, see the Remarks section.

[Schema_name.] Object_name gibt den Namen einer Transact-SQLTransact-SQL gespeicherte Prozedur, Skalarfunktion, aus mehreren Anweisungen bestehenden Funktion mit Tabellenrückgabe oder Transact-SQLTransact-SQL DML-Trigger, enthält Statement_text.[schema_name.]object_name specifies the name of a Transact-SQLTransact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQLTransact-SQL DML trigger that contains statement_text. Wenn Schema_name nicht angegeben ist, Schema_name wird das Schema des aktuellen Benutzers verwendet.If schema_name is not specified, schema_name uses the schema of the current user. Wenn NULL angegeben wird und @Type = 'SQL', den Wert der @Module_or_batch festgelegt ist, auf den Wert der @Stmt. Wenn @Type = "Vorlage ", @Module_or_batch muss NULL sein.If NULL is specified and @type = 'SQL', the value of @module_or_batch is set to the value of @stmt. If @type = 'TEMPLATE ', @module_or_batch must be NULL.

[ @Params =] {N "@Parameter_name, Data_type [,.. ...n ]' | NULL}[ @params = ]{ N'@parameter_name data_type [ ,...n ]' | NULL }
Gibt an, die Definitionen aller Parameter, die in eingebetteten Statement_text.Specifies the definitions of all parameters that are embedded in statement_text. @Params gilt nur wenn eine der folgenden Aussagen zutrifft:@params applies only when either of the following is true:

  • @Type = 'SQL' oder 'TEMPLATE'.@type = 'SQL' or 'TEMPLATE'. Wenn 'TEMPLATE' @Params darf nicht NULL sein.If 'TEMPLATE', @params must not be NULL.

  • Statement_text gesendet wird, mithilfe von Sp_executesql und einen Wert für die @Params-Parameter angegeben wird, oder SQL ServerSQL Server intern übermittelt eine Anweisung, nachdem Sie parametrisiert wurde.statement_text is submitted by using sp_executesql and a value for the @params parameter is specified, or SQL ServerSQL Server internally submits a statement after parameterizing it. Die Übermittlung parametrisierter Abfragen von Datenbank-APIs (einschließlich ODBC, OLE DB und ADO.NET) werden in SQL ServerSQL Server als Aufrufe von sp_executesql oder von API-Servercursorroutinen angezeigt; deshalb können Übereinstimmungen auch von SQL- oder TEMPLATE-Planhinweislisten festgestellt werden.Submission of parameterized queries from database APIs (including ODBC, OLE DB, and ADO.NET) appear to SQL ServerSQL Server as calls to sp_executesql or to API server cursor routines; therefore, they can also be matched by SQL or TEMPLATE plan guides.

    @Parameter_name, Data_type muss im exakt gleichen Format angegeben werden, wie beim Übermitteln SQL ServerSQL Server entweder mithilfe von Sp_executesql oder durch internes übermitteln nach der Parametrisierung.@parameter_name data_type must be supplied in the exact same format as it is submitted to SQL ServerSQL Server either by using sp_executesql or submitted internally after parameterization. Weitere Informationen finden Sie im Abschnitt mit Hinweisen.For more information, see the Remarks section. Wenn der Batch keine Parameter enthält, muss NULL angegeben werden.If the batch does not contain parameters, NULL must be specified. Die Größe des @Params wird nur durch den verfügbaren Arbeitsspeicher des Servers beschränkt.The size of @params is limited only by available server memory.

    [@Hinweise =] {n ' Option (Query_hint [,.. ...n ]) "| N'XML_showplan"| NULL}[@hints = ]{ N'OPTION (query_hint [ ,...n ] )' | N'XML_showplan' | NULL }
    N ' Option (Query_hint [,.. ...n ])N'OPTION (query_hint [ ,...n ] )
    Gibt an, eine Verbindung mit einer Abfrage, die entspricht OPTION-Klausel @Stmt. @Hinweise muss syntaktisch identisch mit einer OPTION-Klausel in einer SELECT-Anweisung, und kann eine beliebige gültige Sequenz von Abfragehinweisen enthalten.Specifies an OPTION clause to attach to a query that matches @stmt. @hints must be syntactically the same as an OPTION clause in a SELECT statement, and can contain any valid sequence of query hints.

    N'XML_showplan'N'XML_showplan'
    Dies ist der Abfrageplan im XML-Format, der als Hinweis angewendet werden soll.Is the query plan in XML format to be applied as a hint.

    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.We recommend assigning the XML Showplan to a variable; otherwise, you must escape any single quotation marks in the Showplan by preceding them with another single quotation mark. Siehe Beispiel E.See example E.

    NULLNULL
    Gibt an, dass ein vorhandener Hinweis, der in der OPTION-Klausel angegeben ist, nicht auf die Abfrage angewendet wird.Indicates that any existing hint specified in the OPTION clause of the query is not applied to the query. Weitere Informationen finden Sie unter OPTION-Klausel (Transact-SQL).For more information, see OPTION Clause (Transact-SQL).

HinweiseRemarks

Die Argumente für sp_create_plan_guide müssen in der angezeigten Reihenfolge bereitgestellt werden.The arguments to sp_create_plan_guide must be provided in the order that is shown. Wenn Sie Werte für die Parameter von sp_create_plan_guideangeben, müssen entweder alle oder überhaupt keine Parameternamen explizit angegeben werden.When you supply values for the parameters of sp_create_plan_guide, all parameter names must be specified explicitly, or none at all. Z. B. wenn @Name = angegeben ist, klicken Sie dann @Stmt = , @Typ = usw., muss auch angegeben werden.For example, if @name = is specified, then @stmt = , @type =, and so on, must also be specified. Auch wenn @Name = nicht angegeben und nur der Parameterwert bereitgestellt wird, die verbleibenden Parameternamen müssen ebenfalls angegeben und nur ihre Werte bereitgestellt.Likewise, if @name = is omitted and only the parameter value is provided, the remaining parameter names must also be omitted, and only their values provided. Argumentnamen dienen nur zu Beschreibungszwecken, zum besseren Verständnis der Syntax.Argument names are for descriptive purposes only, to help understand the syntax. SQL ServerSQL Server überprüft nicht, ob der angegebene Parametername mit dem Namen des Parameters an der Position übereinstimmt, an der der Name verwendet wird. does not verify that the specified parameter name matches the name for the parameter in the position where the name is used.

Sie können mehr als eine Planhinweisliste des Typs OBJECT oder SQL für dieselbe Abfrage und den Batch oder das Modul erstellen.You can create more than one OBJECT or SQL plan guide for the same query and batch or module. Es kann jedoch nur jeweils eine Planhinweisliste aktiviert sein.However, only one plan guide can be enabled at any given time.

Planhinweislisten vom Typ Objekt nicht werden, für erstellt kann eine @Module_or_batch-Wert, der verweist auf eine gespeicherte Prozedur, Funktion oder DML-Trigger, der angibt, die WITH ENCRYPTION-Klausel oder besteht nur vorübergehend.Plan guides of type OBJECT cannot be created for an @module_or_batch value that references a stored procedure, function, or DML trigger that specifies the WITH ENCRYPTION clause or that is temporary.

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.Trying to drop or modify a function, stored procedure, or DML trigger that is referenced by a plan guide, either enabled or disabled, causes an error. Auch der Versuch, eine Tabelle mit einem Trigger zu löschen, auf den eine Planhinweisliste verweist, führt zu einem Fehler.Trying to drop a table that has a trigger defined on it that is referenced by a plan guide also causes an error.

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. Nach dem Serverupgrade sollten Sie in jeder Datenbank prüfen, ob die Planhinweislisten wirklich erwünscht sind.You should verify the desirability of the plan guides in each database after performing a server upgrade.

Voraussetzungen für den PlanhinweislistenabgleichPlan Guide Matching Requirements

Für Planhinweislisten, die angeben, @Type = 'SQL' oder @Type = 'TEMPLATE' entsprechend, dass erfolgreich eine Abfrage, die Werte für Batch_text und @Parameter_name, Data_type[,.. ...n ] müssen in genau das gleiche Format wie die von der Anwendung übermittelten Gegenstücke bereitgestellt werden.For plan guides that specify @type = 'SQL' or @type = 'TEMPLATE' to successfully match a query, the values for batch_text and @parameter_name data_type [,...n ] must be provided in exactly the same format as their counterparts submitted by the application. Das bedeutet, dass Sie den Batchtext genau so bereitstellen müssen, wie er vom SQL ServerSQL Server-Compiler empfangen wird.This means you must provide the batch text exactly as the SQL ServerSQL Server compiler receives it. Mithilfe von SQL Server ProfilerSQL Server Profiler können Sie den eigentlichen Batch- und Parametertext erfassen.To capture the actual batch and parameter text, you can use SQL Server ProfilerSQL Server Profiler. Weitere Informationen finden Sie unter verwenden SQL Server Profiler zum Erstellen und Test-Planhinweislisten.For more information, see Use SQL Server Profiler to Create and Test Plan Guides.

Wenn @Typ = 'SQL' und @Module_or_batch ist auf NULL gesetzt, den Wert der @Module_or_batch festgelegt ist, auf den Wert der @Stmt. Dies bedeutet, dass den Wert für Statement_text muss angegeben werden, in dem genau gleichen Format, Zeichen für Zeichen, wie beim Übermitteln SQL ServerSQL Server.When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt. This means that the value for statement_text must be provided in exactly the same 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 SQL ServerSQL Server entspricht dem Wert Statement_text zu Batch_text und @Parameter_name, Data_type [,.. ...n ], oder wenn @Typ = " Objekt", um den Text der entsprechenden Abfrage in Object_name, die folgenden Zeichenfolgenelemente nicht berücksichtigt:When SQL ServerSQL Server matches the value of statement_text to batch_text and @parameter_name data_type [,...n ], or if @type = ' OBJECT', to the text of the corresponding query inside object_name, the following string elements are not considered:

  • Leerzeichen (Tabstopps, Leerzeichen, Wagenrücklauf oder Zeilenvorschub) innerhalb der Zeichenfolge.White space characters (tabs, spaces, carriage returns, or line feeds) inside the string.

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

  • Nachfolgende SemikolonsTrailing semicolons

    Z. B. SQL ServerSQL Server kann die Statement_text Zeichenfolge N'SELECT * FROM T WHERE a = 10' folgt Batch_text:For example, SQL ServerSQL Server can match the statement_text string N'SELECT * FROM T WHERE a = 10' to the following batch_text:

    N'SELECT *

    FROM T

    WHERE a=10'

    Jedoch dieselbe Zeichenfolge würde nicht zugeordnet werden diese Batch_text:However, the same string would not be matched to this batch_text:

    N'SELECT * FROM T WHERE b = 10'

    SQL ServerSQL Server ignoriert die Zeichen Wagenrücklauf und Zeilenvorschub sowie Leerzeichen in der ersten Abfrage. ignores the carriage return, line feed, and space characters inside the first query. In der zweiten Abfrage wird die Sequenz WHERE b = 10 nicht auf die gleiche Art interpretiert wie WHERE a = 10.In the second query, the sequence WHERE b = 10 is interpreted differently from 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.Matching is case- and accent-sensitive (even when the collation of the database is case-insensitive), except in the case of keywords, where case is insensitive. Bei der Feststellung der Übereinstimmung wird nicht nach verkürzten Formen von Schlüsselwörtern unterschieden.Matching is insensitive to shortened forms of keywords. So werden beispielsweise die Schlüsselwörter EXECUTE, EXEC und execute als gleichwertig angesehen.For example, the keywords EXECUTE, EXEC, and execute are considered equivalent.

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.

BerechtigungenPermissions

Zum Erstellen einer Planhinweisliste vom Typ OBJECT wird die ALTER-Berechtigung für das Objekt benötigt, auf das verwiesen wird.To create a plan guide of type OBJECT, requires ALTER permission on the referenced object. Zum Erstellen einer Planhinweisliste vom Typ SQL oder TEMPLATE wird die ALTER-Berechtigung für die aktuelle Datenbank benötigt.To create a plan guide of type SQL or TEMPLATE, requires ALTER permission on the current database.

BeispieleExamples

A.A. Erstellen einer Planhinweisliste vom Typ OBJECT für eine Abfrage in einer gespeicherten ProzedurCreating a plan guide of type OBJECT for a query in a stored procedure

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.The following example creates a plan guide that matches a query executed in the context of an application-based stored procedure, and applies the OPTIMIZE FOR hint to the query.

Dies ist die gespeicherte Prozedur:Here is the stored procedure:

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:Here is the plan guide created on the query in the stored procedure:

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.B. Erstellen einer Planhinweisliste vom Typ SQL für eine eigenständige AbfrageCreating a plan guide of type SQL for a stand-alone query

Im folgenden Beispiel wird eine Planhinweisliste erstellt, die einer Abfrage in einem Batch zugeordnet wird, der von einer Anwendung übermittelt wird, die die gespeicherte Systemprozedur sp_executesql verwendet.The following example creates a plan guide to match a query in a batch submitted by an application that uses the sp_executesql system stored procedure.

Dies ist der Batch:Here is the 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:To prevent a parallel execution plan from being generated on this query, create the following plan guide:

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.C. Erstellen einer Planhinweisliste vom Typ TEMPLATE für die parametrisierte Form einer AbfrageCreating a plan guide of type TEMPLATE for the parameterized form of a query

Im folgenden Beispiel wird eine Planhinweisliste erstellt, die mit einer beliebigen Abfrage übereinstimmt, die in einer bestimmten Form parametrisiert wird. Außerdem wird SQL ServerSQL Server angewiesen, die Parametrisierung der Abfrage zu erzwingen.The following example creates a plan guide that matches any query that parameterizes to a specified form, and directs SQL ServerSQL Server to force parameterization of the query. Die folgenden beiden Abfragen sind syntaktisch gleichwertig, unterscheiden sich jedoch in ihren konstanten Literalwerten.The following two queries are syntactically equivalent, but differ only in their constant literal values.

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

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

Dies ist die Planhinweisliste für die parametrisierte Form der Abfrage:Here is the plan guide on the parameterized form of the query:

EXEC sp_create_plan_guide   
    @name = N'TemplateGuide1',  
    @stmt = N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2012.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.In the previous example, the value for the @stmt parameter is the parameterized form of the query. 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 .The only reliable way to obtain this value for use in sp_create_plan_guide is to use the sp_get_query_template system stored procedure. Mithilfe des folgenden Skripts können Sie die parametrisierte Abfrage abrufen und anschließend eine Planhinweisliste für die Abfrage erstellen.The following script can be used both to obtain the parameterized query and then create a plan guide on it.

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2012.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)';  

Wichtig

Der Wert der konstanten Literale in dem an sp_get_query_template übergebenen @stmt-Parameter kann sich auf den Datentyp auswirken, der für den Parameter, der das Literal ersetzt, gewählt wird.The value of the constant literals in the @stmt parameter passed to sp_get_query_template might affect the data type that is chosen for the parameter that replaces the literal. Dies wiederum beeinflusst den Planhinweislistenabgleich.This will affect plan guide matching. Möglicherweise müssen mehrere Planhinweislisten für verschiedene Parameterwertbereiche erstellt werden.You may have to create more than one plan guide to handle different parameter value ranges.

D.D. Erstellen einer Planhinweisliste für eine Abfrage, die über eine API-Cursoranforderung übermittelt wirdCreating a plan guide on a query submitted by using an API cursor request

Planhinweislisten können Übereinstimmungen für Abfragen feststellen, die von API-Servercursorroutinen übermittelt werden.Plan guides can match queries that are submitted from API server cursor routines. Zu diesen Routinen gehören sp_cursorprepare, sp_cursorprepexec und sp_cursoropen.These routines include sp_cursorprepare, sp_cursorprepexec, and sp_cursoropen. Anwendungen, die ADO-, OLE DB- und ODBC-APIs verwenden, arbeiten häufig mithilfe von API-Servercursorn mit SQL ServerSQL Server zusammen.Applications that use the ADO, OLE DB, and ODBC APIs frequently interact with SQL ServerSQL Server by using API server cursors. Das Aufrufen von API-Servercursorroutinen in SQL Server ProfilerSQL Server Profiler-Ablaufverfolgungen kann mithilfe des RPC:Starting-Ablaufverfolgungsereignisses angezeigt werden.You can see the invocation of API server cursor routines in SQL Server ProfilerSQL Server Profiler traces by viewing the RPC:Starting profiler trace event.

Angenommen, die folgenden Daten werden in einem RPC:Starting-Ablaufverfolgungsereignis für eine Abfrage angezeigt, die mithilfe einer Planhinweisliste optimiert werden soll:Suppose the following data appears in an RPC:Starting profiler trace event for a query you want to tune with a plan guide:

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.You notice that the plan for the SELECT query in the call to sp_cursorprepexec is using a merge join, but you want to use a hash join. Die mithilfe von sp_cursorprepexec übermittelte Abfrage ist parametrisiert, einschließlich einer Abfragezeichenfolge und einer Parameterzeichenfolge.The query submitted by using sp_cursorprepexec is parameterized, including both a query string and a parameter string. 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.You can create the following plan guide to change the choice of plan by using the query and parameter strings exactly as they appear, character for character, in the call to sp_cursorprepexec.

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.Subsequent executions of this query by the application will be affected by this plan guide, and a hash join will be used to process the query.

E.E. Erstellen einer Planhinweisliste durch Abrufen des XML-Showplans aus einem zwischengespeicherten PlanCreating a plan guide by obtaining the XML Showplan from a cached plan

Im folgenden Beispiel wird eine Planhinweisliste für eine einfache Ad-hoc-SQL-Anweisung erstellt.The following example creates a plan guide for a simple ad hoc SQL statement. 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.The desired query plan for this statement is provided in the plan guide by specifying the XML Showplan for the query directly in the @hints parameter. Im Beispiel wird zunächst die SQL-Anweisung ausgeführt, um einen Plan im Plancache zu erzeugen.The example first executes the SQL statement to generate a plan in the plan cache. Dabei wird davon ausgegangen, dass der erzeugte Plan dem gewünschten Plan entspricht und keine weitere Optimierung der Abfrage erforderlich ist.For the purposes of this example, it is assumed that the generated plan is the desired plan and no additional query tuning is required. 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.The XML Showplan for the query is obtained by querying the sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_text_query_plan dynamic management views and is assigned to the @xml_showplan variable. Die @xml_showplan -Variable wird dann im sp_create_plan_guide -Parameter an die @hints -Anweisung übergeben.The @xml_showplan variable is then passed to the sp_create_plan_guide statement in the @hints parameter. Alternativ können Sie die gespeicherte Prozedur sp_create_plan_guide_from_handle verwenden, um eine Planhinweisliste aus einem Abfrageplan im Plancache zu erstellen.Or, you can create a plan guide from a query plan in the plan cache by using the sp_create_plan_guide_from_handle stored procedure.

USE AdventureWorks2012;  
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  

Siehe auchSee Also

Planhinweislisten Plan Guides
sp_control_plan_guide (Transact-SQL) sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL) sys.plan_guides (Transact-SQL)
Datenbank-Engine gespeicherten Prozeduren (Transact-SQL) Database Engine Stored Procedures (Transact-SQL)
Gespeicherte Systemprozeduren (Transact-SQL) System Stored Procedures (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL) sys.dm_exec_sql_text (Transact-SQL)
dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL) sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL) sp_create_plan_guide_from_handle (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL) sys.fn_validate_plan_guide (Transact-SQL)
sp_get_query_template (Transact-SQL)sp_get_query_template (Transact-SQL)