Prestaties afstemmen met de Query Store

van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (alleen toegewezen SQL-pool)SQL-database in Microsoft Fabric

De functie SQL Server Query Store biedt u de mogelijkheid om query's in uw workload te detecteren en af te stemmen via de visualinterface van SQL Server Management Studio en via T-SQL-query's. In dit artikel wordt beschreven hoe u bruikbare informatie kunt uitvoeren om de queryprestaties in uw database te verbeteren, waaronder het identificeren van query's op basis van hun gebruiksstatistieken en het afdwingen van plannen. U kunt ook de Query Store-hints functie gebruiken om query's te identificeren en hun queryplannen vorm te geven zonder toepassingscode te wijzigen.

• Zie Hoe Query Store gegevens verzameltvoor meer informatie over hoe deze gegevens worden verzameld.
• Zie Prestaties bewaken met behulp van de Query Store-voor meer informatie over het configureren en beheren met de Query Store.
• Zie Query Store gebruiken in Azure SQL Databasevoor meer informatie over het beheren van de Query Store in Azure SQL Database.

Voorbeeldvragen voor prestatie-optimalisatie

Query Store houdt een geschiedenis bij van metrische gegevens over compilatie en runtime tijdens het uitvoeren van query's, zodat u vragen kunt stellen over uw workload.

De volgende voorbeeldquery's kunnen nuttig zijn in uw prestatiebasislijn en onderzoek naar queryprestaties:

Laatste query's uitgevoerd op de database

De laatste n query's die in het afgelopen uur in de database zijn uitgevoerd:

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;

Aantal uitvoeringen

Het aantal uitvoeringen voor elke query binnen het afgelopen uur:

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;

Langste gemiddelde uitvoeringstijd

Het aantal query's met de hoogste gemiddelde duur binnen het afgelopen uur:

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;

Hoogste gemiddelde fysieke I/O-leesbewerkingen

Het aantal query's met de grootste gemiddelde fysieke I/O-leesbewerkingen in de afgelopen 24 uur, met het bijbehorende gemiddelde aantal rijen en het aantal uitvoeringen:

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;

Query's met meerdere plannen

Query's met meer dan één plan zijn vooral interessant, omdat ze kandidaten kunnen zijn voor een regressie in prestaties vanwege een wijziging in de plankeuze.

De volgende query identificeert de query's met het hoogste aantal plannen binnen het afgelopen uur:

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;

De volgende query identificeert deze query's samen met alle plannen binnen het afgelopen uur:

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;

Hoogste wachttijden

Deze query retourneert de top 10 query's met de hoogste wachttijden voor het afgelopen uur:

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;

Notitie

In Azure Synapse Analytics worden de Query Store-voorbeeldquery's in deze sectie ondersteund, met uitzondering van wachtstatistieken, die niet beschikbaar zijn in de DMV's van azure Synapse Analytics Query Store.

Query's die onlangs zijn achteruitgegaan in prestaties

In het volgende queryvoorbeeld worden alle query's geretourneerd waarvoor de uitvoeringstijd in de afgelopen 48 uur is verdubbeld vanwege een wijziging in de planningskeuze. Deze query vergelijkt alle intervallen van runtime-statistieken naast elkaar.

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;

Als u alle prestatieregressies (niet alleen regressies met betrekking tot plankeuzewijziging) wilt zien, verwijdert u voorwaarde AND p1.plan_id <> p2.plan_id uit de vorige query.

Query's met historische regressie in prestaties

Wanneer u recente uitvoering wilt vergelijken met historische uitvoering, vergelijkt de volgende query de uitvoering van de query op basis van de uitvoeringsperiode. In dit specifieke voorbeeld vergelijkt de query de uitvoering in de recente periode (1 uur) met de historische periode (laatste dag) en identificeert degenen die additional_duration_workloadhebben geïntroduceerd. Deze metrische waarde wordt berekend als een verschil tussen de recente gemiddelde uitvoering en de gemiddelde uitvoeringsgeschiedenis, vermenigvuldigd met het aantal recente uitvoeringen. Het geeft aan hoeveel extra tijdsduur deze recente uitvoeringen hebben geïntroduceerd, vergeleken met de historie.

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

Stabiliteit van queryprestaties behouden

Voor query's die meerdere keren worden uitgevoerd, merkt u mogelijk dat SQL Server verschillende plannen gebruikt, wat resulteert in een ander resourcegebruik en een andere duur. Met Query Store kunt u detecteren wanneer de prestaties van queries zijn verslechterd en het optimale plan binnen een bepaalde periode bepalen. Vervolgens kunt u dit optimale plan afdwingen voor toekomstige uitvoering van query's.

U kunt ook inconsistente queryprestaties voor een query identificeren met parameters (automatisch geparameteriseerd of handmatig geparameteriseerd). Onder verschillende plannen kunt u het plan identificeren dat snel en optimaal genoeg is voor alle of de meeste parameterwaarden en dat plan forceren. Dit zorgt voor voorspelbare prestaties voor de bredere set gebruikersscenario's.

Een plan voor een query afdwingen (het afdwingingsbeleid toepassen)

Wanneer een plan wordt gedwongen voor een bepaalde query, probeert SQL Server het plan af te dwingen in de optimizer. Als het afdwingen van plannen mislukt, wordt een uitgebreide gebeurtenis geactiveerd en wordt de optimizer geïnstrueerd om op de normale manier te optimaliseren.

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

Wanneer u sp_query_store_force_plangebruikt, kunt u alleen plannen afdwingen die door Query Store zijn vastgelegd als plan voor die query. Met andere woorden, de enige plannen die beschikbaar zijn voor een query zijn plannen die al zijn gebruikt om die query uit te voeren terwijl Query Store actief was.

Notitie

Het afdwingen van plannen in Query Store wordt niet ondersteund in Azure Synapse Analytics.

Plannen om de ondersteuning voor snelle vooruit- en statische cursors af te dwingen

In SQL Server 2019 (15.x) en latere versies en Azure SQL Database (alle implementatiemodellen) ondersteunt Query Store de mogelijkheid om queryuitvoeringsplannen af te dwingen voor snelle en statische Transact-SQL en API-cursors. Het afdwingen wordt ondersteund door middel van sp_query_store_force_plan of via rapporten van SQL Server Management Studio Query Store.

De dwingende toepassing van een plan voor een query verwijderen

Als u opnieuw wilt vertrouwen op de optimalisatiefunctie voor SQL Server-query's om het optimale queryplan te berekenen, gebruikt u sp_query_store_unforce_plan om het plan dat is geselecteerd voor de query ongedaan te maken.

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

Verwante inhoud

• Prestaties bewaken met behulp van de Query Store-
• Beste Praktijk met de Query Store
• Het gebruik van de Query Store met In-Memory OLTP
• Query Store gebruiksscenario's
• Hoe Query Store gegevens verzamelt
• opgeslagen procedures van Query Store (Transact-SQL)
• Query Store-catalogusweergaven (Transact-SQL)
• Open Activity Monitor (SQL Server Management Studio)
• Live Query-statistieken
• activiteitsmonitor
• sys.database_query_store_options (Transact-SQL)
• Prestaties bewaken en afstemmen op
• hulpprogramma's voor prestatiebewaking en optimalisatie
• Query Store-hints
• database afstemmen met behulp van werkbelasting uit Query Store met de Database Engine Tuning Advisor-