Sdílet prostřednictvím

Průvodci plánováním

  • Článek

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Důležitý

nápovědy k úložišti dotazů poskytují jednodušší metodu pro tvarování plánů dotazů beze změny kódu aplikace. Rady úložiště dotazů jsou jednodušší než vodítka plánu. Nápovědy k úložišti dotazů jsou k dispozici ve službě Azure SQL Database, databázi SQL v Microsoft Fabric, Azure SQL Managed Instance a v SQL Serveru 2022 (16.x) a novějších verzích.

Průvodci plánováním umožňují optimalizovat výkon dotazů, když nemůžete nebo nechcete přímo měnit text skutečného dotazu na SQL Serveru. Průvodci plánováním ovlivňují optimalizaci dotazů připojením tipů dotazů nebo plánu pevného dotazu. Průvodce plánováním může být užitečný, když malá podmnožina dotazů v databázové aplikaci poskytované dodavatelem třetí strany nefunguje podle očekávání. V průvodci plánem zadáte příkaz Transact-SQL, který chcete optimalizovat, a buď klauzuli OPTION obsahující rady dotazů, které chcete použít, nebo konkrétní plán dotazu, který chcete použít k optimalizaci dotazu. Když se dotaz spustí, SQL Server přiřadí příkaz Transact-SQL k průvodci plánem a buď připojí klauzuli OPTION k dotazu při běhu, nebo použije specifikovaný plán dotazu. Vzhledem k tomu, že optimalizátor dotazů SQL Serveru obvykle vybírá nejlepší plán provádění dotazu, doporučujeme používat příručky plánu pouze jako poslední možnost pro zkušené vývojáře a správce databází.

Celkový počet průvodců plánování, které můžete vytvořit, je omezený pouze dostupnými systémovými prostředky. Průvodci plánováním by však měli být omezeni na důležité dotazy zaměřené na lepší nebo stabilizovaný výkon. Vodítka plánu by neměla být použita k ovlivnění většiny zatížení dotazů nasazené aplikace.

Výsledný plán provádění vynucený touto funkcí bude stejný nebo podobný plánu, který je vynucený. Vzhledem k tomu, že výsledný plán nemusí být shodný s plánem určeným průvodcem plánu, může se výkon plánů lišit. Ve výjimečných případech může být rozdíl v výkonu významný a negativní; v takovém případě musí správce odebrat vynucený plán.

Příručky plánu nelze použít v každé edici Microsoft SQL Serveru. Seznam funkcí podporovaných edicemi SQL Serveru najdete v tématu Funkce podporované edicemi SQL Serveru 2016. 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.

Typy průvodců plánem

Můžete vytvořit následující typy průvodců plánu.

Průvodce plánem OBJECT

Průvodce plánem OBJECT odpovídá dotazům, které se spouští v kontextu Transact-SQL uložených procedur, skalárních uživatelem definovaných funkcí, uživatelem definovaných funkcí s více příkazy tabulky a triggerů DML.

Předpokládejme, že následující uložená procedura, která přebírá parametr @Country_region, je v databázové aplikaci nasazené pro AdventureWorks2022 databázi:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;

Předpokládejme, že tato uložená procedura byla zkompilována a optimalizována pro @Country_region = N'AU' (Austrálie). Vzhledem k tomu, že existuje relativně málo prodejních objednávek pocházejících z Austrálie, sníží se výkon při provádění dotazu pomocí hodnot parametrů zemí/oblastí s většími prodejními objednávkami. Vzhledem k tomu, že většina prodejních objednávek pochází ze Spojených států, plán dotazu vygenerovaný pro @Country_region = N'US' by pravděpodobně lépe fungoval pro všechny možné hodnoty parametru @Country_region.

Tento problém můžete vyřešit tím, že upravíte uloženou proceduru a přidáte tip dotazu OPTIMIZE FOR do dotazu. Protože je však uložená procedura v nasazené aplikaci, nemůžete přímo upravit kód aplikace. Místo toho můžete v databázi AdventureWorks2022 vytvořit následující příručku plánu.

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

Když se dotaz zadaný v příkazu sp_create_plan_guide spustí, dotaz se před optimalizací upraví tak, aby zahrnoval klauzuli OPTIMIZE FOR (@Country = N''US'').

Průvodce plánem SQL

SQL průvodce plánem odpovídá dotazům, které se spouští v kontextu samostatných SQL příkazů a dávek Transact-SQL, které nejsou součástí databázového objektu. Pokyny plánu založené na SQL lze také použít ke shodě dotazů, které jsou parametrizovány na zadanou podobu. Příručky plánu SQL platí pro samostatné příkazy Transact-SQL a dávky. Často se tyto příkazy odesílají aplikací pomocí uložené procedury sp_executesql systému. Představte si například následující samostatnou dávku:

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

