sys.sp_create_plan_guide_from_handle (Transact-SQL)

Van toepassing op:SQL Server

Hiermee maakt u een of meer planhandleidingen van een queryplan in de plancache. Je kunt deze opgeslagen procedure gebruiken om ervoor te zorgen dat de query-optimizer altijd een specifiek queryplan gebruikt voor een bepaalde query. Zie Planhandleidingen voor meer informatie over planhandleidingen.

Transact-SQL syntaxis-conventies

Syntax

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

Arguments

[ @name = ] Naamloos

De naam van de planhandleiding. @nameis sysname, zonder standaardinstelling. Namen van plangidsen zijn gericht op de huidige database. @name moet voldoen aan de regels voor id's en mag niet beginnen met het nummerteken (#). De maximale lengte van @name is 124 tekens.

[ @plan_handle = ] plan_handle

Identificeert een batch in de plancache. @plan_handle is varbinair(64), zonder standaard. @plan_handle kan worden verkregen vanuit de sys.dm_exec_query_stats dynamische beheerweergave.

[ @statement_start_offset = ] statement_start_offset

Identificeert de startpositie van de verklaring binnen de batch van de gespecificeerde @plan_handle. @statement_start_offset is int, met als standaard .NULL

De statement-offset komt overeen met de statement_start_offset kolom in de dynamische beheerweergave van sys.dm_exec_query_stats .

Wanneer NULL gespecificeerd is of een statement-offset niet is gespecificeerd, wordt voor elke instructie in de batch een plangids gemaakt met behulp van het queryplan voor de gespecificeerde planhandvat. De resulterende plangidsen zijn gelijk aan plangidsen die de USE PLAN queryhint gebruiken om het gebruik van een specifiek plan af te dwingen.

Remarks

Een plangids kan niet voor alle soorten statements worden gemaakt. Als er geen plangids kan worden gemaakt voor een instructie in de batch, negeert de opgeslagen procedure de instructie en gaat door naar de volgende instructie in de batch. Als een statement meerdere keren in dezelfde batch voorkomt, wordt het plan voor het laatste incident ingeschakeld en eerdere plannen voor de statement uitgeschakeld. Als er geen statements in de batch in een plangids kunnen worden gebruikt, wordt fout 10532 geactiveerd en faalt de statement. We raden aan om altijd het planhandvat uit de sys.dm_exec_query_stats dynamische beheerweergave te halen om de mogelijkheid van deze fout te voorkomen.

Maak plangidsen voor meerdere statements binnen een queryplan

Zoals sp_create_plan_guide, sp_create_plan_guide_from_handle verwijdert het queryplan voor de beoogde batch of module uit de plancache. Dit wordt gedaan om ervoor te zorgen dat alle gebruikers de nieuwe plangids gaan gebruiken. Bij het maken van een plangids voor meerdere statements binnen één queryplan, kun je het verwijderen van het plan uit de cache uitstellen door alle plangidsen in een expliciete transactie te maken. Deze methode maakt het mogelijk dat het plan in de cache blijft totdat de transactie is voltooid en er voor elke opgegeven instructie een plangids wordt gemaakt. Zie voorbeeld B.

Permissions

Hiervoor is VIEW SERVER STATE machtiging vereist. Daarnaast zijn individuele rechten vereist voor elke plangids die wordt gemaakt door gebruik te maken sp_create_plan_guide_from_handlevan . Het maken van een plangids van het type OBJECT vereist ALTER toestemming voor het gerefereerde object. Het maken van een plangids van het type SQL vereist ALTER toestemming TEMPLATE voor de huidige database. Om het type plangids te bepalen dat wordt gemaakt, voer je de volgende query uit:

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;

In de rij die de verklaring bevat waarvoor je de plangids maakt, bekijk je de objtype kolom in de resultaatset. Een waarde van Proc geeft aan dat de plangids van het type OBJECT is. Andere waarden zoals Ad hoc of Prepared geven aan dat de plangids van het type SQL is.

Examples

A. Maak een plangids aan vanuit een queryplan in de plancache

Het volgende voorbeeld creëert een plangids voor één enkele SELECT statement door een queryplan uit de plancache te specificeren. Het voorbeeld begint met het uitvoeren van een eenvoudige SELECT instructie waarvoor de plangids zal worden gemaakt. Het plan voor deze query wordt onderzocht met behulp van de sys.dm_exec_sql_textsys.dm_exec_text_query_plan dynamische beheerweergaven. De plangids wordt vervolgens voor de query gemaakt door het queryplan te specificeren in de plancache die aan de query is gekoppeld. De laatste uitspraak in het voorbeeld bevestigt dat de plangids bestaat.

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. Maak meerdere plangidsen voor een multistatement-batch

Het volgende voorbeeld creëert een plangids voor twee statements binnen een multistatement-batch. De plangidsen worden gemaakt binnen een expliciete transactie zodat het queryplan voor de batch niet uit de plancache wordt verwijderd nadat de eerste plangids is gemaakt. Het voorbeeld begint met het uitvoeren van een batch met meerdere uitspraken. Het plan voor de batch wordt onderzocht met behulp van dynamische beheersweergaven. Voor elke verklaring in de batch wordt een rij teruggegeven. Vervolgens wordt er een plangids gemaakt voor de eerste en derde statements in de batch door de @statement_start_offset parameter te specificeren. De laatste verklaring in het voorbeeld bevestigt dat de plangidsen bestaan.

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

Verwante inhoud

• opgeslagen procedures voor database-engine (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• Planhandleidingen
• 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)