Sdílet prostřednictvím

Monitorování výkonu pomocí úložiště dotazů

  • Článek

platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (pouze vyhrazený fond SQL)databázi SQL v Microsoft Fabric

Funkce úložiště dotazů poskytuje přehled o výběru a výkonu plánu dotazů pro SQL Server, Azure SQL Database, Databázi Fabric, Azure SQL Managed Instance a Azure Synapse Analytics. Úložiště dotazů zjednodušuje řešení potíží s výkonem tím, že vám pomůže rychle najít rozdíly v výkonu způsobené změnami plánu dotazů. Úložiště dotazů automaticky zaznamenává historii dotazů, plánů a statistik modulu runtime a uchovává je pro vaši kontrolu. Odděluje data podle časových intervalů, abyste viděli vzory využití databáze a pochopili, kdy na serveru došlo ke změnám plánu dotazů.

Úložiště dotazů můžete nakonfigurovat pomocí možnosti ALTER DATABASE SET.

Důležitý

Pokud používáte úložiště dotazů pro přehledy úloh za běhu v SQL Serveru 2016 (13.x), naplánujte instalaci oprav škálovatelnosti výkonu v KB 4340759 co nejdříve.

Povolte úložiště dotazů

  • Úložiště dotazů je ve výchozím nastavení povolené pro nové databáze Azure SQL Database a Azure SQL Managed Instance.
  • Úložiště dotazů není ve výchozím nastavení povolené pro SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x). Ve výchozím nastavení je povolena v režimu READ_WRITE pro nové databáze počínaje SQL Serverem 2022 (16.x). Pokud chcete povolit funkce pro lepší sledování historie výkonu, řešení potíží souvisejících s plánem dotazů a povolení nových funkcí v SQL Serveru 2022 (16.x), doporučujeme povolit úložiště dotazů ve všech databázích.
  • Úložiště dotazů není ve výchozím nastavení povolené pro nové databáze Azure Synapse Analytics.

Použití stránky Úložiště dotazů v aplikaci SQL Server Management Studio

  1. V Průzkumníku objektů klikněte pravým tlačítkem myši na databázi a potom vyberte Vlastnosti.

    Poznámka

    Vyžaduje aspoň verzi 16 sady Management Studio.

  2. V dialogovém okně Vlastnosti databáze vyberte stránku Úložiště dotazů.

  3. V poli Provozní režim (požadováno) vyberte Čtení a zápis.

Použití příkazů Transact-SQL

Pomocí příkazu ALTER DATABASE povolte úložiště dotazů pro danou databázi. Například:

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

Možnosti konfigurace úložiště dotazů v databázi Fabric SQL s ALTER DATABASE jsou aktuálně omezené.

V Azure Synapse Analytics aktivujte úložiště dotazů bez dalších voleb, například:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Další možnosti syntaxe související s úložištěm dotazů najdete v tématu ALTER DATABASE SET Options (Transact-SQL).

Poznámka

Úložiště dotazů nelze povolit pro databáze master ani tempdb.

Důležitý

Informace o povolení úložiště dotazů a jeho nastavení podle vašich úloh najdete v tématu Osvědčený postup pro úložiště dotazů.

Informace v úložišti dotazů

Plány provádění pro každý konkrétní dotaz na SQL Serveru se obvykle vyvíjejí v průběhu času z různých důvodů, jako jsou změny statistiky, změny schématu, vytváření/odstranění indexů atd. Mezipaměť procedur (kde jsou uloženy plány dotazů v mezipaměti) ukládá pouze nejnovější plán spuštění. Plány se také vyřadí z plánové mezipaměti kvůli tlaku na paměť. V důsledku toho mohou být regrese výkonu dotazů způsobené změnami plánů provádění netriviální a časově náročné na vyřešení.

Vzhledem k tomu, že úložiště dotazů uchovává více plánů provádění pro každý dotaz, může vynutit zásady, které procesoru dotazů nasměrují na použití konkrétního plánu provádění dotazu. To se označuje jako nucené prosazení plánu. Vynucení plánů v úložišti dotazů je zajištěno pomocí mechanismu podobného nápovědě k dotazu USE PLAN, ale nevyžaduje žádné změny v uživatelských aplikacích. Vynucení plánu může vyřešit regresi výkonu dotazu způsobenou změnou plánu za velmi krátkou dobu.

