Query Store i praksis: Finn og fiks regresjoner

Query Store er SQL Servers innebygde ferdskriver for spørringer. Den lagrer spørringstekst, kjøreplaner og statistikk over tid (CPU, varighet, lesinger og ventinger) i selve databasen. Dermed kan du svare på spørsmål som ellers er nesten umulige: Hvilken spørring ble treg i går kveld? Hvilken plan brukte den før? Hva endret seg? Query Store kom i SQL Server 2016 og er slått på som standard for nye databaser fra SQL Server 2022. Denne guiden viser hvordan du setter den opp, bruker rapportene og fikser en regresjon ved å tvinge en plan.

Kort fortalt
  • Slå Query Store på for alle brukerdatabaser, med QUERY_CAPTURE_MODE = AUTO og nok lagringsplass.
  • Rapporten Regressed Queries finner spørringer som har blitt tregere, og viser hvilke planer de har brukt.
  • Er en gammel plan bedre, kan du tvinge den med ett klikk eller sp_query_store_force_plan.
  • Fra SQL Server 2022 kan du legge Query Store hints på en spørring uten å endre koden i applikasjonen.

Slå på Query Store

Sjekk først om den allerede er på:

SELECT actual_state_desc, desired_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb,
       query_capture_mode_desc
FROM sys.database_query_store_options;

Står den som OFF, eller er databasen flyttet fra en eldre versjon, slår du den på slik:

ALTER DATABASE [MinDatabase] SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,           -- ignorerer sjeldne og billige ad hoc-spørringer
    MAX_STORAGE_SIZE_MB = 2048,
    INTERVAL_LENGTH_MINUTES = 60,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    SIZE_BASED_CLEANUP_MODE = AUTO
);
InnstillingAnbefalingHvorfor
QUERY_CAPTURE_MODEAUTOALL fyller Query Store med engangsspørringer fra applikasjoner som ikke bruker parametere.
MAX_STORAGE_SIZE_MB1–10 GB, etter hvor travel databasen erBlir den full, går Query Store over til READ_ONLY og slutter å samle data.
INTERVAL_LENGTH_MINUTES15–60Kortere intervaller gir finere oppløsning, men bruker mer plass.
STALE_QUERY_THRESHOLD_DAYS30Nok historikk til å sammenligne med forrige måned.

Overvåk actual_state_desc. Står den som READ_ONLY mens desired_state_desc er READ_WRITE, er plassen brukt opp. Øk MAX_STORAGE_SIZE_MB og sett tilstanden tilbake til READ_WRITE. Kostnaden ved Query Store er normalt noen få prosent CPU og er verdt det.

De innebygde rapportene

I SSMS finner du rapportene under databasen, i mappen Query Store:

RapportBrukes til
Regressed QueriesSpørringer som har blitt tregere i en periode sammenlignet med en tidligere periode.
Top Resource Consuming QueriesDe tyngste spørringene etter CPU, varighet, lesinger eller antall kjøringer.
Queries With High VariationSpørringer der kjøretiden varierer mye – typisk tegn på parameter sniffing.
Query Wait StatisticsHvilke ventetyper spørringene bruker tid på (SQL Server 2017 og nyere).
Queries With Forced PlansOversikt over planer du har tvunget, og om tvingingen har feilet.
Overall Resource ConsumptionTotalbelastning over tid. Nyttig for å finne når noe skjedde.

Grafen øverst til høyre i rapportene viser hver plan som en egen farge. Hopper en spørring fra rask til treg på samme tidspunkt som en ny plan dukker opp, har du funnet årsaken.

Finne de tyngste spørringene med T-SQL

-- Topp 10 spørringer etter total CPU siste døgn
SELECT TOP (10)
       q.query_id,
       SUM(rs.count_executions)                              AS Kjøringer,
       SUM(rs.avg_cpu_time * rs.count_executions) / 1000     AS TotalCpuMs,
       SUM(rs.avg_duration * rs.count_executions) / 1000     AS TotalVarighetMs,
       COUNT(DISTINCT p.plan_id)                             AS AntallPlaner,
       MAX(SUBSTRING(qt.query_sql_text, 1, 200))             AS Spørring
