sp_create_plan_guide (Transact-SQL)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

Vytvoří průvodce plánem pro přidružení tipů dotazů nebo skutečných plánů dotazů k dotazům v databázi. Další informace o průvodcích plánem najdete v tématu Průvodci plánem.

Transact-SQL konvence syntaxe

Syntaxe

sp_create_plan_guide
    [ @name = ] N'name'
    [ , [ @stmt = ] N'stmt' ]
    , [ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }
    [ , [ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' } ]
    [ , [ @params = ] N'@parameter_name data_type [ , ... n ]' ]
    [ , [ @hints = ] { N'OPTION ( query_hint [ , ...n ] )' | N'XML_showplan' } ]
[ ; ]

Argumenty

[ @name = ] N'název'

Název průvodce plánem. @name je sysname, bez výchozího nastavení a maximální délka 124 znaků. 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 (#).

[ @stmt = ] N'stmt'

Prohlášení Transact-SQL, proti kterému se má vytvořit průvodce plánem. @stmt je nvarchar(max) s výchozím nastavením NULL. Když optimalizátor dotazů SQL Serveru rozpozná dotaz, který odpovídá @stmt, @name se projeví. Aby bylo vytvoření průvodce plánem úspěšné, musí se @stmt objevit v kontextu určeném parametry @type, @module_or_batch a @params .

@stmt musí být poskytována způsobem, který umožňuje optimalizátoru dotazů odpovídat odpovídajícímu příkazu zadanému v dávce nebo modulu určeném @module_or_batch a @params. Další informace najdete v části Poznámky. Velikost @stmt je omezená pouze dostupnou pamětí serveru.

[ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }

Typ entity, ve které se zobrazí @stmt . Určuje kontext pro porovnávání @stmt k @name. @type je nvarchar(60) a může to být jedna z těchto hodnot:

• OBJECT

  Označuje , @stmt se zobrazí v kontextu Transact-SQL uložené procedury, skalární funkce, funkce s více hodnotami tabulky nebo Transact-SQL triggerU DML v aktuální databázi.

• SQL

  Označuje , @stmt se zobrazí v kontextu samostatného příkazu nebo dávky, které lze odeslat na SQL Server prostřednictvím libovolného mechanismu. Transact-SQL příkazy odeslané objekty CLR (Common Language Runtime) nebo rozšířené uložené procedury nebo pomocí EXECUTE N'<sql_string>', se zpracovávají jako dávky na serveru, a proto by měly být identifikovány jako @typeSQL. Pokud SQL je zadán, nelze v parametru PARAMETERIZATION { FORCED | SIMPLE } zadat nápovědu dotazu.

• TEMPLATE

  Označuje průvodce plánem, který se vztahuje na jakýkoli dotaz, který parametrizuje formulář uvedený v @stmt. Pokud TEMPLATE je zadána, lze v PARAMETERIZATION { FORCED | SIMPLE } zadat pouze nápovědu dotazu. Další informace o TEMPLATE průvodcích plánem naleznete v tématu Určení chování parametrizace dotazů pomocí průvodců plánem.

[ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' }

Určuje název objektu, ve kterém se @stmt zobrazí, nebo dávkový text, ve kterém se @stmt zobrazí. @module_or_batch je nvarchar(max) s výchozím nastavením NULL. Dávkový text nemůže obsahovat USE <database> příkaz.

Aby průvodce plánem odpovídal dávce odeslané z aplikace, musí být @module_or_batch k dispozici ve stejném formátu, jako je znak pro znak, který je odeslán na SQL Server. Aby bylo snazší tuto shodu, neprovádí se žádný interní převod. Další informace najdete v části Poznámky.

[ <schema_name>. ] <object_name> určuje název Transact-SQL uložené procedury, skalární funkce, funkce s více hodnotami tabulky nebo Transact-SQL triggerU DML, který obsahuje @stmt. Pokud <schema_name> není zadáno, <schema_name> použije schéma aktuálního uživatele. Pokud NULL je zadán a @type je SQL, hodnota @module_or_batch je nastavena na hodnotu @stmt. Je-li @typeTEMPLATE, @module_or_batch musí být NULL.

[ @params = ] N'@parameter_namedata_type [ ,... n ]'

Určuje definice všech parametrů, které jsou vloženy do @stmt. @params je nvarchar(max) s výchozím nastavením NULL. @params platí pouze v případech, kdy platí některé z následujících možností:

• @type je SQL nebo TEMPLATE. Pokud TEMPLATE, @params nesmí být NULL.

• @stmt je odeslán pomocí sp_executesql a hodnota parametru @params je zadána, nebo SQL Server interně odešle příkaz po parametrizaci. Odeslání parametrizovaných dotazů z databázových rozhraní API (včetně rozhraní ODBC, OLE DB a ADO.NET) se sql Serveru jeví jako volání sp_executesql nebo rutiny kurzoru serveru rozhraní API, proto je můžou spárovat také průvodci plánovánímSQL.TEMPLATE

@params musí být zadány ve stejném formátu jako odeslané do SQL Serveru buď pomocí sp_executesql nebo odeslání interně po parametrizaci. Další informace najdete v části Poznámky. Pokud dávka neobsahuje parametry, NULL je nutné zadat. Velikost @params je omezená pouze dostupnou pamětí serveru.

[ @hints = ] { N'OPTION ( query_hint [ , ... n ] ) ' | N'XML_showplan' }

@hints je nvarchar(max) s výchozím nastavením NULL.

• OPTION ( <query_hint> [ , ...n ] )

  Určuje OPTION klauzuli, která se má připojit k dotazu, který odpovídá @stmt. @hints musí být syntakticky stejné jako OPTION klauzule v SELECT příkazu a může obsahovat libovolnou platnou sekvenci tipů dotazu.

• <XML_showplan>'

  Plán dotazu ve formátu XML, který se má použít jako nápověda.

  Doporučujeme přiřadit plán showplan XML proměnné. Jinak je nutné uvozovky v sadě showplan uvozovek označit jako předchozí. Viz příklad E.

• NULL

  Označuje, že všechny existující nápovědy zadané v OPTION klauzuli dotazu se na dotaz nepoužijí. Další informace naleznete v tématu KLAUZULE OPTION.

Poznámky

Argumenty, které se mají sp_create_plan_guide zadat v uvedeném pořadí. Při zadávání hodnot parametrů sp_create_plan_guidemusí být zadány všechny názvy parametrů explicitně nebo žádné vůbec. Pokud @name = je například zadán, pak @stmt =@type =, a tak dále, musí být také zadán. Podobně platí, že pokud @name = je vynechána a je zadaná pouze hodnota parametru, musí se vynechat i zbývající názvy parametrů a zadat pouze jejich hodnoty. Názvy argumentů jsou určené pouze pro popisné účely, které vám pomůžou porozumět syntaxi. SQL Server neověřuje, že zadaný název parametru odpovídá názvu parametru v pozici, kde se název používá.

Pro stejný dotaz a dávku nebo modul můžete vytvořit více než jednu OBJECT příručku nebo SQL průvodce plánem. V daném okamžiku však lze povolit pouze jednoho průvodce plánem.

Vodítka plánu typu OBJECT nelze vytvořit pro @module_or_batch hodnotu, která odkazuje na uloženou proceduru, funkci nebo trigger DML, který určuje WITH ENCRYPTION klauzuli nebo která je dočasná.

Při pokusu o vyřazení nebo úpravu funkce, uložené procedury nebo triggeru DML, na který odkazuje průvodce plánem, který je povolený nebo zakázaný, dojde k chybě. Pokus o vyřazení tabulky, která je na ní definovaná aktivační událostí, na kterou odkazuje průvodce plánem, také způsobí chybu.

Příručky plánu nelze použít v každé edici SQL Serveru. Seznam funkcí podporovaných edicemi SQL Serveru najdete v tématu Edice a podporované funkce systému SQL Server 2022. Průvodci plánu jsou viditelní v jakékoli edici. Můžete také připojit databázi, která obsahuje příručky k plánům k libovolné edici. Průvodce plánováním zůstanou nedotčené, když obnovíte nebo připojíte databázi k upgradované verzi SQL Serveru. Po provedení upgradu serveru byste měli ověřit použitelnost průvodců plánu v každé databázi.

Požadavky na porovnávání průvodce plánováním

Pro příručky plánu, které určují @typeSQL dotazu nebo TEMPLATE aby se úspěšně shodovaly s dotazem, hodnoty pro @module_or_batch a @params [, ... n ] musí být poskytována ve stejném formátu jako jejich protějšky předložené žádostí. To znamená, že musíte zadat dávkový text přesně tak, jak ho kompilátor SQL Serveru přijímá. K zachycení skutečného dávkového a parametrového textu můžete použít SQL Server Profiler. Další informace najdete v tématu Použití SQL Server Profileru k vytvoření a testování průvodců plánem.

Pokud jeSQL @type a @module_or_batch nastavena na NULLhodnotu , hodnota @module_or_batch je nastavena na hodnotu @stmt. To znamená, že hodnota pro @stmt musí být zadána ve stejném formátu, znak pro znak, který je odeslán na SQL Server. Aby bylo snazší tuto shodu, neprovádí se žádný interní převod.

Když SQL Server odpovídá hodnotě @stmt@module_or_batch a@params [, ... n ], nebo pokud @type je OBJECT, na text odpovídajícího dotazu uvnitř <object_name>, následující řetězcové prvky nejsou považovány za:

• Prázdné znaky (tabulátory, mezery, návraty na začátek řádku nebo odřádkování) uvnitř řetězce
• Komentáře (-- nebo /* */)
• Koncové středníky

SQL Server může například odpovídat řetězciN'SELECT * FROM T WHERE a = 10' @stmt následujícímu @module_or_batch:

 N'SELECT *
 FROM T
 WHERE a = 10'

Stejný řetězec by se ale neshodoval s tímto @module_or_batch:

N'SELECT * FROM T WHERE b = 10'

SQL Server ignoruje návrat na začátek řádku, odřádkování a mezery uvnitř prvního dotazu. V druhém dotazu je sekvence WHERE b = 10 interpretována odlišně od WHERE a = 10. Porovnávání se rozlišují malá a velká písmena a rozlišují se na malá a velká písmena (i když kolace databáze nerozlišuje malá a velká písmena), s výjimkou případů, kdy existují klíčová slova bez rozlišování velkých a malých písmen. Porovnávání je citlivé na prázdné mezery. Porovnávání není citlivé na zkrácené formy klíčových slov. Například klíčová slova EXECUTE, EXECa execute jsou považovány za ekvivalentní.

Účinek průvodce plánem na mezipaměť plánu

Vytvoření průvodce plánem v modulu odebere plán dotazu pro tento modul z mezipaměti plánu. Vytvoření průvodce plánem typu OBJECT nebo SQL dávky odebere plán dotazu pro dávku, která má stejnou hodnotu hash. Vytvoření průvodce plánem typu TEMPLATE odebere všechny dávky s jedním příkazem z mezipaměti plánu v rámci této databáze.

Povolení

Chcete-li vytvořit průvodce plánem typu OBJECT, vyžaduje ALTER oprávnění pro odkazovaný objekt. Chcete-li vytvořit průvodce plánem typu SQL nebo TEMPLATE, vyžaduje ALTER oprávnění k aktuální databázi.

Příklady

A. Vytvoření průvodce plánem typu OBJECT pro dotaz v uložené proceduře

Následující příklad vytvoří průvodce plánem, který odpovídá dotazu spuštěného v kontextu uložené procedury založené na aplikaci a použije nápovědu OPTIMIZE FOR pro dotaz.

Tady je uložená procedura:

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO

CREATE PROCEDURE Sales.GetSalesOrderByCountry (
    @Country_region NVARCHAR(60)
)
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h
         INNER JOIN Sales.Customer AS c
             ON h.CustomerID = c.CustomerID
         INNER JOIN Sales.SalesTerritory AS t
             ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

Tady je průvodce plánem vytvořený pro dotaz v uložené proceduře:

EXECUTE sp_create_plan_guide
    @name = N'Guide1',
    @stmt = N'SELECT *
              FROM Sales.SalesOrderHeader AS h
              INNER JOIN Sales.Customer AS c
                         ON h.CustomerID = c.CustomerID
              INNER JOIN Sales.SalesTerritory AS t
                 ON c.TerritoryID = t.TerritoryID
              WHERE t.CountryRegionCode = @Country_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

B. Vytvoření průvodce plánem typu SQL pro samostatný dotaz

Následující příklad vytvoří průvodce plánem, který odpovídá dotazu v dávce odeslané aplikací, která používá systém uloženou proceduru sp_executesql .

Tady je dávka:

SELECT TOP 1 *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC;

Pokud chcete zabránit generování plánu paralelního spuštění v tomto dotazu, vytvořte následující průvodce plánem:

EXECUTE sp_create_plan_guide
    @name = N'Guide1',
    @stmt = N'SELECT TOP 1 *
              FROM Sales.SalesOrderHeader
              ORDER BY OrderDate DESC',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (MAXDOP 1)';

C. Vytvoření průvodce plánem typu TEMPLATE pro parametrizovanou formu dotazu

Následující příklad vytvoří průvodce plánem, který odpovídá jakémukoli dotazu, který parametrizuje zadaný formulář, a směruje SQL Server k vynucení parametrizace dotazu. Následující dva dotazy jsou syntakticky ekvivalentní, ale liší se pouze v jejich konstantních literálových hodnotách.

SELECT *
FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
     INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
         ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT *
FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
     INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
         ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

Tady je průvodce plánem parametrizované formy dotazu:

EXECUTE sp_create_plan_guide
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

V předchozím příkladu je hodnota parametru @stmt parametrem parametrizovaná forma dotazu. Jediným spolehlivým způsobem, jak tuto hodnotu získat pro použití sp_create_plan_guide , je použít sp_get_query_template systém uloženou proceduru. Následující skript získá parametrizovaný dotaz a pak na něm vytvoří průvodce plánem.

DECLARE @stmt AS NVARCHAR (MAX);
DECLARE @params AS NVARCHAR (MAX);

EXECUTE sp_get_query_template N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
                  ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;', @stmt OUTPUT, @params OUTPUT;

EXECUTE sp_create_plan_guide N'TemplateGuide1',
    @stmt, N'TEMPLATE', NULL,
    @params, N'OPTION(PARAMETERIZATION FORCED)';

Důležité

Hodnota konstantních literálů v parametru @stmt předaného sp_get_query_template může ovlivnit datový typ zvolený pro parametr, který nahradí literál. To ovlivní sladění pokynů plánu. Možná budete muset vytvořit více než jednoho průvodce plánem pro zpracování různých rozsahů hodnot parametrů.

D. Vytvoření průvodce plánem pro dotaz odeslaný pomocí požadavku kurzoru rozhraní API

Průvodci plánováním můžou odpovídat dotazům odeslaným z rutin kurzoru serveru rozhraní API. Mezi tyto rutiny patří sp_cursorprepare, sp_cursorprepexeca sp_cursoropen. Aplikace, které používají rozhraní API ADO, OLE DB a ODBC často komunikují s SQL Serverem pomocí kurzorů serveru rozhraní API. Vyvolání rutin kurzoru serveru API můžete zobrazit v trasování sql Server Profileru zobrazením RPC:Starting události trasování profileru.

Předpokládejme, že se v RPC:Starting události trasování profileru pro dotaz, který chcete vyladit, zobrazí následující data:

DECLARE @p1 AS INT;
SET @p1 = -1;

DECLARE @p2 AS INT;
SET @p2 = 0;

DECLARE @p5 AS INT;
SET @p5 = 4104;

DECLARE @p6 AS INT;
SET @p6 = 8193;

DECLARE @p7 AS INT;
SET @p7 = 0;

EXECUTE sp_cursorprepexec
    @p1 OUTPUT,
    @p2 OUTPUT, N'@P1 varchar(255),@P2 varchar(255)', N'SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN
    @P1 AND
    @P2', @p5 OUTPUT, @p6 OUTPUT, @p7 OUTPUT, '20040101', '20050101';

SELECT @p1,
       @p2,
       @p5,
       @p6,
       @p7;

Všimněte si, že plán SELECT dotazu ve volání používá sp_cursorprepexec slučovací spojení, ale chcete použít spojení hash. Dotaz odeslaný pomocí parametrizován sp_cursorprepexec , včetně řetězce dotazu i řetězce parametru. Následující průvodce plánem můžete vytvořit a změnit výběr plánu pomocí řetězce dotazu a parametru přesně tak, jak se zobrazí, znak znaku pro znak ve volání sp_cursorprepexec.

EXECUTE sp_create_plan_guide
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h
              INNER JOIN Sales.SalesOrderDetail AS d
                ON h.SalesOrderID = d.SalesOrderID
              WHERE h.OrderDate BETWEEN @P1 AND @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

Následné spuštění tohoto dotazu aplikací je ovlivněno tímto průvodcem plánu a ke zpracování dotazu se použije spojení hash.

E. Vytvoření průvodce plánem získáním plánu XML showplan z plánu uloženého v mezipaměti

Následující příklad vytvoří průvodce plánem pro jednoduchý ad hoc SQL příkaz. Požadovaný plán dotazu pro tento příkaz je k dispozici v průvodci plánem zadáním xml showplan pro dotaz přímo v parametru @hints . Příklad nejprve spustí příkaz, který vygeneruje SQL plán v mezipaměti plánu. Pro účely tohoto příkladu se předpokládá, že vygenerovaný plán je požadovaný plán a nevyžaduje se žádné další ladění dotazů. Xml showplan pro dotaz je získán dotazováním sys.dm_exec_query_statszobrazení , sys.dm_exec_sql_texta sys.dm_exec_text_query_plan dynamické správy a je přiřazena k @xml_showplan proměnné. Proměnná @xml_showplan se pak předá sp_create_plan_guide příkazu v parametru @hints . Nebo můžete vytvořit průvodce plánem z plánu dotazu v mezipaměti plánu pomocí sp_create_plan_guide_from_handle uložené procedury.

USE AdventureWorks2022;
GO

SELECT City,
       StateProvinceID,
       PostalCode
FROM Person.Address
ORDER BY PostalCode DESC;
GO

DECLARE @xml_showplan AS NVARCHAR (MAX);

SET @xml_showplan = (
    SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.TEXT LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%'
);

EXECUTE sp_create_plan_guide
    @name = N'Guide1_from_XML_showplan',
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = @xml_showplan;
GO

Související obsah

• Průvodci plánem
• sp_control_plan_guide (Transact-SQL)
• sys.plan_guides (Transact-SQL)
• uložené procedury databázového stroje (Transact-SQL)
• uložené procedury systému (Transact-SQL)
• sys.dm_exec_sql_text (Transact-SQL)
• sys.dm_exec_cached_plans (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• sp_create_plan_guide_from_handle (Transact-SQL)
• sys.fn_validate_plan_guide (Transact-SQL)
• sp_get_query_template (Transact-SQL)