Poznámka

Úložiště Query Store shromažďuje plány pro příkazy DML, jako jsou SELECT, INSERT, UPDATE, DELETE, MERGEa BULK INSERT.

Úložiště dotazů záměrně neshromažďuje plány pro příkazy DDL, jako jsou CREATE INDEXatd. Úložiště dotazů zaznamenává kumulativní spotřebu prostředků shromažďováním plánů pro podkladové příkazy DML. Úložiště dotazů může například zobrazit SELECT a INSERT příkazy spouštěné interně za účelem naplnění nového indexu.

Úložiště dotazů ve výchozím nastavení neshromažďuje data pro nativně zkompilované uložené procedury. Pomocí sys.sp_xtp_control_query_exec_stats povolte shromažďování dat pro nativně zkompilované uložené procedury.

statistiky čekání jsou dalším zdrojem informací, které pomáhají řešit potíže s výkonem databázového stroje. Po dlouhou dobu byly statistiky čekání k dispozici pouze na úrovni instance, což ztěžovalo navracení čekání na konkrétní dotaz. Počínaje SQL Serverem 2017 (14.x) a Službou Azure SQL Database obsahuje úložiště dotazů dimenzi, která sleduje statistiky čekání. Následující příklad umožňuje úložišti dotazů shromažďovat statistiky čekání.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Mezi běžné scénáře použití funkce Úložiště dotazů patří:

  • Rychle najděte a opravte regresi výkonu dotazu vynucením předchozího dotazovacího plánu. Opravte dotazy, u kterých došlo k nedávnému snížení výkonu kvůli změnám plánu provádění.
  • Určete, kolikrát byl dotaz ve stanoveném časovém intervalu spuštěn, což pomůže DBA při řešení problémů s výkonem prostředků.
  • V posledních x hodinách identifikujte nejčastější n dotazů (podle doby provádění, spotřeby paměti atd.).
  • Auditujte historii plánů dotazů pro daný dotaz.
  • Analyzujte vzory využití prostředků (procesor, vstupně-výstupní operace a paměť) pro konkrétní databázi.
  • Identifikujte nejčastější dotazy, které čekají na prostředky.
  • Porozumíte povaze čekání na konkrétní dotaz nebo plán.

Úložiště dotazů obsahuje tři úložiště:

  • úložiště plánu pro uchování informací o plánu provádění.
  • statistiky modulu runtime ukládají pro zachování informací o statistikách provádění.
  • úložiště statistik čekání pro uchování informací o statistikách čekání.

Počet jedinečných plánů, které lze uložit pro dotaz v úložišti plánů, je omezen možností konfigurace max_plans_per_query. Kvůli zvýšení výkonu se informace zapisuje do úložišť asynchronně. Aby se minimalizovalo využití místa, statistiky spouštění modulu runtime v úložišti statistik modulu runtime se agregují v rámci pevného časového intervalu. Informace v těchto úložištích jsou viditelné prostřednictvím dotazování zobrazení katalogu Query Storu.

Následující dotaz vrátí informace o dotazech, jejich plánech, čase kompilace a statistikách za běhu z úložiště dotazů.

SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;

Úložiště dotazů pro sekundární repliky

platí pro: SQL Server (počínaje SQL Serverem 2022 (16.x))

Funkce Úložiště dotazů pro sekundární repliky umožňuje stejné funkce úložiště dotazů u úloh sekundární repliky, které jsou k dispozici pro primární repliky. Pokud je povolené úložiště dotazů pro sekundární repliky, repliky odesílají informace o spuštění dotazu, které by se normálně ukládaly do úložiště dotazů zpět na primární repliku. Primární replika pak uchovává data na disk ve vlastním úložišti dotazů. V podstatě existuje jedno úložiště dotazů sdílené mezi primární a všemi sekundárními replikami. Úložiště dotazů existuje na primární replice a ukládá data pro všechny repliky společně.

