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'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

Argumente

[ @name = ] N'name'

Der Name des Planleitfadens. @name ist "sysname" ohne Standard. Die Gültigkeit der Namen von Planhinweislisten beschränkt sich auf die aktuelle Datenbank. @name müssen die Regeln für Bezeichner einhalten und dürfen nicht mit dem Nummernzeichen (#) beginnen. Die maximale Länge von @name beträgt 124 Zeichen.

[ @plan_handle = ] plan_handle

Identifiziert einen Batch im Plancache. @plan_handle ist varbinary(64) ohne Standard. @plan_handle können aus der sys.dm_exec_query_stats dynamischen Verwaltungsansicht abgerufen werden.

[ @statement_start_offset = ] statement_start_offset

Identifiziert die Startposition der Anweisung innerhalb des Batches der angegebenen @plan_handle. @statement_start_offset ist int mit einem Standardwert von NULL.

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

Wenn NULL angegeben oder kein Anweisungsoffset angegeben wird, wird für jede Anweisung im Batch mithilfe des Abfrageplans für den angegebenen Planhandle ein Planleitfaden erstellt. Die resultierenden Planhandbücher entsprechen Planhandbüchern, die den USE PLAN Abfragehinweis verwenden, um die Verwendung eines bestimmten Plans zu erzwingen.

Hinweise

Ein Planleitfaden kann nicht für alle Anweisungstypen erstellt werden. Wenn kein Planleitfaden für eine Anweisung im Batch 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, immer den Planhandle aus der sys.dm_exec_query_stats dynamischen Verwaltungsansicht zu erhalten, um die Möglichkeit dieses Fehlers zu vermeiden.

Wichtig

sp_create_plan_guide_from_handle erstellt Planhandbücher basierend auf Plänen, wie sie im Plancache angezeigt werden. Dies bedeutet, dass der Batchtext, Transact-SQL-Anweisungen und XML Showplan zeichenweise (einschließlich aller Literalwerte, die an die Abfrage übergeben werden) aus dem Plancache in die resultierende Plananleitung übernommen werden. Diese Textzeichenfolgen können vertrauliche Informationen enthalten, die dann in den Metadaten der Datenbank gespeichert werden. Benutzer mit entsprechenden Berechtigungen können diese Informationen mithilfe der sys.plan_guides Katalogansicht und des Dialogfelds "Eigenschaften der Planhandbuch " in SQL Server Management Studio anzeigen. Um sicherzustellen, dass vertrauliche Informationen nicht über einen Planleitfaden offengelegt werden, empfehlen wir, die im Plancache erstellten Planhandbücher zu überprüfen.

Erstellen von Planhandbüchern für mehrere Anweisungen innerhalb eines Abfrageplans

Entfernt wie sp_create_plan_guidefolgt sp_create_plan_guide_from_handle den Abfrageplan für den zielbezogenen Batch 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. Darüber hinaus sind individuelle Berechtigungen für jeden Planleitfaden erforderlich, der mit der Verwendung sp_create_plan_guide_from_handleerstellt wird. Zum Erstellen eines Planleitfadens des Typs OBJECT ist die Berechtigung für das referenzierte Objekt erforderlich ALTER . Erstellen eines Planleitfadens vom Typ SQL oder TEMPLATE erfordert ALTER Berechtigungen für die aktuelle Datenbank. 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
INNER 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;

Überprüfen Sie in der Zeile, in der die Anweisung enthalten ist, für die Sie die Plananleitung erstellen, die objtype Spalte im Resultset. Der Wert Proc gibt an, dass die Planhinweisliste den Typ OBJECT hat. Andere Werte, wie z. B. Ad hoc oder Prepared, geben an, dass die Planhinweisliste den Typ SQL hat.

Beispiele

A. Erstellen eines Planleitfadens aus einem Abfrageplan im Plancache

Im folgenden Beispiel wird ein Planleitfaden 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
INNER 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 mehrerer Planhandbücher für einen Mehrinstanzenbatch

Im folgenden Beispiel wird eine Planhinweisliste für zwei Anweisungen innerhalb eines Batches mit mehreren Anweisungen erstellt. Die Planhandbücher werden innerhalb einer expliziten Transaktion erstellt, sodass der Abfrageplan für den Batch nach der Erstellung des ersten Planleitfadens nicht aus dem Plancache entfernt wird. Im Beispiel wird zuerst ein Batch mit mehreren Anweisungen ausgeführt. Der Plan für den Batch wird unter Verwendung der dynamischen Verwaltungssichten untersucht. Es wird eine Zeile für jede Anweisung im Batch zurückgegeben. 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

Zugehöriger Inhalt

• Datenbank-Engine gespeicherten Prozeduren (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)