Justera prestanda med Query Store

gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (endast dedikerad SQL-pool)SQL-databas i Microsoft Fabric

Sql Server Query Store-funktionen ger dig kraften att identifiera och finjustera frågor i din arbetsbelastning via det visuella gränssnittet i SQL Server Management Studio och via T-SQL-frågor. Den här artikeln beskriver hur du kan vidta åtgärdsbar information för att förbättra frågeprestandan i databasen, inklusive hur du identifierar frågor baserat på deras användningsstatistik och tvingar fram planer. Du kan också använda Query Store-tips funktionen för att identifiera frågor och forma deras frågeplaner utan att ändra programkod.

• Mer information om hur dessa data samlas in finns i How Query Store collects data.
• Mer information om hur du konfigurerar och administrerar med Query Store finns i Övervakningsprestanda med hjälp av Query Store-.
• Information om hur du kör Query Store i Azure SQL Database finns i Använda Query Store i Azure SQL Database.

Exempelfrågor för prestandajustering

Query Store har en historik över kompilerings- och exekveringsmått under frågornas körningar, vilket gör att du kan ställa frågor om din arbetslast.

Följande exempelfrågor kan vara användbara i din prestandabaslinje och frågeprestandaundersökning:

De senaste frågorna som kördes i databasen

De senaste förfrågningar som körts på databasen under den senaste timmen:

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;

Antal körningar

Antal exekveringar för varje fråga under den senaste timmen:

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;

Längsta genomsnittliga exekveringstid

Antalet frågor med den högsta genomsnittliga varaktigheten under den senaste timmen:

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;

Högsta genomsnittliga fysiska I/O-läsningar

Antalet frågor som hade de största genomsnittliga fysiska I/O-läsningarna under de senaste 24 timmarna, med motsvarande genomsnittligt antal rader och körningsantal:

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;

Frågor med flera planer

Frågor med mer än en plan är särskilt intressanta, eftersom de kan vara kandidater för en regression i prestanda på grund av en ändring i planvalet.

Följande fråga identifierar de frågor som har det högsta antalet planer under den senaste timmen:

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;

Följande fråga identifierar dessa frågor tillsammans med alla planer under den senaste timmen:

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;

Högsta väntetid

Den här frågan returnerar de 10 vanligaste frågorna med de högsta väntetiderna för den senaste timmen:

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;

Obs

I Azure Synapse Analytics stöds frågearkivexempelfrågorna i det här avsnittet med undantag för väntestatistik, som inte är tillgängliga i Azure Synapse Analytics Query Store DMV:er.

Sökfrågor som nyligen har försämrats i prestanda

Exempel på fråga nedan returnerar alla frågor där exekveringstiden har fördubblats under de senaste 48 timmarna på grund av en ändring av planval. Den här frågan jämför alla körningsstatistikintervall sida vid sida:

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;

Om du vill se alla prestandaregressioner (inte bara regressioner relaterade till planvalsändring) tar du bort villkoret AND p1.plan_id <> p2.plan_id från föregående fråga.

Frågor med historisk regression i prestanda

När du vill jämföra den senaste körningen med historisk körning jämför följande fråga frågekörning baserat på körningsperioden. I det här exemplet jämför frågan körningen under den senaste perioden (1 timme) med historikperioden (föregående dag) och identifierar de som introducerade additional_duration_workload. Det här måttet beräknas som en skillnad mellan den senaste genomsnittliga exekveringen och den historiska genomsnittliga exekveringen multiplicerat med antalet senaste exekveringar. Det representerar hur mycket extra tid de senaste körningarna har tillfört jämfört med historiken.

--- "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);

Upprätthålla stabilitet för frågeprestanda

För frågor som körs flera gånger kanske du märker att SQL Server använder olika planer, vilket resulterar i olika resursanvändning och varaktighet. Med Query Store kan du identifiera när frågeprestandan har regresserats och fastställa den optimala planen inom en period av intresse. Du kan sedan framtvinga den optimala planen för framtida frågeexekvering.

Du kan också identifiera inkonsekventa frågeprestanda för en fråga med parametrar (antingen automatiskt parametriserade eller manuellt parametriserade). Bland olika planer kan du identifiera den plan som är snabb och optimal nog för alla eller de flesta parametervärdena och framtvinga planen. Detta behåller förutsägbara prestanda för en bredare uppsättning användarscenarier.

Framtvinga en plan för en fråga (tillämpa tvingad princip)

När en plan tvingas för en viss fråga försöker SQL Server framtvinga planen i optimeraren. Om planforcering misslyckas utlöses en utökad händelse och optimeraren instrueras att optimera på vanligt sätt.

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

När du använder sp_query_store_force_plankan du bara tvinga fram planer som registrerats av Query Store som en plan för den frågan. Med andra ord är de enda planer som är tillgängliga för en fråga planer som redan användes för att köra frågan medan Query Store var aktivt.

Not

Tvinga planer i Query Store stöds inte i Azure Synapse Analytics.

Förbereda stöd för snabba och statiska markörer

I SQL Server 2019 (15.x) och senare versioner samt Azure SQL Database (alla distributionsmodeller) har Query Store stöd för att framtvinga körningsplaner för 'fast forward' och statiska Transact-SQL- och API-kursorer. Tvångsåtgärder stöds antingen genom sp_query_store_force_plan eller via rapporterna i SQL Server Management Studio Query Store.

Ta bort planforcering för en fråga

Om du vill förlita dig igen på SQL Server-frågeoptimeraren för att beräkna den optimala frågeplanen använder du sp_query_store_unforce_plan för att ta bort den plan som valdes för frågan.

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

Relaterat innehåll

• Övervakning av prestanda med hjälp av Query Store
• Bästa praxis med Query Store
• Använda Query Store med In-Memory OLTP
• Användningsscenarier för frågearkiv
• Hur Query Store samlar in data
• lagrade procedurer för Query Store (Transact-SQL)
• katalogvyer för Query Store (Transact-SQL)
• Öppna Aktivitetsövervakaren (SQL Server Management Studio)
• Statistik för livefrågor
• Aktivitetsövervakare
• sys.database_query_store_options (Transact-SQL)
• Övervaka och finjustera prestanda
• verktyg för prestandaövervakning och justering
• Query Store-anvisningar
• Optimering av databasen med arbetsbelastning från Query Store med Database Engine Tuning Advisor