Úplné informace o úložišti dotazů pro sekundární repliky najdete v tématu Úložiště dotazů pro sekundární repliky skupiny dostupnosti Always On.

Použití funkce Regressed Queries

Po povolení Query Store aktualizujte část databáze v podokně Průzkumníka objektů, aby se přidala sekce Query Store.

Snímek obrazovky sestavy stromu Query Store v Průzkumníku objektů SSMS

Poznámka

Pro Azure Synapse Analytics jsou zobrazení úložiště dotazů k dispozici v části Systémová zobrazení v části Databáze v podokně Průzkumníka objektů.

Výběrem Regressed Queries otevřete podokno Regressed Queries v aplikaci SQL Server Management Studio. V podokně s názvem Regressed Queries se zobrazují dotazy a plány v úložišti dotazů. Pomocí rozevíracích seznamů v horní části můžete filtrovat dotazy na základě různých kritérií: Doba trvání (ms) (výchozí), čas procesoru (ms), logické čtení (KB), logické zápisy (KB), fyzické čtení (KB), čas CLR (ms), DOP, spotřeba paměti (KB), počet řádků, využitá paměť protokolu (KB), využitá paměť databáze tempa (KB) a doba čekání (ms).

Vyberte plán, abyste zobrazili grafický plán dotazu. Tlačítka jsou k dispozici pro zobrazení zdrojového dotazu, vynucení a vynucování plánu dotazu, přepínání mezi formáty mřížky a grafu, porovnání vybraných plánů (pokud je vybráno více než jeden) a aktualizace zobrazení.

snímek obrazovky se sestavou regresních dotazů SQL Serveru v Průzkumníku objektů SSMS

Pokud chcete vynutit plán, vyberte dotaz a plán a pak vyberte Vynutit Plán. Plány uložené funkcí plánu dotazu můžete vynutit pouze v mezipaměti plánu dotazů.

Vyhledání čekajících dotazů

Počínaje SQL Serverem 2017 (14.x) a službou Azure SQL Database jsou v úložišti dotazů k dispozici statistiky čekání na dotaz v průběhu času.

V úložišti dotazů se typy čekání kombinují do kategorií čekání . Mapování kategorií čekání na typy čekání je k dispozici v sys.query_store_wait_stats (Transact-SQL).

Výběrem možnosti Statistika čekání dotazu otevřete podokno Statistika čekání dotazu ve verzi SQL Server Management Studio 18.0 nebo vyšší. Podokno Statistika čekání dotazu zobrazuje pruhový graf obsahující hlavní kategorie čekání v úložišti dotazů. Pomocí rozevíracího seznamu v horní části vyberte agregační kritéria pro dobu čekání: průměr, maximum, minimum, směrodatná odchylka a celkový (výchozí nastavení).

snímek obrazovky se sestavou statistik čekání dotazu SQL Serveru v Průzkumníku objektů SSMS

Vyberte kategorii čekání kliknutím na panel a zobrazí se podrobný pohled na vybranou kategorii čekání. Tento nový pruhový graf obsahuje dotazy, které přispěly k této kategorii čekání.

snímek obrazovky s podrobným zobrazením statistik čekání dotazu SQL Serveru v Průzkumníku objektů SSMS

Pomocí rozevíracího seznamu v horní části můžete filtrovat dotazy na základě různých kritérií čekací doby pro vybranou kategorii čekání: avg, max, min, std dev a celkový počet (výchozí). Vyberte plán, abyste viděli grafický plán dotazu. Tlačítka jsou k dispozici pro zobrazení zdrojového dotazu, vynucení a zrušení vynucení plánu dotazu a aktualizaci zobrazení.

Čekací kategorie kombinují různé typy čekacích dob do skupin podobných charakterem. Různé kategorie čekání vyžadují k vyřešení problému jinou následnou analýzu, ale typy čekání ze stejné kategorie vedou k velmi podobným zkušenostem při řešení problémů a poskytnutí dotazu ovlivněného čekáním by byla chybějící částí k úspěšnému dokončení většiny takových šetření.

Tady je několik příkladů, jak můžete získat další přehled o úlohách před zavedením kategorií čekání v úložišti dotazů a po nich:

