Optimalizace výkonu pomocí úložiště dotazů

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ů SQL Serveru umožňuje zjišťovat a ladit dotazy ve vaší úloze prostřednictvím vizuálního rozhraní aplikace SQL Server Management Studio a dotazů T-SQL. Tento článek podrobně popisuje, jak můžete využít využitelné informace ke zlepšení výkonu dotazů v databázi, včetně toho, jak identifikovat dotazy na základě jejich statistik využití a vynucení plánů provádění. Pomocí funkce nápovědy k úložišti dotazů můžete také identifikovat dotazy a tvarovat jejich plány dotazů beze změny kódu aplikace.

• Další informace o tom, jak se tato data shromažďují, najdete v tématu Jak úložiště dotazů shromažďuje data.
• Další informace o konfiguraci a správě pomocí úložiště dotazů najdete v tématu Monitorování výkonu pomocí úložiště dotazů.
• Informace o provozu úložiště dotazů ve službě Azure SQL Database najdete v tématu Provoz úložiště dotazů ve službě Azure SQL Database.

Příklady dotazů na optimalizaci výkonu

Úložiště dotazů uchovává historii metrik kompilace a modulu runtime během provádění dotazů a umožňuje klást otázky týkající se úloh.

Následující ukázkové dotazy můžou být užitečné v rámci základních ukazatelů výkonu a analýzy výkonu dotazů.

Poslední dotazy spuštěné v databázi

Poslední n dotazů spuštěných v databázi během poslední hodiny:

SELECT TOP 10 qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.last_execution_time DESC;

Počty spuštění

Počet spuštění každého dotazu během poslední hodiny:

SELECT q.query_id,
    qt.query_text_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY q.query_id,
    qt.query_text_id,
    qt.query_sql_text
ORDER BY total_execution_count DESC;

Nejdelší průměrná doba provádění

Počet dotazů s nejvyšší průměrnou dobou trvání během poslední hodiny:

SELECT TOP 10 ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) /
        NULLIF(SUM(rs.count_executions), 0), 2) avg_duration,
    SUM(rs.count_executions) AS total_execution_count,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    GETUTCDATE() AS CurrentUTCTime,
    MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id
ORDER BY avg_duration DESC;

Nejvyšší průměrné fyzické vstupně-výstupní čtení

Počet dotazů, které za posledních 24 hodin měly největší průměrný počet fyzických vstupně-výstupních operací, s odpovídajícím průměrným počtem řádků a počtem spuštění:

SELECT TOP 10 rs.avg_physical_io_reads,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.runtime_stats_id,
    rsi.start_time,
    rsi.end_time,
    rs.avg_rowcount,
    rs.count_executions
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;

Dotazy s více plány

Dotazy s více než jedním plánem jsou obzvláště zajímavé, protože mohou být kandidáty na regresi výkonu v důsledku změny ve volbě plánu.

Následující dotaz identifikuje dotazy s nejvyšším počtem plánů během poslední hodiny:

SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    COUNT(*) AS QueryPlanCount,
    STRING_AGG(p.plan_id, ',') plan_ids,
    qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY OBJECT_NAME(object_id),
    q.query_id,
    qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY QueryPlanCount DESC;

Následující dotaz identifikuje tyto dotazy spolu se všemi plány během poslední hodiny:

WITH Query_MultPlans
AS (
    SELECT COUNT(*) AS QueryPlanCount,
        q.query_id
    FROM sys.query_store_query_text AS qt
    INNER JOIN sys.query_store_query AS q
        ON qt.query_text_id = q.query_text_id
    INNER JOIN sys.query_store_plan AS p
        ON p.query_id = q.query_id
    GROUP BY q.query_id
    HAVING COUNT(DISTINCT plan_id) > 1
)
SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    query_sql_text,
    p.plan_id,
    p.query_plan AS plan_xml,
    p.last_compile_start_time,
    p.last_execution_time
