Prozkoumání úložiště dotazů

Dokončeno

Úložiště dotazů SQL Serveru je funkce pro každou databázi, která automaticky zachycuje historii dotazů, plánů a statistik modulu runtime, což zjednodušuje řešení potíží s výkonem a ladění dotazů. Poskytuje také přehled o vzorech využití databáze a spotřebě prostředků.

Úložiště dotazů se skládá ze tří úložišť:

• Úložiště plánů: Ukládá odhadované informace o plánu provádění.
• Úložiště statistik modulu runtime: Ukládá informace o statistikách provádění.
• Úložiště statistik čekání: Uchovává informace o statistikách čekání.

Povolení úložiště dotazů

Úložiště dotazů je ve výchozím nastavení povolené v databázích Azure SQL. Pokud ho chcete použít s SQL Serverem a Azure Synapse Analytics, musíte ho nejdřív povolit. Pokud chcete povolit funkci Úložiště dotazů, použijte následující dotaz platný pro vaše prostředí:

-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;

Jak úložiště dotazů shromažďuje data

Úložiště dotazů se integruje s kanálem zpracování dotazů ve více fázích. V každém bodu integrace se data shromažďují v paměti a zapisují se na disk asynchronně, aby se minimalizovaly režijní náklady na vstupně-výstupní operace. Integrační body jsou následující:

1. Při prvním spuštění dotazu se text dotazu a počáteční odhadovaný plán provádění odešlou do úložiště dotazů a zachovají se.

2. Plán se aktualizuje v úložišti dotazů při opětovném kompilaci dotazu. Pokud výsledkem rekompilu je nově vygenerovaný plán provádění, zachová se také v úložišti dotazů, aby se rozšířily předchozí plány. Úložiště dotazů navíc sleduje statistiky provádění pro každý plán dotazů pro účely porovnání.

3. Během kompilace a kontroly rekompilovaných fází úložiště dotazů identifikuje, jestli existuje vynucený plán pro spuštění dotazu. Dotaz se znovu zkompiluje, pokud úložiště dotazů poskytuje vynucený plán, který se liší od plánu v mezipaměti procedur.

4. Když se dotaz spustí, jeho statistiky modulu runtime se zachovají v úložišti dotazů. Úložiště dotazů agreguje tato data, aby bylo zajištěno přesné znázornění každého plánu dotazu.

Další informace o tom, jak úložiště dotazů shromažďuje data, najdete v tématu Jak úložiště dotazů shromažďuje data.

Obvyklé scénáře

Úložiště dotazů SQL Serveru poskytuje cenné přehledy o výkonu databázových operací. Mezi běžné scénáře patří:

• Identifikace a oprava regresí výkonu kvůli nižšímu výběru plánu provádění dotazů
• Identifikace a ladění dotazů s nejvyšší spotřebou prostředků
• Testování A/B pro vyhodnocení dopadů změn databáze a aplikací
• Zajištění stability výkonu po upgradu SQL Serveru
• Určení nejčastěji používaných dotazů
• Auditování historie plánů dotazů pro dotaz
• Identifikace a vylepšení neplánovaných úloh
• Pochopení nejčastějších kategorií čekání databáze a přispívajících dotazů a plánů ovlivňujících dobu čekání
• Analýza vzorů využití databáze v průběhu času z hlediska spotřeby prostředků (procesor, vstupně-výstupní operace, paměť).

Zjištění zobrazení úložiště dotazů

Jakmile je úložiště dotazů pro databázi povolené, složka Úložiště dotazů se zobrazí pro databázi v Průzkumník objektů. Pro Azure Synapse Analytics se zobrazení úložiště dotazů zobrazí v části Systémová zobrazení. Zobrazení úložiště dotazů poskytují agregované a rychlé přehledy o aspektech výkonu databáze SQL Serveru.

Dotazy s nižším výkonem

Regresní dotaz má za sebou snížení výkonu v průběhu času kvůli změnám plánu provádění. Odhadované plány provádění se můžou měnit z důvodu různých faktorů, včetně změn schématu, změn statistik a změn indexu. Zkoumání mezipaměti procedur může být prvním instinktem, ale ukládá pouze nejnovější plán spuštění dotazu a plány je možné vyřadit na základě požadavků na paměť systému. Úložiště dotazů však uchovává několik plánů provádění pro každý dotaz, což umožňuje flexibilitu zvolit konkrétní plán prostřednictvím vynucení plánu pro řešení regrese výkonu dotazů způsobené změnami plánu.