Předchozí zkušenost Nové prostředí Akce
Vysoké RESOURCE_SEMAPHORE čekání na databázi Vysoké čekání na paměť v úložišti dotazů pro konkrétní dotazy Vyhledejte dotazy s nejvyšším využitím paměti v úložišti dotazů. Tyto dotazy pravděpodobně zpozdí další průběh ovlivněných dotazů. Zvažte použití nápovědy MAX_GRANT_PERCENT pro tyto dotazy nebo pro dotazy, kterých se to týká.
Vysoké LCK_M_X čekání pro každou databázi Vysoce zatížené uzamčení čeká v úložišti dotazů na konkrétní dotazy. Zkontrolujte texty dotazů pro ovlivněné dotazy a identifikujte cílové entity. Vyhledejte v úložišti dotazů další dotazy, které upravují stejnou entitu, které se provádějí často nebo mají vysokou dobu trvání. Po identifikaci těchto dotazů zvažte změnu logiky aplikace, aby se zlepšila souběžnost, nebo použijte méně omezující úroveň izolace.
Vysoké čekání na PAGEIOLATCH_SH na databázi Vysoké čekání na vstupně-výstupní operace v úložišti dotazů pro konkrétní dotazy. Najděte dotazy s vysokým počtem fyzických přístupů k datům v Úložišti dotazů. Pokud se dotazy shodují s dlouhými čekacími dobami na vstupně-výstupní operace, zvažte zavedení indexu u podkladové entity, aby bylo možno provádět hledání místo prohledávání, a tím minimalizovat vstupně-výstupní režijní náklady dotazů.
Vysoké SOS_SCHEDULER_YIELD čekací doby na jednu databázi Vysoké čekání procesoru v úložišti dotazů pro konkrétní dotazy Vyhledejte dotazy s nejvyšším využitím procesoru v úložišti dotazů. Mezi nimi identifikujte dotazy, u kterých vysoký trend využití procesoru koreluje s vysokými čekacími časy procesoru pro dotazy, které jsou ovlivněny. Zaměřte se na optimalizaci těchto dotazů – může existovat regrese plánu nebo možná chybějící index.

Možnosti konfigurace

Viz dostupné možnosti konfigurace parametrů úložiště dotazů v MOŽNOSTI ALTER DATABASE SET (Transact-SQL) .

Proveďte dotaz na zobrazení sys.database_query_store_options a určete aktuální možnosti Query Store. Další informace o hodnotách najdete v tématu sys.database_query_store_options.

Příklady nastavení možností konfigurace pomocí příkazů Transact-SQL najdete v tématu Správa možností.

Poznámka

Pro Azure Synapse Analytics je možné úložiště dotazů povolit jako na jiných platformách, ale další možnosti konfigurace se nepodporují.

Zobrazte a spravujte úložiště dotazů prostřednictvím sady Management Studio nebo pomocí následujících zobrazení a postupů.

Funkce úložiště dotazů

Funkce pomáhají s operacemi s úložištěm dotazů.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Zobrazení katalogu úložiště dotazů

Zobrazení katalogu zobrazují informace o úložišti dotazů.

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

Uložené procedury v úložišti dotazů

Uložené procedury konfiguruje úložiště dotazů.

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)1

1 V extrémních scénářích může úložiště dotazů kvůli interním chybám zadat stav CHYBY. Počínaje SQL Serverem 2017 (14.x), pokud k tomu dojde, je možné úložiště dotazů obnovit spuštěním sp_query_store_consistency_check uložené procedury v ovlivněné databázi. Další podrobnosti popsané v popisu sloupce actual_state_desc najdete v sys.database_query_store_options.

Údržba úložiště dotazů

Osvědčené postupy a doporučení pro údržbu a správu úložiště dotazů byly rozšířeny v tomto článku: osvědčené postupy pro správu úložiště dotazů.

Auditování výkonu a řešení potíží

Další informace o potápění do ladění výkonu pomocí úložiště dotazů naleznete v tématu Ladění výkonu pomocí úložiště dotazů.

Další témata týkající se výkonu:

Související obsah