FROM Query_MultPlans AS qm
INNER JOIN sys.query_store_query AS q
    ON qm.query_id = q.query_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY q.query_id,
    p.plan_id;

Nejvyšší doby čekání

Tento dotaz vrátí prvních 10 dotazů s nejvyšší dobou čekání za poslední hodinu:

SELECT TOP 10 qt.query_text_id,
    q.query_id,
    p.plan_id,
    sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
INNER JOIN sys.query_store_plan p
    ON ws.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_text_id,
    q.query_id,
    p.plan_id
ORDER BY sum_total_wait_ms DESC;

Poznámka

V Azure Synapse Analytics jsou ukázkové dotazy úložiště dotazů uvedené v této části podporovány s výjimkou statistik čekání, které nejsou dostupné v DMVs úložiště dotazů Azure Synapse Analytics.

Dotazy, u kterých došlo k nedávnému snížení výkonu

Následující příklad dotazu vrátí všechny dotazy, pro které se doba provádění zdvojnásobila za posledních 48 hodin kvůli změně volby plánu. Tento dotaz porovnává všechny intervaly statistik modulu runtime vedle sebe:

SELECT qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    rs1.runtime_stats_id AS runtime_stats_id_1,
    rsi1.start_time AS interval_1,
    p1.plan_id AS plan_1,
    rs1.avg_duration AS avg_duration_1,
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2,
    rsi2.start_time AS interval_2,
    rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi2
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
    AND rsi2.start_time > rsi1.start_time
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > 2 * rs1.avg_duration
ORDER BY q.query_id,
    rsi1.start_time,
    rsi2.start_time;

Pokud chcete zobrazit všechny regrese výkonu (nejen regrese související se změnou výběru plánu), odeberte podmínku AND p1.plan_id <> p2.plan_id z předchozího dotazu.

Dotazy s historickou regresí výkonu

Pokud chcete porovnat nedávné spuštění s historickým spuštěním, následující dotaz porovnává provádění dotazů na základě období provádění. V tomto konkrétním příkladu dotaz porovnává provádění v nedávném období (1 hodina) versus historickým obdobím (poslední den) a identifikuje ty, které zavedly additional_duration_workload. Tato metrika se vypočítá jako rozdíl mezi nedávným průměrným spuštěním a historickým průměrným spuštěním, vynásobený počtem nedávných spuštění. O kolik se prodloužila doba trvání těchto nedávných spuštění v porovnání s předchozími:

--- "Recent" workload - last 1 hour
DECLARE @recent_start_time DATETIMEOFFSET;
DECLARE @recent_end_time DATETIMEOFFSET;

SET @recent_start_time = DATEADD(hour, - 1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload
DECLARE @history_start_time DATETIMEOFFSET;
DECLARE @history_end_time DATETIMEOFFSET;

SET @history_start_time = DATEADD(hour, - 24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();

WITH hist AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @history_start_time
        AND rs.last_execution_time < @history_end_time
    )
    OR (
        rs.first_execution_time <= @history_start_time
        AND rs.last_execution_time > @history_start_time
    )
    OR (
        rs.first_execution_time <= @history_end_time
        AND rs.last_execution_time > @history_end_time
    )
    GROUP BY p.query_id
),
recent AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @recent_start_time
        AND rs.last_execution_time < @recent_end_time
    )
    OR (
        rs.first_execution_time <= @recent_start_time
        AND rs.last_execution_time > @recent_start_time
    )
    OR (
        rs.first_execution_time <= @recent_end_time
        AND rs.last_execution_time > @recent_end_time
    )
    GROUP BY p.query_id
    )
