sp_create_plan_guide_from_handle (Transact-SQL)sp_create_plan_guide_from_handle (Transact-SQL)

Gilt für: JaSQL Server NeinAzure SQL-Datenbank NeinAzure Synapse Analytics (SQL DW) NeinParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Erstellt eine oder mehrere Planhinweislisten aus einem Abfrageplan im Plancache.Creates one or more plan guides from a query plan in the plan cache. Sie können diese gespeicherte Prozedur verwenden, um sicherzustellen, dass der Abfrageoptimierer einen bestimmten Abfrageplan für eine bestimmte Abfrage verwendet.You can use this stored procedure to ensure the query optimizer always uses a specific query plan for a specified query. 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_from_handle [ @name = ] N'plan_guide_name'  
    , [ @plan_handle = ] plan_handle  
    , [ [ @statement_start_offset = ] { statement_start_offset | 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.

[ @plan_handle =] Plan_handle[ @plan_handle = ] plan_handle
Identifiziert einen Batch im Plancache.Identifies a batch in the plan cache. plan_handle ist varbinary(64)plan_handle is varbinary(64). Plan_handle abgerufen werden kann, aus der Sys. dm_exec_query_stats dynamische verwaltungssicht.plan_handle can be obtained from the sys.dm_exec_query_stats dynamic management view.

[ @statement_start_offset =] { Statement_start_offset | NULL}][ @statement_start_offset = ] { statement_start_offset | NULL } ]
Identifiziert die Anfangsposition der Anweisung innerhalb des Batches des angegebenen Plan_handle.Identifies the starting position of the statement within the batch of the specified plan_handle. Statement_start_offset ist Int, hat den Standardwert NULL.statement_start_offset is int, with a default of NULL.

Der anweisungsoffset entspricht, auf die Spalte Statement_start_offset in der Sys. dm_exec_query_stats dynamische verwaltungssicht.The statement offset corresponds to the statement_start_offset column in the sys.dm_exec_query_stats dynamic management view.

Wenn NULL angegeben ist oder kein Anweisungsoffset angegeben ist, wird eine Planhinweisliste für jede Anweisung im Batch unter Verwendung des Abfrageplans für das angegebene Planhandle erstellt.When NULL is specified or a statement offset is not specified, a plan guide is created for each statement in the batch using the query plan for the specified plan handle. Die daraus resultierenden Planhinweislisten entsprechen den Planhinweislisten, die mit dem USE PLAN-Abfragehinweis die Verwendung eines bestimmten Plans erzwingen.The resulting plan guides are equivalent to plan guides that use the USE PLAN query hint to force the use of a specific plan.

HinweiseRemarks

Planhinweislisten können nicht für alle Anweisungstypen erstellt werden.A plan guide cannot be created for all statement types. Wenn für eine Anweisung im Batch keine Planhinweisliste erstellt werden kann, ignoriert die gespeicherte Prozedur die Anweisung und fährt mit der nächsten Anweisung im Batch fort.If a plan guide cannot be created for a statement in the batch, the stored procedure ignores the statement and continues to the next statement in the batch. Wenn eine Anweisung mehrfach im selben Batch vorkommt, wird der Plan für das letzte Vorkommen aktiviert, und die vorherigen Pläne für die Anweisung werden deaktiviert.If a statement occurs multiple times in the same batch, the plan for the last occurrence is enabled and previous plans for the statement are disabled. Wenn in einer Planhinweisliste keine Anweisungen im Batch verwendet werden können, wird Fehler 10532 ausgegeben, und die Anweisung schlägt fehl.If no statements in the batch can be used in a plan guide, error 10532 is raised and the statement fails. Es wird empfohlen, das Planhandle immer aus der dynamischen Verwaltungssicht sys.dm_exec_query_stats abzurufen, um das Auftreten dieses Fehlers zu verhindern.We recommend that you always obtain the plan handle from the sys.dm_exec_query_stats dynamic management view to help avoid the possibility of this error.

Wichtig

