sp_create_plan_guide_from_handle (Transact-SQL)

Gilt für:SQL Server

Erstellt eine oder mehrere Planhinweislisten aus einem Abfrageplan im Plancache. Sie können diese gespeicherte Prozedur verwenden, um sicherzustellen, dass der Abfrageoptimierer einen bestimmten Abfrageplan für eine bestimmte Abfrage verwendet. Weitere Informationen zu Planhinweislisten finden Sie unter Planhinweislisten.

Transact-SQL-Syntaxkonventionen

Syntax

sp_create_plan_guide_from_handle [ @name = ] N'plan_guide_name'  
    , [ @plan_handle = ] plan_handle  
    , [ [ @statement_start_offset = ] { statement_start_offset | 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 den Regeln für Bezeichner entsprechen und darf nicht mit dem Nummernzeichen (#) beginnen. Die maximale Länge von plan_guide_name beträgt 124 Zeichen.

[ @plan_handle = ] plan_handle
Identifiziert einen Batch im Plancache. plan_handle ist varbinary(64) plan_handle können Sie in der dynamischen Verwaltungsansicht sys.dm_exec_query_stats abrufen.

[ @statement_start_offset = ] { statement_start_offset | NULL } ]
Gibt die Startposition der Anweisung im Batch der angegebenen plan_handle an. statement_start_offset ist int mit dem Standardwert NULL.

Der Anweisungsoffset entspricht der Spalte statement_start_offset in der sys.dm_exec_query_stats dynamischen Verwaltungsansicht.

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. Die daraus resultierenden Planhinweislisten entsprechen den Planhinweislisten, die mit dem USE PLAN-Abfragehinweis die Verwendung eines bestimmten Plans erzwingen.

Bemerkungen

Planhinweislisten können nicht für alle Anweisungstypen erstellt werden. 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. 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. Wenn in einer Planhinweisliste keine Anweisungen im Batch verwendet werden können, wird Fehler 10532 ausgegeben, und die Anweisung schlägt fehl. Es wird empfohlen, das Planhandle immer aus der dynamischen Verwaltungssicht sys.dm_exec_query_stats abzurufen, um das Auftreten dieses Fehlers zu verhindern.

Erstellen von Planhinweislisten für mehrere Anweisungen innerhalb eines Abfrageplans

sp_create_plan_guide_from_handle entfernt (wie sp_create_plan_guide) den Abfrageplan für den Zielbatch oder das Zielmodul aus dem Plancache. Dies geschieht, um sicherzustellen, dass alle Benutzer die neue Planhinweisliste verwenden. 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. 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. Siehe Beispiel B.

Berechtigungen

Erfordert die VIEW SERVER STATE-Berechtigung. Außerdem sind einzelne Berechtigungen für jede Planhinweisliste erforderlich, die unter Verwendung von sp_create_plan_guide_from_handle erstellt wird. Zum Erstellen einer Planhinweisliste vom Typ OBJECT ist eine Berechtigung für das Objekt erforderlich ALTER , auf das verwiesen wird. Zum Erstellen einer Planhinweisliste vom Typ SQL oder TEMPLATE ist die Berechtigung für die aktuelle Datenbank erforderlich ALTER . Um den Typ der erstellten Planhinweislistentyp zu bestimmen, führen Sie die folgende Abfrage aus:

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. Der Wert Proc gibt an, dass die Planhinweisliste den Typ OBJECT hat. Andere Werte, wie z. B. AdHoc oder Prepared, geben an, dass die Planhinweisliste den Typ SQL hat.

Beispiele

A. Erstellen einer Planhinweisliste aus einem Abfrageplan im Plancache

Im folgenden Beispiel wird eine Planhinweisliste für eine einzelne SELECT-Anweisung erstellt, indem ein Abfrageplan aus dem Plancache angegeben wird. In diesem Beispiel wird zuerst eine einfache SELECT-Anweisung ausgeführt, für die die Planhinweisliste erstellt werden soll. 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. Anschließend wird die Planhinweisliste für die Abfrage erstellt, indem der Abfrageplan in dem Plancache angegeben wird, der der Abfrage zugeordnet ist. Die abschließende Anweisung in dem Beispiel überprüft, dass die Planhinweisliste vorhanden ist.

USE AdventureWorks2022;  
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. Erstellen von mehreren Planhinweislisten für einen Batch mit mehreren Anweisungen

Im folgenden Beispiel wird eine Planhinweisliste für zwei Anweisungen innerhalb eines Batches mit mehreren Anweisungen erstellt. 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. Im Beispiel wird zuerst ein Batch mit mehreren Anweisungen ausgeführt. Der Plan für den Batch wird unter Verwendung der dynamischen Verwaltungssichten untersucht. Beachten Sie, dass eine Zeile für jede Anweisung im Batch zurückgegeben wird. Anschließend wird eine Planhinweisliste für die erste und die dritte Anweisung in dem Batch durch Angabe des @statement_start_offset-Parameters erstellt. Die abschließende Anweisung in dem Beispiel überprüft, dass die Planhinweislisten vorhanden sind.

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

Weitere Informationen

Gespeicherte Prozeduren für die Datenbank-Engine (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
Planhinweislisten
sp_create_plan_guide (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_text_query_plan (Transact-SQL)
sp_control_plan_guide (Transact-SQL)