sys.sp_create_plan_guide_from_handle (Transact-SQL)

Gäller för:SQL Server

Skapar en eller flera planguider från en frågeplan i plancachen. Du kan använda denna lagrade procedur för att säkerställa att frågeoptimeraren alltid använder en specifik frågeplan för en viss fråga. Mer information om planguider finns i Planguider.

Transact-SQL syntaxkonventioner

Syntax

sys.sp_create_plan_guide_from_handle
    [ @name = ] N'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

Arguments

[ @name = ] N'namn'

Namnet på planguiden. @name är sysname, utan standard. Planguidenamn är begränsade till den aktuella databasen. @name måste följa reglerna för identifierare och kan inte börja med nummertecknet (#). Den maximala längden på @name är 124 tecken.

[ @plan_handle = ] plan_handle

Identifierar en batch i plancachen. @plan_handle är varbinär(64) utan standard. @plan_handle kan erhållas från den sys.dm_exec_query_stats dynamiska hanteringsvyn.

[ @statement_start_offset = ] statement_start_offset

Identifierar startpositionen för satsen inom batchen av den specificerade @plan_handle. @statement_start_offset är int, med standardvärdet .NULL

Satsförskjutningen motsvarar kolumnen statement_start_offset i sys.dm_exec_query_stats dynamisk hanteringsvy.

När NULL anges eller en satsoffset inte specificeras, skapas en planguide för varje sats i batchen med hjälp av frågeplanen för det angivna planhandtaget. De resulterande planguiderna är ekvivalenta med planguider som använder USE PLAN frågeledtråden för att tvinga fram användning av en specifik plan.

Remarks

En planguide kan inte skapas för alla typer av uttalanden. Om en planguide inte kan skapas för ett sats i batchen, ignorerar den lagrade proceduren satsen och fortsätter till nästa sats i batchen. Om ett uttalande sker flera gånger i samma batch aktiveras planen för den senaste händelsen och tidigare planer för utsagan inaktiveras. Om inga satser i batchen kan användas i en planguide aktiveras fel 10532 och satsen misslyckas. Vi rekommenderar att du alltid hämtar planhandtaget från dynamisk hanteringsvy sys.dm_exec_query_stats för att undvika risken för detta fel.

Skapa planguider för flera påståenden inom en frågeplan

Som sp_create_plan_guide, sp_create_plan_guide_from_handle tar bort frågeplanen för den utvalda batchen eller modulen från plancachen. Detta görs för att säkerställa att alla användare börjar använda den nya planguiden. När du skapar en planguide för flera satser inom en och samma frågeplan kan du skjuta upp borttagandet av planen från cachen genom att skapa alla planguider i en explicit transaktion. Denna metod tillåter att planen kan stanna kvar i cachen tills transaktionen är klar och en planguide för varje specificerad sats skapas. Se exempel B.

Permissions

Kräver VIEW SERVER STATE behörighet. Dessutom krävs individuella behörigheter för varje planguide som skapas med hjälp av sp_create_plan_guide_from_handle. Att skapa en planguide av typen OBJECT kräver ALTER behörighet på det refererade objektet. Att skapa en planguide av typ SQL kräver ALTER behörighet TEMPLATE i den aktuella databasen. För att bestämma vilken typ av planguide som ska skapas, kör följande fråga:

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;

I raden som innehåller satsen för vilken du skapar planguiden, undersök kolumnen objtype i resultatuppsättningen. Ett värde på Proc indikerar att planguiden är av typen OBJECT. Andra värden såsom Ad hoc eller Prepared indikerar att planguiden är av typen SQL.

Examples

A. Skapa en planguide från en frågeplan i plancachen

Följande exempel skapar en planguide för en enskild SELECT sats genom att specificera en frågeplan från plancachen. Exemplet börjar med att utföra en enkel SELECT sats för vilken planguiden ska skapas. Planen för denna fråga granskas genom att använda dynamiska sys.dm_exec_sql_textsys.dm_exec_text_query_plan hanteringsvyer. Planguiden skapas sedan för frågan genom att specificera frågeplanen i plancachen som är kopplad till frågan. Det sista påståendet i exemplet verifierar att planguiden existerar.

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. Skapa flera planguider för en multistatement-batch

Följande exempel skapar en planguide för två satser inom en multistatement-batch. Planguiderna skapas inom en explicit transaktion så att frågeplanen för batchen inte tas bort från plancachen efter att den första planguiden har skapats. Exemplet börjar med att köra en multistatement-batch. Planen för batchen undersöks med dynamiska hanteringsvyer. En rad för varje sats i batchen returneras. En planguide skapas sedan för det första och tredje satsen i batchen genom att specificera parametern @statement_start_offset . Det sista påståendet i exemplet bekräftar att planguiderna finns.

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

Relaterat innehåll

• lagrade procedurer för databasmotorn (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• Planguider
• 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)