FROM sys.query_store_query            AS q
JOIN sys.query_store_query_text       AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan             AS p  ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats    AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
     ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(DAY, -1, SYSUTCDATETIME())
GROUP BY q.query_id
ORDER BY TotalCpuMs DESC;

Merk at tidene i Query Store er i mikrosekunder, og at start_time er lagret med tidssone (UTC).

Fikse en regresjon: tvinge en plan

Den vanligste situasjonen: en spørring har gått fint i månedsvis, og plutselig tar den 30 sekunder. Statistikken er oppdatert, eller spørringen ble kompilert på nytt med en uheldig parameterverdi, og optimalisatoren valgte en ny og dårligere plan. Med Query Store kan du be SQL Server om å bruke den gamle planen:

-- Finn query_id og plan_id i rapporten, eller med spørringen over
EXEC sys.sp_query_store_force_plan @query_id = 4711, @plan_id = 812;

-- Angre senere
EXEC sys.sp_query_store_unforce_plan @query_id = 4711, @plan_id = 812;

I rapportene gjør du det samme ved å markere planen og klikke Force Plan. Noen råd:

  • Tving en plan som et plaster, og finn den egentlige årsaken etterpå: manglende indeks, utdatert statistikk eller en spørring som bør skrives om.
  • Følg med i Queries With Forced Plans. Fjernes en indeks som planen bruker, feiler tvingingen, og force_failure_count øker.
  • En tvunget plan er en plan for alle parameterverdier. Er datafordelingen svært skjev, kan den være riktig for de fleste og feil for noen.

Automatisk plankorrigering

SQL Server kan gjøre dette selv. Med automatic tuning oppdager den at en ny plan er tregere enn den forrige, tvinger den gamle og sjekker etterpå om det hjalp:

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Funksjonen krever Enterprise Edition på egen server, og er slått på som standard i Azure SQL Database. Se guiden om utgaver og lisensiering.

Query Store hints

Fra SQL Server 2022 kan du legge et hint på en spørring uten å endre koden. Det er gull verdt når spørringen kommer fra et fagsystem eller en ORM du ikke kontrollerer:

-- Kompiler spørringen på nytt hver gang (løser ofte parameter sniffing)
EXEC sys.sp_query_store_set_hints
     @query_id = 4711,
     @query_hints = N'OPTION (RECOMPILE)';

-- Begrens parallellitet for én spørring
EXEC sys.sp_query_store_set_hints
     @query_id = 4712,
     @query_hints = N'OPTION (MAXDOP 1)';

-- Se og fjern hints
SELECT query_id, query_hint_text FROM sys.query_store_query_hints;
EXEC sys.sp_query_store_clear_hints @query_id = 4711;

Hintet følger spørringen så lenge den har samme tekst. Endres spørringen i applikasjonen, får den en ny query_id, og hintet gjelder ikke lenger.

Vanlige spørsmål

Hvorfor finner jeg ikke spørringen min?

Med QUERY_CAPTURE_MODE = AUTO lagres ikke spørringer som kjøres sjelden og er billige. Spørringer med ulik tekst (for eksempel konstanter i stedet for parametere) blir dessuten egne spørringer. Søk på en del av teksten i sys.query_store_query_text med LIKE.

Fungerer Query Store i Express?

Ja. Query Store finnes i alle utgaver. Det er bare automatisk plankorrigering som krever Enterprise.

Følger Query Store med når databasen flyttes?

Ja. Dataene ligger i selve databasen og følger med i backup og restore. Det gjør det enkelt å analysere et problem fra produksjon på en testserver. Se guiden om å flytte en database.

Erstatter Query Store sys.dm_exec_query_stats?

I praksis ja. DMV-ene viser bare det som ligger i plan-cachen akkurat nå, og alt forsvinner ved omstart eller når minnet blir knapt. Query Store har historikk over uker.

Les også