Vysvětlení odhadovaných a skutečných plánů dotazů
Téma skutečného a odhadovaného plánu provádění může být matoucí. Rozdíl je v tom, že skutečný plán zahrnuje statistiky modulu runtime, které nejsou zachyceny v odhadovaném plánu. Použité operátory a pořadí provádění budou stejné jako odhadovaný plán téměř ve všech případech. Dalším aspektem je, že aby bylo možné zachytit skutečný plán provádění, musí být dotaz proveden, což může být časově náročné nebo není možné. Dotaz může být například příkaz UPDATE , který lze spustit pouze jednou. Pokud ale potřebujete zobrazit výsledky dotazu a plán, budete muset použít jednu ze skutečných možností plánu.
Jak je znázorněno výše, můžete v nástroji SSMS vygenerovat odhadovaný plán kliknutím na tlačítko odkazující na pole odhadovaného plánu dotazu (nebo pomocí klávesového příkazu Control+L). Skutečný plán můžete vygenerovat kliknutím na zobrazenou ikonu (nebo pomocí klávesového příkazu Control+M) a následným spuštěním dotazu. Tato dvě přepínače fungují trochu jinak. Tlačítko Zahrnout odhadovaný plán dotazu okamžitě odpoví na jakýkoli zvýrazněný dotaz (nebo na celý pracovní prostor, pokud není zvýrazněno nic), na rozdíl od tlačítka Zahrnout skutečný plán dotazu.
Provádění dotazu i generování odhadovaného plánu provádění má režijní náklady, takže zobrazení plánů provádění by se mělo provádět pečlivě v produkčním prostředí.
Při psaní dotazu můžete obvykle použít odhadovaný plán provádění, abyste porozuměli jeho charakteristikám výkonu, identifikovali chybějící indexy nebo zjistili anomálie dotazů. Skutečný plán provádění se nejlépe používá k pochopení výkonu modulu runtime dotazu a nejdůležitější mezery ve statistických datech, které způsobují, že optimalizátor dotazů provede neoptimální volby na základě dostupných dat.
Čtení plánu dotazu
Plány provádění ukazují, jaké úlohy databázový stroj provádí při načítání dat potřebných k uspokojení dotazu. Pojďme se ponořit do plánu.
Nejprve se samotný dotaz zobrazí níže:
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;
Tento dotaz spojuje tabulku StockItems k tabulce StockItemHoldings , kde jsou hodnoty ve sloupci StockItemID stejné. Databázový stroj musí nejprve tyto řádky identifikovat, aby mohl zpracovat zbytek dotazu.
Každá ikona v plánu zobrazuje konkrétní operaci, která představuje různé akce a rozhodnutí, která tvoří plán provádění. Databázový stroj SQL Serveru má více než 100 operátorů dotazů, které se můžou shodovat s plánem provádění. Všimněte si, že pod ikonou každého operátoru je procento nákladů vzhledem k celkové ceně dotazu. I operace, která zobrazuje náklady na 0 %, stále představují určité náklady. Ve skutečnosti je 0 % obvykle způsobeno zaokrouhlováním, protože náklady na grafický plán se vždy zobrazují jako celá čísla a reálné procento je něco menšího než 0,5 %.
Tok provádění v plánu provádění je zprava doleva a shora dolů, takže ve výše uvedeném plánu je operace clusterované kontroly indexu v clusterovém indexu StockItemHoldings.PK_Warehouse_StockItemHoldings clusterovaný index první operací v dotazu. Šířky čar, které spojují operátory, jsou založeny na odhadovaném počtu řádků dat, které proudí směrem k dalšímu operátoru. Tlustá šipka je indikátorem velkého operátora pro přenos operátora a může to indikovat příležitost k ladění dotazu. Můžete také podržet myš nad operátorem a zobrazit další informace v popisu, jak je znázorněno níže.
Popis ukazuje náklady a odhady odhadovaného plánu a skutečný plán bude zahrnovat porovnání se skutečnými řádky a náklady. Každý operátor má také vlastnosti, které zobrazí více než popis. Pokud kliknete pravým tlačítkem myši na konkrétní operátor, můžete vybrat možnost Vlastnosti z místní nabídky a zobrazit úplný seznam vlastností. Tato možnost otevře samostatné podokno Vlastnosti v aplikaci SQL Server Management Studio, které je ve výchozím nastavení na pravé straně. Po otevření podokna Vlastnosti se kliknutím na libovolný operátor naplní seznam Vlastnosti vlastnostmi daného operátoru. Případně můžete otevřít podokno Vlastnosti kliknutím na Zobrazit v hlavní nabídce aplikace SQL Server Management Studio a výběrem možnosti Vlastnosti.
Podokno Vlastnosti obsahuje další informace a zobrazuje výstupní seznam, který obsahuje podrobnosti o sloupcích předávaných dalšímu operátoru. Tyto sloupce můžou znamenat, že k vylepšení výkonu dotazů při analýze pomocí clusterované kontroly indexu je potřeba neclusterovaný index. Vzhledem k tomu, že operace prohledávání clusterovaného indexu čte celou tabulku, může být v tomto scénáři efektivnější neskupený index ve sloupci StockItemID v každé tabulce.
Zjednodušená profilace dotazů
Jak už bylo zmíněno výše, zachycení skutečných plánů provádění, ať už použití SSMS nebo infrastruktury monitorování rozšířených událostí, může mít velkou režii a obvykle se provádí pouze při řešení potíží s živými lokalitami. Režie pozorovatele, jak je známo, jsou náklady na monitorování spuštěné aplikace. V některých scénářích můžou být tyto náklady jen pár procentních bodů využití procesoru, ale v jiných případech, jako je zachycení skutečných plánů provádění, může výrazně zpomalit výkon jednotlivých dotazů. Starší infrastruktura profilace v modulu SQL Serveru může způsobit až 75% režijní náklady na zachytávání informací o dotazech, zatímco zjednodušená infrastruktura profilace má maximální režii přibližně 2 %.
V první verzi zjednodušené profilace shromáždil počet řádků a informace o využití vstupně-výstupních operací (počet logických a fyzických čtení a zápisů provedených databázovým strojem pro splnění daného dotazu). Kromě toho byla zavedena nová rozšířená událost s názvem query_thread_profile , která umožňuje kontrolovat data z každého operátora v plánu dotazu. V počáteční verzi zjednodušené profilace vyžaduje použití této funkce globální povolení příznaku trasování 7412.
V novějších verzích (SQL Server 2016 SP2 CU3, SQL Server 2017 CU11 a SQL Server 2019), pokud není zjednodušené profilování povolené globálně, můžete použít nápovědu USE HINT QUERY_PLAN_PROFILE dotazu k povolení zjednodušené profilace na úrovni dotazu. Když dotaz s tímto tipem dokončí provádění, vygeneruje se query_plan_profile rozšířená událost, která poskytuje skutečný plán provádění. Příklad dotazu můžete zobrazit pomocí tohoto tipu:
SELECT [stockItemName]
,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
,[ QuantityonHand]
FROM [Warehouse].[StockItems] s
JOIN [Warehouse].[StockItems] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox
OPTION(USE HINT ('QUERY_PLAN_PROFILE'));
Statistiky posledního plánu dotazů
SQL Server 2019 a Azure SQL Database podporují dvě další vylepšení infrastruktury profilace dotazů. Nejprve je zjednodušené profilování ve výchozím nastavení povolené v SQL Serveru 2019 i ve službě Azure SQL Database a spravované instanci. Zjednodušené profilování je také k dispozici jako možnost konfigurace s vymezeným oborem databáze, která se nazývá LIGHTWEIGHT_QUERY_PROFILING. Pomocí možnosti s vymezeným oborem databáze můžete tuto funkci zakázat pro libovolnou uživatelskou databázi nezávisle na sobě.
Za druhé je k sys.dm_exec_query_plan_statsdispozici nová funkce dynamické správy, která vám může ukázat poslední známý skutečný plán provádění dotazů pro daný popisovač plánu. Pokud chcete zobrazit poslední známý skutečný plán dotazů prostřednictvím funkce, můžete povolit příznak trasování 2451 pro celý server. Alternativně můžete tuto funkci povolit pomocí možnosti konfigurace s vymezeným oborem databáze s názvem LAST_QUERY_PLAN_STATS.
Tuto funkci můžete kombinovat s jinými objekty a získat tak poslední plán provádění pro všechny dotazy uložené v mezipaměti, jak je znázorněno níže:
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO
Tato funkce umožňuje rychle identifikovat statistiky modulu runtime pro poslední spuštění jakéhokoli dotazu v systému s minimální režií. Následující obrázek ukazuje, jak plán načíst. Pokud vyberete XML plánu provádění, což bude první sloupec výsledků, zobrazí se plán provádění zobrazený na druhém obrázku níže.
Jak vidíte z vlastností níže načtené kontroly indexu Columnstore, plán načtený z mezipaměti má skutečný počet řádků načtených v dotazu.
