Principy plánů dotazů
Je užitečné mít základní znalosti o tom, jak optimalizátory databází fungují, než se podrobněji seznámíte s podrobnostmi plánu provádění. SQL Server používá to, co se označuje jako optimalizátor dotazů založený na nákladech. Optimalizátor dotazů vypočítá náklady na několik možných plánů na základě statistik, které má na využívaných sloupcích, a možné indexy, které je možné použít pro každou operaci v každém plánu dotazu. Na základětěchtoch Některé složité dotazy můžou mít tisíce možných plánů provádění. Optimalizátor nevyhodnocuje každý možný plán, ale používá heuristiku k určení plánů, u kterých je pravděpodobné, že budou mít dobrý výkon. Optimalizátor pak zvolí nejnižší nákladový plán všech plánů vyhodnocených pro daný dotaz.
Vzhledem k tomu, že optimalizátor dotazů je založený na nákladech, je důležité, aby byl vhodný pro rozhodování. Sql Server statistiky používá ke sledování distribuce dat ve sloupcích a indexech aktuální, nebo může způsobit generování neoptimálních plánů provádění. SQL Server automaticky aktualizuje statistiky při změnách dat v tabulce; Pro rychlé změny dat však může být potřeba častější aktualizace. Modul při vytváření plánu používá mnoho faktorů, včetně úrovně kompatibility databáze, odhadů řádků na základě statistik a dostupných indexů.
Když uživatel odešle dotaz do databázového stroje, stane se následující proces:
- Dotaz se analyzuje pro správnou syntaxi a pokud je syntaxe správná, vygeneruje se strom analýzy databázových objektů.
- Analýza stromu z kroku 1 se považuje za vstup do komponenty databázového stroje, která se nazývá Algebrizer pro vazbu. Tento krok ověří, že existují sloupce a objekty v dotazu, a identifikuje datové typy, které se zpracovávají pro daný dotaz. Tento krok vypíše strom procesoru dotazů, který je ve vstupu pro krok 3.
- Vzhledem k tomu, že optimalizace dotazů je relativně nákladný proces z hlediska spotřeby procesoru, databázový stroj ukládá plány spouštění do mezipaměti ve speciální oblasti paměti označované jako mezipaměť plánu. Pokud plán pro daný dotaz již existuje, tento plán se načte z mezipaměti. Dotazy, jejichž plány jsou uloženy v mezipaměti, budou mít každá hodnotu hash vygenerovanou na základě T-SQL v dotazu. Tato hodnota se označuje jako query_hash. Modul vygeneruje query_hash pro aktuální dotaz a pak se podívá, jestli odpovídá jakýmkoli existujícím dotazům v mezipaměti plánu.
- Pokud plán neexistuje, optimalizátor dotazů použije svůj optimalizátor založený na nákladech k vygenerování několika možností plánu provádění na základě statistik o sloupcích, tabulkách a indexech používaných v dotazu, jak je popsáno výše. Výstupem tohoto kroku je plán provádění dotazů.
- Dotaz se pak spustí pomocí plánu provádění, který se načítá z mezipaměti plánu, nebo pomocí nového plánu vygenerovaného v kroku 4. Výstupem tohoto kroku jsou výsledky dotazu.
Poznámka:
Další informace o fungování procesoru dotazů najdete v průvodci architekturou zpracování dotazů.
Podívejme se na příklad. Představte si následující dotaz:
SELECT orderdate,
AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;
V tomto příkladu SQL Server zkontroluje existenci sloupců OrderDate, ShipDate a SalesAmount v tabulce FactResellerSales. Pokud tyto sloupce existují, vygeneruje pro dotaz hodnotu hash a prozkoumá mezipaměť plánu pro odpovídající hodnotu hash. Pokud existuje plán dotazu s odpovídající hodnotou hash, modul se pokusí tento plán znovu použít. Pokud neexistuje žádný plán s odpovídající hodnotou hash, zkontroluje statistiky, které má k dispozici ve sloupcích OrderDate a ShipDate . Klauzule WHERE odkazující na sloupec ShipDate je to, co se v tomto dotazu označuje jako predikát. Pokud existuje neclusterovaný index, který obsahuje sloupec ShipDate , SQL Server pravděpodobně zahrne do plánu, pokud jsou náklady nižší než načítání dat z clusterovaného indexu. Optimalizátor pak zvolí plán s nejnižšími náklady dostupných plánů a spustí dotaz.
Plány dotazů kombinují řadu relačních operátorů pro načtení dat a také zaznamenávají informace o datech, jako jsou odhadované počty řádků. Dalším prvkem plánu provádění je paměť potřebná k provádění operací, jako je spojení nebo řazení dat. Paměť potřebná dotazem se nazývá udělení paměti. Udělení paměti je dobrým příkladem důležitosti statistiky. Pokud SI SQL Server myslí, že operátor vrátí 10 000 000 řádků, když vrací pouze 1000, je dotazu uděleno mnohem větší množství paměti. Udělení paměti, které je větší, než je nutné, může způsobit dvojí problém. Nejprve může dotaz narazit na RESOURCE_SEMAPHORE čekání, což značí, že dotaz čeká na přidělení velkého množství paměti SQL Serveru. SQL Server ve výchozím nastavení čeká 25krát náklady na dotaz (v sekundách) před spuštěním až 24 hodin. Za druhé, když se dotaz spustí, pokud není k dispozici dostatek paměti, dotaz přeteče do databáze tempdb, což je mnohem pomalejší než provoz v paměti.
Plán provádění také ukládá další metadata o dotazu, včetně úrovně kompatibility databáze, stupně paralelismu dotazu a parametrů zadaných v případě parametrizovaného dotazu, ale nikoli pouze na úroveň kompatibility databáze.
Plány dotazů je možné zobrazit buď v grafické reprezentaci, nebo v textovém formátu. Textové možnosti jsou vyvolány příkazy SET a vztahují se pouze na aktuální připojení. Textové plány se dají zobrazit kdekoli, kde můžete spouštět dotazy T-SQL.
Většina dbA dává přednost grafickému pohledu na plány, protože grafický plán umožňuje zobrazit plán jako celek, včetně toho, co se nazývá tvar plánu, snadno. Existuje několik způsobů, jak zobrazit a uložit grafické plány dotazů. Nejběžnějším nástrojem používaným k tomuto účelu je SQL Server Management Studio, ale odhadované plány je možné zobrazit také v nástroji Azure Data Studio. Existují také nástroje třetích stran, které podporují zobrazování grafických plánů spouštění.
Existují tři různé typy plánů provádění, které je možné zobrazit.
Odhadovaný plán provádění
Tento typ je plán provádění vygenerovaný optimalizátorem dotazů. Metadata a velikost grantu paměti dotazu jsou založená na odhadech ze statistiky, protože existují v databázi v době kompilace dotazu. Pokud chcete před spuštěním dotazu zobrazit textový odhadovaný plán, spusťte příkaz SET SHOWPLAN_ALL ON . Po spuštění dotazu uvidíte kroky plánu provádění, ale dotaz se nespustí a nezobrazí se žádné výsledky. Možnost SET zůstane v platnosti, dokud ji nenastavíte vypnutou.
Skutečný plán provádění
Tento typ je stejný jako odhadovaný plán; Tento plán však obsahuje také kontext spuštění dotazu, který zahrnuje odhadované a skutečné počty řádků, všechna upozornění spuštění, skutečný stupeň paralelismu (počet použitých procesorů) a uplynulé doby využití procesoru během provádění. Pokud chcete zobrazit textový skutečný plán, spusťte příkaz SET STATISTICS PROFILE ON před spuštěním dotazu. Dotaz se spustí a zobrazí se plán a výsledky.
Živé statistiky dotazů
Tato možnost zobrazení plánu kombinuje odhadované a skutečné plány do animovaného plánu, který zobrazuje průběh provádění prostřednictvím operátorů v plánu. Aktualizuje se každou sekundu a zobrazí skutečný počet řádků procházejících operátory. Další výhodou statistiky živého dotazu je, že ukazuje předání operátoru k operátorovi, což může být užitečné při řešení některých problémů s výkonem. Vzhledem k tomu, že typ plánu je animovaný, je k dispozici pouze jako grafický plán.