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.
- Slå Query Store på for alle brukerdatabaser, med
QUERY_CAPTURE_MODE = AUTOog 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
);
| Innstilling | Anbefaling | Hvorfor |
|---|---|---|
QUERY_CAPTURE_MODE | AUTO | ALL fyller Query Store med engangsspørringer fra applikasjoner som ikke bruker parametere. |
MAX_STORAGE_SIZE_MB | 1–10 GB, etter hvor travel databasen er | Blir den full, går Query Store over til READ_ONLY og slutter å samle data. |
INTERVAL_LENGTH_MINUTES | 15–60 | Kortere intervaller gir finere oppløsning, men bruker mer plass. |
STALE_QUERY_THRESHOLD_DAYS | 30 | Nok 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:
| Rapport | Brukes til |
|---|---|
| Regressed Queries | Spørringer som har blitt tregere i en periode sammenlignet med en tidligere periode. |
| Top Resource Consuming Queries | De tyngste spørringene etter CPU, varighet, lesinger eller antall kjøringer. |
| Queries With High Variation | Spørringer der kjøretiden varierer mye – typisk tegn på parameter sniffing. |
| Query Wait Statistics | Hvilke ventetyper spørringene bruker tid på (SQL Server 2017 og nyere). |
| Queries With Forced Plans | Oversikt over planer du har tvunget, og om tvingingen har feilet. |
| Overall Resource Consumption | Totalbelastning 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.