sp_create_plan_guide_from_handle erstellt Planhinweislisten auf der Basis von Plänen, wie sie im Plancache angezeigt werden.sp_create_plan_guide_from_handle creates plan guides based on plans as they appear in the plan cache. Das bedeutet, dass der Batchtext, Transact-SQLTransact-SQL-Anweisungen und XML-Showplan Zeichen für Zeichen (einschließlich aller an die Abfrage übergebenen Literalwerte) aus dem Plancache in die resultierende Planhinweisliste übertragen werden.This means that the batch text, Transact-SQLTransact-SQL statements, and XML Showplan are taken character-by-character (including any literal values passed to the query) from the plan cache into the resulting plan guide. Diese Textzeichenfolgen enthalten möglicherweise vertrauliche Informationen, die dann in den Metadaten der Datenbank gespeichert werden.These text strings may contain sensitive information that is then stored in the metadata of the database. Benutzer mit entsprechenden Berechtigungen können diese Informationen anzeigen, indem Sie mit der Sys. plan_guides-Katalogsicht und die Eigenschaften der Planhinweisliste im Dialogfeld SQL Server Management StudioSQL Server Management Studio.Users with appropriate permissions can view this information by using the sys.plan_guides catalog view and the Plan Guide Properties dialog box in SQL Server Management StudioSQL Server Management Studio. Um sicherzustellen, dass vertrauliche Informationen nicht über eine Planhinweisliste offen gelegt werden, wird empfohlen, die aus dem Plancache erstellten Planhinweislisten zu überprüfen.To ensure that sensitive information is not disclosed through a plan guide, we recommend reviewing the plan guides created from the plan cache.

Erstellen von Planhinweislisten für mehrere Anweisungen innerhalb eines AbfrageplansCreating Plan Guides for Multiple Statements Within a Query Plan

sp_create_plan_guide_from_handle entfernt (wie sp_create_plan_guide) den Abfrageplan für den Zielbatch oder das Zielmodul aus dem Plancache.Like sp_create_plan_guide, sp_create_plan_guide_from_handle removes the query plan for the targeted batch or module from the plan cache. Dies geschieht, um sicherzustellen, dass alle Benutzer die neue Planhinweisliste verwenden.This is done to ensure that all users begin using the new plan guide. Beim Erstellen einer Planhinweisliste für mehrere Anweisungen innerhalb eines einzelnen Abfrageplans können Sie das Entfernen des Plans aus dem Cache verzögern, indem Sie alle Planhinweislisten in einer expliziten Transaktion erstellen.When creating a plan guide for multiple statements within a single query plan, you can postpone the removal of the plan from the cache by creating all the plan guides in an explicit transaction. Bei Verwendung dieser Methode bleibt der Plan so lange im Cache, bis die Transaktion abgeschlossen ist und eine Planhinweisliste für jede angegebene Anweisung erstellt ist.This method allows the plan to remain in the cache until the transaction is complete and a plan guide for each specified statement is created. Siehe Beispiel B.See Example B.

BerechtigungenPermissions

Erfordert die VIEW SERVER STATE-Berechtigung.Requires VIEW SERVER STATE permission. Außerdem sind einzelne Berechtigungen für jede Planhinweisliste erforderlich, die unter Verwendung von sp_create_plan_guide_from_handle erstellt wird.In addition, individual permissions are required for each plan guide that is created by using sp_create_plan_guide_from_handle. Zum Erstellen einer Planhinweisliste vom Typ erfordert Objekt ALTER-Berechtigung für das Objekt 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. Um den Typ der erstellten Planhinweislistentyp zu bestimmen, führen Sie die folgende Abfrage aus:To determine the plan guide type that will be created, run the following query:

SELECT cp.plan_handle, sql_handle, st.text, objtype   
FROM sys.dm_exec_cached_plans AS cp  
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st;  

Untersuchen Sie in der Zeile mit der Anweisung, für die Sie die Planhinweisliste erstellen, die objtype-Spalte im Resultset.In the row that contains the statement for which you are creating the plan guide, examine the objtype column in the result set. Der Wert Proc gibt an, dass die Planhinweisliste den Typ OBJECT hat.A value of Proc indicates the plan guide is of type OBJECT. Andere Werte, wie z. B. AdHoc oder Prepared, geben an, dass die Planhinweisliste den Typ SQL hat.Other values such as AdHoc or Prepared indicate the plan guide is of type SQL.

BeispieleExamples

A.A. Erstellen einer Planhinweisliste aus einem Abfrageplan im PlancacheCreating a plan guide from a query plan in the plan cache

