Jak jsme si pověděli minule, Execution Plan je výsledkem operací prováděných Query Optimizerem. Rychlost a efektivita provedení dotazu tedy závisí na kvalitě plánu, který generuje Optimizer. Optimizer můžeme ručně ovlivňovat různými nastaveními tzv. Hinty. Později se s nimi seznámíme. Při ladění dotazů se můžete setkat se dvěma druhy plánů.
Estimated Execution Plan je seznam operací, které je potřeba vykonat. Tento plán je celý postaven na odhadech z dostupných metadat tabulek. Udávané počty řádků jsou pouze vypočteny na základě dostupných statistik. Plán fakticky není proveden, proto se počty odhadnutých řádků mohou lišit od počtů skutečných řádků, které dotaz vrátí při skutečném spuštění.
Actual Execution Plan je naopak plán, který byl získán při spuštění dotazu. Počty řádků nejsou vypočteny, ale změřeny při provádění operací.
Technická poznámka: Testovací příklady budu zkoušet na volně stažitelné vzorové databázi AdventureWorks2016CTP3
Pro zobrazení aktuálního exekučního plánu v SQL Management Studio stiskněte CTRL+M nebo menu Query volba Include Actual Execution Plan a spusťte dotaz.
SELECT P.LastName ,P.FirstName ,E.EmailAddress FROM Person.Person AS P LEFT JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID WHERE P.LastName = 'Smith'
Uvidíme následující plán.
Plán v SQL Sentry Plan Exploreru bude vypadat následovně:
Plán obsahuje množství operací. Pokud čteme plán zleva doprava, čteme jej ve směru Control Flow. Směr zprava doleva značí směr Data Flow. V následujícím výkladu se přidržím čtení plánu ve směru toku dat.
První operací je vyfiltrování řádků v tabulce [Person].[Person] podle zadané podmínky LastName = ‚Smith‘. Pro vyhledání řádků se použije index IX_Person_LastName_FirstName_MiddleName, který obsahuje sloupec LastName. Obdržená data je potřeba setřídit podle pole BusinessEntityID, proto je druhou operací Sort. SQL Server si tedy připravil první sadu dat, která obsahuje pole LastName a FirstName. Jelikož se v dotazu vyskytuje pole EmailAddress, je potřeba projít tabulku [Person].[EmailAddress]. Tato operace je v plánu označena jako Clustered Index Scan podle primárního klíče, který se skládá z polí BusinessEntityID a EmailAddressID. Máme tedy druhou sadu dat, která je shodou okolností již seřazena podle BusinessEntityID. Předposlední operací tohoto plánu je Merge Join, který má na vstupu dvě sady stejně seřazených dat a provádí jejich sloučení. V případě, že k záznamu z tabulky [Person].[Person] neexistuje záznam v tabulce [Person].[EmailAddress], zobrazí se na výstupu ve sloupci EmailAddress hodnota NULL. Poslední operací toho plánu je zobrazení dat na výstup. Spojnice mezi jednotlivými operacemi značí počet řádků, které jsou přečteny z tabulek, popř. počet řádků, které vyprodukuje konkrétní operace na výstupu. Procentní hodnota nad každou operací značí její náročnost z hlediska CPU+I/O. V Sentry Plan Exploreru lze vybírat výpočet podle tří hodnot: CPU, I/O, CPU+I/O.
Ze záložky Plan Tree můžeme vyčíst předpokládané vs. aktuální počty řádků a jejich náročnost z hlediska CPU (Est CPU Cost) nebo IO (Est IO Cost). Obě jsou bezrozměrné veličiny. Obecně Cost je bezrozměrná veličina, která slouží k relativnímu srovnání jednotlivých operátorů jak v rámci jednoho plánu, tak i různých plánů navzájem.
Jak jsem předeslal již v úvodu, předpokládaný (Est Rows) a skutečný (Actual Rows) počet řádků se liší. Může to být způsobené např. nekatuálními statistikami. Statistiky se váží k jednotlivým sloupcům a popisují strukturu dat. Kolik řádků je v tabulce uloženo, počet různých hodnot ve sloupci, atp. V závislosti na nastavení se statistiky automaticky aktualizují při DELETE/UPDATE/INSERT řádků do tabulky. Aktualizace statistik je poměrně náročný proces na zdroje (CPU, Disk IO, RAM), proto se aktualizace neprovádí při každé jednotlivé operaci. Pokud má tabulka více řádků jak 500 a zároveň bylo změněno 500 + 20% z celkového počtu, provede se aktualizace statistik. Z tohoto tedy vyplývá, že statistiky časem zastarávají. Počet řádků je přímo úměrný velikosti místa v paměti. Je-li očekávaný počet řádků řádově vyšší než skutečný, je velmi pravděpodobné, že mezivýsledky budou uloženy do TempDB na disk. Jakákoli manipulace s daty v TempDB je mnohem pomalejší než přímo v paměti. To vyvolá potřebu jiných operací v plánu a nakonec i zpomalení celého dotazu. Přičemž skutečný počet řádků by se do paměti pohodlně vešel.
Exekuční plán je velmi užitečným pomocníkem pro ladění dotazů a zkoumání, jak SQL Server získává data z tabulek.