sys.sp_create_plan_guide_from_handle (Transact-SQL)

platí pro:SQL Server

Vytvoří jeden nebo více průvodců plánu z plánu dotazu v mezipaměti plánu. Můžete použít tuto uloženou proceduru, abyste zajistili, že optimalizátor dotazů vždy používá specifický plán dotazů pro konkrétní dotaz. Další informace o průvodcích plánem najdete v tématu Průvodci plánem.

Transact-SQL konvence syntaxe

Syntax

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

Arguments

[ @name = ] N'jméno'

Název průvodce plánem. @name je sysname, bez výchozího nastavení. Názvy průvodců plánu jsou vymezeny na aktuální databázi. @name musí dodržovat pravidla pro identifikátory a nesmí začínat znakem čísla (#). Maximální délka @name je 124 znaků.

[ @plan_handle = ] plan_handle

Identifikuje dávku v plánové cache. @plan_handle je varbinární(64), bez výchozího nastavení. @plan_handle lze získat z pohledu sys.dm_exec_query_stats dynamického řízení.

[ @statement_start_offset = ] statement_start_offset

Identifikuje počáteční pozici příkazu v rámci šarže specifikovaných @plan_handle. @statement_start_offset je int, s výchozím nastavením NULL.

Přepis příkazů odpovídá sloupci statement_start_offset v dynamickém pohledu sys.dm_exec_query_stats .

Když NULL je specifikován nebo není specifikován offset příkazu, pro každý příkaz v dávce se vytvoří plánovací průvodce pomocí dotazovacího plánu pro specifikovaný plán handle. Výsledné plánové průvodce jsou ekvivalentní plánovým přívodcům, které používají USE PLAN dotazovací nápovědu k vynucení použití konkrétního plánu.

Remarks

Plánovací průvodce nelze vytvořit pro všechny typy výkazů. Pokud nelze vytvořit plánovací průvodce pro příkaz v dávce, uložená procedura příkaz ignoruje a pokračuje k dalšímu příkazu v dávce. Pokud se příkaz vyskytne vícekrát ve stejné dávce, plán pro poslední výskyt je aktivován a předchozí plány pro tento výrok jsou deaktivovány. Pokud nelze v plánu použít žádné příkazy v dávkové dávce, zobrazí se chyba 10532 a příkaz selže. Doporučujeme, abyste si vždy pořídili handle plánu z pohledu sys.dm_exec_query_stats dynamického řízení, abyste se této chybě vyhnuli.

Important

sp_create_plan_guide_from_handle Vytváří plány na základě plánů, jak se objevují v cache plánů. To znamená, že dávkový text, Transact-SQL příkazy a XML Showplan jsou převáděny znak po znaku (včetně jakýchkoli literálních hodnot předaných dotazu) z cache plánu do výsledného plánového průvodce. Tyto textové řetězce mohou obsahovat citlivé informace, které jsou následně uloženy v metadatech databáze. Uživatelé s příslušnými oprávněními mohou tyto informace zobrazit pomocí sys.plan_guides zobrazení katalogu a dialogového okna Vlastnosti plánu v SQL Server Management Studio. Aby se zajistilo, že citlivé informace nebudou zveřejněny prostřednictvím plánového průvodce, doporučujeme prostudovat plánovací průvodce vytvořené z plánové cache.

Vytvořte průvodce plánem pro více tvrzení v rámci plánu dotazu

Například , sp_create_plan_guide odstraní sp_create_plan_guide_from_handleplán dotazu pro cílovou dávku nebo modul z cache plánu. To je proto, aby všichni uživatelé začali nový plán používat. Při vytváření plánového průvodce pro více výpisů v rámci jednoho dotazovacího plánu můžete odložit odstranění plánu z cache vytvořením všech plánových průvodců v explicitní transakci. Tato metoda umožňuje zůstat plánu v cache až do dokončení transakce a vytvoření plánového průvodce pro každý specifikovaný příkaz. Viz příklad B.

Permissions

Vyžaduje VIEW SERVER STATE oprávnění. Kromě toho jsou pro každý plánový průvodce vytvořený pomocí sp_create_plan_guide_from_handle. vyžadována individuální oprávnění. Vytvoření plánového průvodce typu OBJECT vyžaduje ALTER povolení k odkazovanému objektu. Vytvoření plánového průvodce typu SQL nebo TEMPLATE vyžaduje ALTER povolení k aktuální databázi. Pro určení typu plánu, který bude vytvořen, spusťte následující dotaz:

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;

V řádku, který obsahuje tvrzení, pro které vytváříte plánovací průvodce, prohlédněte sloupec objtype ve výsledné sadě. Hodnota označuje Proc , že plánovací průvodce je typu OBJECT. Další hodnoty, jako Ad hoc nebo Prepared označují, že plánovací průvodce je typu SQL.

Examples

A. Vytvořte plán z dotazového plánu v plánové cache

Následující příklad vytváří plánovací průvodce pro jedno SELECT tvrzení tím, že specifikuje plán dotazu z cache plánu. Příklad začíná vyvedením jednoduchého SELECT příkazu, pro který bude vytvořen plánovací průvodce. Plán pro tento dotaz je zkoumán pomocí dynamických pohledů sys.dm_exec_sql_textsys.dm_exec_text_query_plan a řízení. Plánovací průvodce je pak vytvořen pro dotaz specifikací plánu dotazu v plánové cache, která je s dotazem spojena. Závěrečné tvrzení v příkladu potvrzuje, že plánovací průvodce existuje.

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. Vytvořte více plánových průvodců pro vícenásobnou dávku

Následující příklad vytváří plán pro dvě tvrzení v rámci vícenásobné dávky. Plánové průvodce jsou vytvářeny v rámci explicitní transakce, aby dotazovací plán pro dávku nebyl odstraněn z plánové cache po vytvoření prvního plánu. Příklad začíná vykonáním vícenásobné dávky. Plán pro dávku je zkoumán pomocí dynamických pohledů správy. Pro každý příkaz v šarži se vrací řádek. Poté je vytvořen plánovací průvodce pro první a třetí příkaz v dávce specifikací parametru @statement_start_offset . Závěrečné tvrzení v příkladu potvrzuje, že plánové vodítka existují.

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

Související obsah

  • Poslední aktualizace v