SELECT results.query_id AS query_id,
    results.query_text AS query_text,
    results.additional_duration_workload AS additional_duration_workload,
    results.total_duration_recent AS total_duration_recent,
    results.total_duration_hist AS total_duration_hist,
    ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
    ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM (
    SELECT hist.query_id AS query_id,
        qt.query_sql_text AS query_text,
        ROUND(CONVERT(FLOAT, recent.total_duration / recent.count_executions - hist.total_duration / hist.count_executions) * (recent.count_executions), 2) AS additional_duration_workload,
        ROUND(recent.total_duration, 2) AS total_duration_recent,
        ROUND(hist.total_duration, 2) AS total_duration_hist,
        recent.count_executions AS count_executions_recent,
        hist.count_executions AS count_executions_hist
    FROM hist
    INNER JOIN recent
        ON hist.query_id = recent.query_id
    INNER JOIN sys.query_store_query AS q
        ON q.query_id = hist.query_id
    INNER JOIN sys.query_store_query_text AS qt
        ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);

Údržba stability výkonu dotazů

U dotazů spuštěných vícekrát si můžete všimnout, že SQL Server používá různé plány, což vede k jinému využití prostředků a době trvání. S úložištěm dotazů můžete zjistit, kdy se výkon dotazů zmenšuje, a určit optimální plán v rámci období zájmu. Pak můžete použít tento optimální plán pro další provádění dotazů.

Můžete také identifikovat nekonzistentní výkon dotazu s parametry (buď automaticky parametrizovaný, nebo ručně parametrizovaný). V různých plánech můžete identifikovat plán, který je rychlý a optimální pro všechny nebo většinu hodnot parametrů a vynutit tento plán. Tím zajistíte předvídatelný výkon pro širší sadu uživatelských scénářů.

Vynutit plán pro dotaz (uplatnění zásad vynucení)

Pokud je plán vynucen pro určitý dotaz, SQL Server se pokusí vynutit plán v optimalizátoru. Pokud dojde k selhání vynucení plánu, je aktivována rozšířená událost a optimalizátoru je dá pokyn k optimalizaci běžným způsobem.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

Při použití sp_query_store_force_planmůžete vynutit, aby úložiště dotazů zaznamenalo plány pouze jako plán pro tento konkrétní dotaz. Jinými slovy, jedinými dostupnými plány pro dotaz jsou plány, které se už použily k provedení dotazu, zatímco úložiště dotazů bylo aktivní.

Poznámka

Vynucení plánů v úložišti dotazů se ve službě Azure Synapse Analytics nepodporuje.

Podpora vynucení plánování pro rychlé dopředu a statické kurzory

V SQL Serveru 2019 (15.x) a novějších verzích a v Azure SQL Database (ve všech modelech nasazení) podporuje Query Store možnost vynutit vykonávací plány pro fast-forward, statické Transact-SQL a API kurzory. Vynucení se podporuje prostřednictvím sp_query_store_force_plan nebo prostřednictvím sestav Query Store v SQL Server Management Studio.

Odebrání vynucení plánu pro dotaz

Pokud se chcete znovu spolehnout na optimalizátor dotazů SQL Serveru k výpočtu optimálního plánu dotazů, použijte sp_query_store_unforce_plan k vynucování plánu vybraného pro dotaz.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

Související obsah

• Monitorování výkonu pomocí úložiště dotazů
• osvědčený postup s úložiště dotazů
• Použití úložiště dotazů s In-Memory OLTP
• scénáře použití úložiště dotazů
• Jak úložiště dotazů shromažďuje data
• uložené procedury úložiště dotazů (Transact-SQL)
• Zobrazení katalogu Query Store (Transact-SQL)
• Otevřete nástroj Monitor aktivity (SQL Server Management Studio)
• Statistiky živého dotazu
• monitorování aktivit
• sys.database_query_store_options (Transact-SQL)
• Monitorování a ladění výkonu
• nástroje pro monitorování výkonu a ladění
• nápovědy k úložišti dotazů
• Ladění databáze pomocí pracovní zátěže z úložiště dotazů s poradcem pro ladění databázového stroje