Parameter Sniffing v SQL Serveru 2019 Miloslav Peterka

Parameter Sniffing v SQL Serveru 2019 Miloslav Peterka, Solitea BI Experts, s. r. o. MCSE: Data Platform | Business Intelligence | Data Management and Analytics miloslav. peterka@biexperts. cz

Obsah § § Co je parameter sniffing Možnosti obrany Adaptive Memory Grants Adaptive Joins

Exekuční plán § Sekvence operací pro vyřešení dotazu § Výsledek optimalizace • Velice náročná na zdroje a čas § SQL Server kešuje exekuční plány v plan cache § Při opakovaném volání téhož dotazu se použije již existující plán • Šetří čas a zdroje zejména pro často používané dotazy § Text dotazu rozhodující

Parameter sniffing (Sniff – čichat, čenichat, větřit, šňupat, …) § Proces tvorby exekučního plánu pro parametrizované dotazy • Exekuční plán je vytvořen s hodnotou parametrů při prvním spuštění • Exekuční plán je uložen do procedurální keše • Další spuštění procedury použije zakešovaný plán i pro jiné hodnoty parametrů Ten nemusí být optimální, jiná hodnota parametru může vézt ke zpracování diametrálně odlišného počtu řádků § Stejný dotaz je někdy rychlý a někdy pomalý při stejné hodnotě parametru

Demo 1 § Parameter sniffing

Jak řešit problémy s parameter sniffingem § Rekompilace exekučního plánu • na úrovni dotazu, ne procedury § Dynamické SQL • Bude se generovat nový plán, pokud se bude text dotazu lišit • Pokud se hodnota parametru nezmění, použije existující plán § Optimalizace pro hodnotu parametru • UNKNOWN (přiřazení parametru do lokální proměnné má stejný efekt) • Specifickou § Větvení kódu dle hodnoty parametru

Demo 2 § Možnosti řešení problémů s parameter sniffingem

Adaptive Memory Grant § Memory Grant – paměť potřebná pro vykonání dotazu (ex. plánu) • Stanovena během kompilace Na základě odhadu kardinality • Podhodnocený odhad – data odlita do tempdb • Nadhodnocený odhad – plýtvání zdroji § Memory Grant Feedback • Kompilace a vykonání dotazu beze změny • U zakešovaného plánu následně přizpůsoben Memory Grant

Adaptive Memory Grant § § Snížen, pokud bylo více než 50% paměti nevyužito Zvýšen, pokud se objeví Spill Zohledňuje pouze poslední vykonání dotazu Informace pouze v procedurální keši • Není serializována • Mizí s plánem § Při neúspěšné adaptaci reset na původní hodnotu

Demo 3 § Adaptive Memory Grant

Adaptive Joins § Pro spojování tabulek tři základní algoritmy • HASH • NESTED LOOPS • MERGE § Adaptive Join umožňuje dynamicky vybírat mezi HASH a NESTED LOOP

Demo 4 § Adaptive Joins

Děkuji vám za pozornost! Miloslav Peterka, Solitea BI Experts, s. r. o. MCSE: Data Platform | Business Intelligence | Data Management and Analytics miloslav. peterka@biexperts. cz