Zobrazení Regressed Queries dokáže určit dotazy, jejichž metriky spouštění se zhoršují v důsledku změn v plánu provádění v zadaném časovém rámci. Toto zobrazení umožňuje filtrování na základě vybrané metriky (například doby trvání, času procesoru, počtu řádků a dalších) a statistiky (celkem, průměr, minimum, maximum nebo směrodatná odchylka). Pak vypíše prvních 25 dotazů s nižším přenosem dat na základě zadaného filtru. Ve výchozím nastavení se zobrazí grafické zobrazení pruhového grafu dotazů, ale volitelně můžete zobrazit dotazy ve formátu mřížky.

Po výběru dotazu z levého horního podokna dotazu se v podokně souhrnu plánu zobrazí trvalé plány dotazů přidružené k dotazu v průběhu času. Výběrem plánu dotazu v podokně Souhrn plánu se v dolním podokně zobrazí grafický plán dotazu. Tlačítka panelu nástrojů v podokně souhrnu plánu i v podokně grafického plánu dotazu umožňují vynutit vybraný plán pro vybraný dotaz. Tato struktura a chování podokna se konzistentně používají ve všech zobrazeních dotazů SQL.

Případně můžete pomocí sp_query_store_force_plan uložené procedury použít vynucení plánu.

EXEC sp_query_store_force_plan @query_id=73, @plan_id=79

Celkové využití prostředku

Zobrazení Celkové využití prostředků umožňuje analyzovat celkovou spotřebu prostředků pro více metrik provádění (například počet spuštění, dobu trvání, dobu čekání a další) pro zadaný časový rámec. Vykreslené grafy jsou interaktivní; Při výběru míry z jednoho z grafů se na nové kartě zobrazí zobrazení přechodu k podrobnostem zobrazující dotazy spojené s vybranou mírou.

Zobrazení podrobností obsahuje prvních 25 dotazů příjemce prostředků, které přispěly k vybrané metrice. Toto zobrazení podrobností používá konzistentní rozhraní, které umožňuje kontrolu přidružených dotazů a jejich podrobností, vyhodnocení uložených odhadovaných plánů dotazů a volitelně použití vynucení plánu ke zlepšení výkonu. Toto zobrazení je užitečné, když se kolize systémových prostředků stane problémem, například když využití procesoru dosáhne kapacity.

Dotazy nejvíce využívající prostředky

Zobrazení Dotazy s nejvyšším využitím prostředků je podobné podrobnému zobrazení Celkového využití prostředků. Umožňuje také vybrat metriku a statistiku jako filtr. Zobrazené dotazy jsou ale 25 nejvýraznějších dotazů na základě zvoleného filtru a časového rámce.

Zobrazení Dotazy s nejvyšším využitím prostředků poskytuje první indikaci neplánované povahy úlohy při identifikaci a vylepšování neplánovaných úloh. Na následujícím obrázku jsou například vybrány metriky Počet spuštění a celková statistika, které odhalí přibližně 90% dotazů s nejvyšším využitím prostředků pouze jednou.

Dotazy s vynucenými plány

Zobrazení Dotazy s vynucenými plány poskytuje rychlý přehled o dotazech, které mají vynucené plány dotazů. Toto zobrazení se stane relevantním v případě, že vynucený plán přestane fungovat podle očekávání a je potřeba ho znovu vyhodnotit. Toto zobrazení poskytuje možnost zkontrolovat všechny trvalé odhadované plány provádění vybraného dotazu, které snadno určují, jestli je teď pro výkon vhodnější jiný plán. Pokud ano, tlačítka panelu nástrojů jsou k dispozici pro zrušení vynucovat plán podle potřeby.

Dotazy s vysokou variantou

Výkon dotazů se může mezi spuštěními lišit. Zobrazení Dotazy s vysokou variantou obsahuje analýzu dotazů, které mají nejvyšší odchylku nebo směrodatnou odchylku pro vybranou metriku. Rozhraní je konzistentní s většinou zobrazení úložiště dotazů, které umožňuje kontrolu podrobností dotazu, vyhodnocení plánu spuštění a volitelně vynucení konkrétního plánu. Pomocí tohoto zobrazení můžete ladit nepředvídatelné dotazy na konzistentnější vzor výkonu.

