Jedným zo spôsobov, ako identifikovať problematické časti exekučného plánu, je porovnanie Actual Execution Plan vs. Estimated Execution Plan. V drvivej väčšine prípadov sú tieto plány identické, rozdiel medzi nimi môže vzniknúť v dôsledku toho, že Estimated Execution Plan obsahuje  odhadované počty záznamov získaných z tabuliek, zatiaľ čo Actual Execution Plan obsahuje tzv. runtime štatistiky (okrem skutočného počtu riadkov aj napr. ActualExecutions, MemoryGrant, DegreeOfParallelism a ďalšie). Príliš veľký rozdiel medzi odhadovaným a skutočným počtom záznamov môže byť jednou z príčin pomalej query.

Actual Execution Plan v grafickej forme môžeme získať štyrmi spôsobmi:

1. SET STATISTICS XML

Na demonštráciu použijem zväčšenú databázu AdventureWorks (vďaka J. Kehayiasovi, skript si môžete stiahnuť z adresy  https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/).
Použitím tohto príkazu pred spustením query sa po jej dobehnutí zobrazí xml reprezentácia Actual Execution Plan:

Actual Execution Plan si po kliknutí na linku môžeme zobraziť aj v SSMS:

2. SQL Server Management Studio

Druhým spôsobom ako získať Actual Execution Plan je jeho vloženie do SQL Server Management Studia pred samotným spustením query pomocou Ctrl+M alebo kliknutím na Include Actual Execution Plan:

3. SQL Server Trace/Profiler

Pre zachytenie Actual Execution Plan môžeme využiť udalosť Showplan XML Statistics Profile:

4. Extended Events – legacy infraštruktúra

Pomocou infraštruktúry Extended Events môžeme na zachytenie Actual Execution Plan využiť udalosť query_post_execution_plan:

A výsledný Actual Execution Plan si môžeme pozrieť po prepnutí do záložky Query Plan:
Nevýhodou prvých dvoch možností získania Actual Execution Plan je, že si vyžadujú aktivitu ešte pred spustením query. Vďaka tomu sú tieto spôsoby získania Actual Execution Plan vhodné najmä pre vývojárov, resp. počas testovania.

Nevýhody tretej a štvrtej možností sú dve:

1. Vysoká réžia
Obidva spôsoby využívajú pre zachytávanie Actual Execution Plan tzv. legacy infraštruktúru, ktorá má réžiu až 75%.

2. Nedostatočná granularita zachytávania Actual Execution Plan
Uvedenými spôsobmi budeme zachytávať Actual Execution Plan pre všetky query v rámci danej session/užívateľa/databázy/aplikácie.

Tieto nedostatky prakticky vylučujú query profiling s využitím legacy infraštruktúry na produkčných serveroch s vysokou záťažou. Ako však získať Actual Execution Plan práve z takéhoto servera?

Problém s vysokou réžiou legacy infraštruktúry odstraňuje SQL Server 2016 SP1 – okrem legacy infraštruktúry prináša možnosť využívania tzv. lightweight infraštruktúry pre query profiling. Táto infraštruktúra je optimalizovaná na čo najmenšie zaťaženie monitorovaného systému, jej réžia predstavuje približne 2%. Štandardne je deaktivovaná a pred monitorovaním je potrebné ju aktivovať jedným z dvoch spôsobov:

1. Zapnutím globálneho trace flagu 7412

2. Povolením query_thread_profile extended event

Problém s nedostatočnou granularitou zachytávania Actual Execution Plan je vyriešený až vo verziách SQL Server 2016 SP2 CU3/2017 CU11/2019. Actual Execution Plan pre individuálnu query môžeme získať pomocou nového USE HINT argumentu – QUERY_PLAN_PROFILE. Tento argument povoľuje lightweight infraštruktúru len pre query, ktorá obsahuje tento query hint. Ako to presne funguje?

V prvom rade musíme našu query doplniť o query hint QUERY_PLAN_PROFILE, ktorý použijeme ako argument query hintu USE HINT:

Po dobehnutí query sa spustí nový Extended Event – query_plan_profile, ktorý obsahuje Actual Execution Plan v tvare XML aj s runtime štatistikami:
V tomto prípade nemáme k dispozícii záložku Query Plan, takže si musíme pomôcť skopírovaním XML reprezentácie Actual Execution Plan buď do SSMS, alebo do Plan Explorer:
Potom môžeme skontrolovať Actual Execution Plan (Plan Explorer):
Tento postup prináša ale jednu zásadnú nevýhodu – nutnosť modifikácie požadovanej query v aplikácii. Ak však nemáme takúto možnosť, môžeme použiť iný, elegantnejší spôsob na dosiahnutie rovnakého cieľa – Plan Guides. Plan Guides nám umožňujú pridanie query hintu pre ľubovoľnú query bez nutnosti modifikácie zdrojového kódu:
Teraz môžeme spustiť našu query, ale tentokrát bez query hintu:
A tu je výsledok:
QUERY_PLAN_PROFILE je užitočný hint, ktorý uľahčí život mnohým databázovým administrátorom pri riešení produkčných problémov 🙂

Nech sa disky všetkých SQL Server večne točia!

Karol