Chcete-li zabránit generování plánu paralelního spuštění v tomto dotazu, vytvořte následující průvodce plánem a nastavte hint dotazu MAXDOP na 1 v parametru @hints.

sp_create_plan_guide   
@name = N'Guide2',   
@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)';

Jako další příklad zvažte následující příkaz SQL odeslaný pomocí sp_executesql.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;

Pokud chcete vytvořit jedinečný plán pro každé spuštění tohoto dotazu, vytvořte následující příručku plánu a v parametru @hints použijte nápovědu k dotazu OPTION (RECOMPILE).

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

Důležitý

Hodnoty zadané pro @module_or_batch a @params argumenty příkazu sp_create_plan guide musí odpovídat odpovídajícímu textu odeslanému ve skutečném dotazu. Další informace najdete v tématu sp_create_plan_guide (Transact-SQL) a Použití SQL Server Profileru k vytváření a testování průvodců plánů.

Vodítka plánu SQL lze také vytvořit u dotazů, které se parametrizují do stejného tvaru, když je databázová možnost PARAMETRIZACE nastavena na FORCED, nebo když je vytvořeno plánovací vodítko ŠABLONA, které určuje parametrizovanou kategorii dotazů.

Průvodce plánem ŠABLONY

Průvodce plánem ŠABLONy odpovídá samostatným dotazům, které parametrizují zadaný formulář. Tyto průvodce plánem slouží k tomu, aby přepsaly aktuální nastavení parametrizace databáze pro třídu dotazů.

Průvodce plánem šablony můžete vytvořit v některé z následujících situací:

  • Možnost PARAMETRIZACE databáze je NASTAVENA na VYNUCENO, ale existují dotazy, které chcete zkompilovat podle pravidel Jednoduchá Parametrizace.

  • Možnost PARAMETRIZACE databáze je NASTAVENA na SIMPLE (výchozí nastavení), ale chcete, aby se Vynucená Parametrizace vyzkoušela u třídy dotazů.

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

Vodítka plánu jsou omezeny na databázi, ve které jsou vytvořeny. Proto lze s dotazem spárovat pouze příručky plánu, které jsou v databázi, která je aktuální, když se dotaz spustí. Pokud je například AdventureWorks2022 aktuální databází a spustí se následující dotaz:

SELECT FirstName, LastName FROM Person.Person;

K tomuto dotazu mohou být přiřazeni pouze průvodci plánů v databázi AdventureWorks2022. Pokud je ale AdventureWorks2022 aktuální databází a spustí se následující příkazy:

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

K porovnání s dotazem mají nárok pouze průvodci plánováním v DB1, protože dotaz se provádí v kontextu DB1.

V případě průvodců plánů založených na SQL nebo ŠABLONách odpovídá SQL Server hodnotám argumentů dotazu @module_or_batch a @params porovnáním dvou hodnot znak po znaku. To znamená, že text musíte zadat přesně tak, jak ho SQL Server přijme při zpracování dávky.

Pokud je @type = SQL a @module_or_batch nastavena na hodnotu NULL, je hodnota @module_or_batch nastavena na hodnotu @stmt. To znamená, že hodnota pro statement_text 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.

Pokud se na příkaz může použít průvodce standardním plánem (SQL nebo OBJECT) i průvodce plánem šablony, použije se pouze průvodce standardním plánem.

Poznámka

Dávka, která obsahuje příkaz, pro nějž chcete vytvořit průvodce plánem, nemůže obsahovat příkaz USE na databázi .

Efekt průvodce plánem v mezipaměti 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 v dávce 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.

Úkol Téma
Popisuje, jak vytvořit plánovacího průvodce. Vytvoření průvodce novým plánem
Popisuje, jak vytvořit průvodce plánem pro parametrizované dotazy. Vytvoření průvodce plánem pro parametrizované dotazy
Popisuje, jak řídit chování parametrizace dotazů pomocí vodítek plánu. Specifikujte chování parametrizace dotazu pomocí průvodců plánu
Popisuje, jak do průvodce plánem zahrnout plán s pevným dotazem. Aplikovat pevný plán dotazu na plán průvodce
Popisuje, jak v průvodci plánem zadat nápovědy k dotazům. Připojit dotazové nápovědy k plánovacímu průvodci
Popisuje, jak zobrazit vlastnosti plánovací příručky. Zobrazit vlastnosti průvodce plánem
Popisuje, jak používat SQL Server Profiler k vytváření a testování průvodců plánem. použití SQL Server Profileru k vytváření a testování průvodců plánem
Popisuje, jak ověřit průvodce plánováním. Ověřit průvodce plánu po upgradu

Viz také

sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)