Statistika čekání dotazu

Zobrazení Statistika čekání dotazu analyzuje nejaktivnější kategorie čekání databáze a vykreslí graf. Tento graf je interaktivní; Výběrem kategorie čekání přejdete k podrobnostem dotazů, které přispívají ke statistikě doby čekání.

Rozhraní zobrazení podrobností je také konzistentní s většinou zobrazení úložiště dotazů umožňujících kontrolu podrobností dotazu, vyhodnocení plánu provádění a volitelně vynucení konkrétního plánu. Toto zobrazení pomáhá identifikovat dotazy, které ovlivňují uživatelské prostředí napříč aplikacemi.

Sledovací dotaz

Zobrazení sledovacího dotazu umožňuje analyzovat konkrétní dotaz na základě zadané hodnoty ID dotazu. Po spuštění zobrazení poskytuje kompletní historii provádění dotazu. Značka zaškrtnutí při provádění označuje, že byl použit vynucený plán. Toto zobrazení může poskytnout přehled o dotazech, jako jsou dotazy s vynucenými plány, abyste ověřili, že výkon dotazů zůstává stabilní.

Použití úložiště dotazů k vyhledání čekání dotazů

Když se výkon systému začne snižovat, je vhodné poradit se statistikou čekání na dotazy, aby se potenciálně identifikovala příčina. Kromě identifikace dotazů, které je potřeba ladit, může také odhalit potenciální upgrady infrastruktury, které by byly přínosné.

Úložiště dotazů SQL poskytuje zobrazení Statistika čekání dotazu , které poskytuje přehled o hlavních kategoriích čekání databáze. V současné době existuje 23 kategorií čekání.

Pruhový graf zobrazuje nejvýraznější kategorie čekání databáze při otevření zobrazení Statistika čekání dotazu. Kromě toho filtr umístěný na panelu nástrojů podokna kategorií čekání umožňuje vypočítat statistiky čekání na základě celkové doby čekání (výchozí), průměrné doby čekání, minimální doby čekání, maximální doby čekání nebo standardní doby čekání.

Výběrem kategorie čekání přejdete k podrobnostem dotazů, které přispívají k dané kategorii čekání. Z tohoto zobrazení máte možnost prozkoumat jednotlivé dotazy, které mají největší dopad. K trvalým odhadovaným plánům provádění, které se zobrazí v podokně souhrnu plánu, můžete získat přístup výběrem dotazu v podokně dotazu. Výběrem plánu dotazu v podokně souhrnu plánu se zobrazí grafický plán dotazu v dolním podokně. Z tohoto zobrazení máte možnost vynutit nebo zrušit vynucení plánu dotazu, aby se zlepšil výkon dotazu.

Automatická oprava plánu

Služby SQL Server 2017 a Azure SQL Database zavedly koncept automatické opravy plánu pomocí analýzy dat v úložišti dotazů. Když je úložiště dotazů povoleno s databází v SQL Serveru 2017 (nebo novějším) a v Azure SQL Database, bude stroj SQL Serveru hledat regrese plánu dotazů a poskytovat doporučení. Tato doporučení můžete zobrazit v sys.dm_db_tuning_recommendations zobrazení dynamické správy (DMV). Tato doporučení budou zahrnovat příkazy T-SQL pro ruční vynucení plánu dotazů, pokud se výkon nacházel „v dobrém stavu“.

Pokud získáte důvěru v tato doporučení, můžete povolit, aby SQL Server vynucoval plány automaticky, když dojde k regresím. Povolte automatickou opravu plánu pomocí ALTER DATABASE argumentu a argumentu AUTOMATIC_TUNING .

Pro Azure SQL Database můžete také povolit automatickou opravu plánu pomocí možností automatického ladění na webu Azure Portal nebo v rozhraních REST API. Doporučení automatické opravy plánu jsou vždycky povolená pro všechny databáze s povoleným úložištěm dotazů (to je výchozí nastavení pro Azure SQL Database a spravovanou instanci Azure SQL). U nových databází je automatická oprava plánu (FORCE_PLAN) ve výchozím nastavení povolená pro Azure SQL Database.