Im folgenden Beispiel wird eine Planhinweisliste für eine einzelne SELECT-Anweisung erstellt, indem ein Abfrageplan aus dem Plancache angegeben wird.The following example creates a plan guide for a single SELECT statement by specifying a query plan from the plan cache. In diesem Beispiel wird zuerst eine einfache SELECT-Anweisung ausgeführt, für die die Planhinweisliste erstellt werden soll.The example begins by executing a simple SELECT statement for which the plan guide will be created. Der Plan für diese Abfrage wird unter Verwendung der dynamischen Verwaltungssichten sys.dm_exec_sql_text und sys.dm_exec_text_query_plan untersucht.The plan for this query is examined by using the sys.dm_exec_sql_text and sys.dm_exec_text_query_plan dynamic management views. Anschließend wird die Planhinweisliste für die Abfrage erstellt, indem der Abfrageplan in dem Plancache angegeben wird, der der Abfrage zugeordnet ist.The plan guide is then created for the query by specifying the query plan in the plan cache that is associated with the query. Die abschließende Anweisung in dem Beispiel überprüft, dass die Planhinweisliste vorhanden ist.The final statement in the example verifies that the plan guide exists.

USE AdventureWorks2012;  
GO  
SELECT WorkOrderID, p.Name, OrderQty, DueDate  
FROM Production.WorkOrder AS w   
JOIN Production.Product AS p ON w.ProductID = p.ProductID  
WHERE p.ProductSubcategoryID > 4  
ORDER BY p.Name, DueDate;  
GO  
-- Inspect the query plan by using dynamic management views.  
SELECT * FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp  
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
GO  
-- Create a plan guide for the query by specifying the query plan in the plan cache.  
DECLARE @plan_handle varbinary(64);  
DECLARE @offset int;  
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st  
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp  
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
  
EXECUTE sp_create_plan_guide_from_handle   
    @name =  N'Guide1',  
    @plan_handle = @plan_handle,  
    @statement_start_offset = @offset;  
GO  
-- Verify that the plan guide is created.  
SELECT * FROM sys.plan_guides  
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
GO  

B.B. Erstellen von mehreren Planhinweislisten für einen Batch mit mehreren AnweisungenCreating multiple plan guides for a multistatement batch

Im folgenden Beispiel wird eine Planhinweisliste für zwei Anweisungen innerhalb eines Batches mit mehreren Anweisungen erstellt.The following example creates a plan guide for two statements within a multistatement batch. Die Planhinweislisten werden innerhalb einer expliziten Transaktion erstellt, damit der Abfrageplan für den Batch erst dann aus dem Plancache entfernt wird, nachdem die erste Planhinweisliste erstellt wurde.The plan guides are created within an explicit transaction so that the query plan for the batch is not removed from the plan cache after the first plan guide is created. Im Beispiel wird zuerst ein Batch mit mehreren Anweisungen ausgeführt.The example begins by executing a multistatement batch. Der Plan für den Batch wird unter Verwendung der dynamischen Verwaltungssichten untersucht.The plan for the batch is examined by using dynamic management views. Beachten Sie, dass eine Zeile für jede Anweisung im Batch zurückgegeben wird.Notice that a row for each statement in the batch is returned. Anschließend wird eine Planhinweisliste für die erste und die dritte Anweisung in dem Batch durch Angabe des @statement_start_offset-Parameters erstellt.A plan guide is then created for the first and third statements in the batch by specifying the @statement_start_offset parameter. Die abschließende Anweisung in dem Beispiel überprüft, dass die Planhinweislisten vorhanden sind.The final statement in the example verifies that the plan guides exist.

USE AdventureWorks2012;
GO
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4;
SELECT * FROM Person.Address;
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10;
GO

-- Examine the query plans for this batch
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%';
GO

-- Create plan guides for the first and third statements in the batch by specifying the statement offsets.
BEGIN TRANSACTION

DECLARE @plan_handle varbinary(64);
DECLARE @offset int;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement1_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement3_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

COMMIT TRANSACTION
GO

-- Verify the plan guides are created.
SELECT * FROM sys.plan_guides;
GO

Siehe auchSee Also

Datenbank-Engine gespeicherten Prozeduren (Transact-SQL) Database Engine Stored Procedures (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL) sys.dm_exec_query_stats (Transact-SQL)
Planhinweislisten Plan Guides
sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL) sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_text_query_plan (Transact-SQL) sys.dm_exec_text_query_plan (Transact-SQL)
sp_control_plan_guide (Transact-SQL)sp_control_plan_guide (Transact-SQL)