Narcis Miclaus

7 min de citit

SQL Server, de la zero Lecția 28 / 40

Plan cache și Query Store: mașina ta a timpului

Diferența dintre plan cache și Query Store. Cum găsești query-ul care a regresat marțea trecută. Forțarea planurilor fără modificări de cod.

Pe canalul de ops al Runehold, marți la 09:14: „Hei, endpoint-ul de order-search a devenit lent peste noapte. A schimbat cineva ceva?” Jumătate din echipă neagă orice deployment. Cealaltă jumătate spune „eu doar am modificat CSS-ul.” DBA-ul deschide Query Store.

Query Store e o înregistrare istorică integrată a fiecărui query pe care l-a rulat serverul tău și a fiecărui plan pe care l-a folosit optimizatorul. E o mașină a timpului. Înainte să existe (înainte de SQL Server 2016), găsirea „planului care mergea săptămâna trecută” era muncă de detectiv. Cu Query Store, sunt trei click-uri.

Lecția asta acoperă plan cache-ul (memoria pe termen scurt a engine-ului) și Query Store (înregistrarea pe termen lung), cum se leagă și cum le folosești efectiv ca să rezolvi probleme de producție.

Plan cache-ul

Plan cache-ul stă în RAM. Fiecare plan compilat trăiește acolo, indexat după hash-ul textului query-ului. Când execuți un query, SQL Server verifică întâi cache-ul; dacă planul există, e reutilizat; altfel, se compilează unul proaspăt și se cache-uiește.

Conținutul cache-ului se golește când:

  • SQL Server se restartează.
  • Presiunea de memorie evict-ează planurile reci.
  • Cineva rulează DBCC FREEPROCCACHE.
  • Anumite modificări de schemă invalidează planurile.
-- Top 20 query-uri după CPU total din plan cache
SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_logical_reads,
    SUBSTRING(st.text,
              qs.statement_start_offset / 2 + 1,
              (CASE qs.statement_end_offset
                   WHEN -1 THEN DATALENGTH(st.text)
                   ELSE qs.statement_end_offset
               END - qs.statement_start_offset) / 2 + 1) AS statement_text,
    qp.query_plan
FROM sys.dm_exec_query_stats              AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)     AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Click pe query_plan în rezultate → planul se deschide în SSMS. Util, dar datele astea dispar la restart. Asta e limitarea pe care o rezolvă Query Store.

Query Store, activat

Query Store e un feature la nivel de bază de date. Activează per bază de date:

ALTER DATABASE Runehold SET QUERY_STORE = ON (
    OPERATION_MODE            = READ_WRITE,
    CLEANUP_POLICY            = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB       = 1000,
    INTERVAL_LENGTH_MINUTES   = 60,
    QUERY_CAPTURE_MODE        = AUTO
);

Ce face fiecare opțiune:

  • OPERATION_MODE = READ_WRITE — operare normală. Datele sunt colectate și planurile forțate folosite.
  • STALE_QUERY_THRESHOLD_DAYS = 30 — ține 30 de zile de istoric de query-uri.
  • DATA_FLUSH_INTERVAL_SECONDS = 900 — flush din memorie pe disc la fiecare 15 minute. Mai mic = mai puține date pierdute la crash, mai mult write overhead.
  • MAX_STORAGE_SIZE_MB = 1000 — quota de 1 GB. Redimensionează în funcție de volum.
  • INTERVAL_LENGTH_MINUTES = 60 — împarte statisticile în slot-uri orare pentru analiză de tendințe.
  • QUERY_CAPTURE_MODE: AUTO e inteligent (capturează query-urile semnificative, sare peste cele triviale). ALL capturează tot; NONE dezactivează capturarea, dar păstrează planurile forțate.

AUTO e default-ul corect pentru majoritatea workload-urilor.

Odată activat, Query Store rulează. Lasă-l să colecteze date o săptămână sau două înainte să te bazezi pe tendințe.

UI-ul Query Store în SSMS

Expandează baza de date în SSMS → folder-ul Query Store. Șapte rapoarte integrate:

  • Regressed Queries — query-urile a căror performanță s-a înrăutățit.
  • Overall Resource Consumption — ce metrici cresc.
  • Top Resource Consuming Queries — suspecții obișnuiți.
  • Queries With Forced Plans — ce ai fixat.
  • Queries With High Variation — același query, runtime-uri foarte diferite (semn de parameter sniffing).
  • Query Wait Statistics — defalcare pe wait-type.
  • Tracked Queries — urmărește un query specific în timp.

Primul raport — Regressed Queries — e ce deschizi marți dimineața când s-a stricat ceva. Alegi o metrică (CPU, Duration, Logical Reads), alegi o fereastră de timp, iar SQL Server îți arată query-urile a căror medie s-a înrăutățit notabil. Dai click pe unul și vezi fiecare plan pe care l-a folosit acel query în timp. Identifici planul de dinainte de regresie, îl compari cu cel curent și decizi dacă forțezi planul vechi.

Forțarea unui plan

Două click-uri în UI: selectezi planul bun în view-ul Tracked Query, click „Force Plan”.

Programatic:

-- Găsește query-ul care te interesează
SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query      AS q ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%GetOrdersForCustomer%';

-- Uită-te la planurile pentru acel query
SELECT p.plan_id, p.query_id, p.is_forced_plan, p.last_execution_time,
       p.avg_duration, p.count_executions
FROM sys.query_store_plan AS p
WHERE p.query_id = 19
ORDER BY p.last_execution_time DESC;

-- Forțează planul bun
EXEC sp_query_store_force_plan @query_id = 19, @plan_id = 42;

-- Mai târziu, dacă te răzgândești
EXEC sp_query_store_unforce_plan @query_id = 19, @plan_id = 42;

Planul forțat se aplică automat la fiecare execuție ulterioară. Fără modificări de cod. Fără hint. Reversibil.

Când forțarea eșuează: dacă schema se schimbă astfel încât invalidează planul forțat (ștergi un index folosit de plan, de exemplu), Query Store îl unforce-uiește automat și îl marchează. Verifică periodic:

SELECT query_id, plan_id, force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan
WHERE is_forced_plan = 1 AND force_failure_count > 0;

Query integrat de vânătoare a regresiilor

Query util: „query-urile care au devenit mai lente în ultima zi față de săptămâna anterioară.”

WITH recent AS (
    SELECT p.query_id, p.plan_id,
           AVG(rs.avg_duration) AS recent_avg_us
    FROM sys.query_store_plan AS p
    JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
    JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE rsi.end_time >= DATEADD(HOUR, -24, GETDATE())
    GROUP BY p.query_id, p.plan_id
),
baseline AS (
    SELECT p.query_id, p.plan_id,
           AVG(rs.avg_duration) AS baseline_avg_us
    FROM sys.query_store_plan AS p
    JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
    JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE rsi.end_time BETWEEN DATEADD(DAY, -7, GETDATE()) AND DATEADD(DAY, -1, GETDATE())
    GROUP BY p.query_id, p.plan_id
)
SELECT r.query_id, r.plan_id,
       r.recent_avg_us   / 1000 AS recent_ms,
       b.baseline_avg_us / 1000 AS baseline_ms,
       r.recent_avg_us * 1.0 / NULLIF(b.baseline_avg_us, 0) AS ratio
FROM recent AS r
JOIN baseline AS b ON b.query_id = r.query_id AND b.plan_id = r.plan_id
WHERE r.recent_avg_us > b.baseline_avg_us * 2
ORDER BY ratio DESC;

„Query-uri a căror medie pe ultimele 24 de ore e cel puțin 2× față de baseline” — alertă clasică de regresie. Conectează asta la o unealtă de monitorizare și vei vedea regresiile înainte ca support-ul să le observe.

Filtru de captură

Pe servere ocupate, Query Store poate să crească. Folosește QUERY_CAPTURE_POLICY (2019+) ca să sară peste query-uri cu frecvență mică:

ALTER DATABASE Runehold SET QUERY_STORE (
    QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
    )
);

Cere ca un query să atingă cel puțin unul dintre praguri ca să fie capturat. Reduce dramatic stocarea pentru sisteme cu volum mare fără să pierzi query-urile care contează cu adevărat.

Azure SQL Database

Pe Azure SQL Database, Query Store e on by default și nu poate fi oprit. Primești și automatic plan correction — Azure poate forța automat planul anterior când detectează o regresie. Opt-in:

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Uimitor de util. Mi-a salvat un weekend de on-call în 2023 când query planner-ul a rescris prost un plan după un update de stats, iar engine-ul Azure s-a auto-reparat înainte ca alerta să se declanșeze.

Rulează asta pe propria mașină

USE Runehold;
GO

-- 1. Activează Query Store dacă nu e deja
ALTER DATABASE Runehold SET QUERY_STORE = ON;

-- 2. Verifică că rulează
SELECT actual_state_desc, readonly_reason, current_storage_size_mb
FROM sys.database_query_store_options;

-- 3. Rulează niște query-uri ca să existe date de văzut
EXEC dbo.GetOrdersForCustomer @CustomerId = 1;
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;
EXEC dbo.GetOrdersForCustomer @CustomerId = 42;

-- 4. Uită-te la cele mai scumpe query-uri din top
SELECT TOP (10)
    qt.query_sql_text,
    rs.avg_duration / 1000 AS avg_ms,
    rs.count_executions
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query       AS q  ON q.query_text_id = qt.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
ORDER BY rs.avg_duration DESC;

-- 5. Forțează un plan (completează cu query_id și plan_id reale din UI sau query-urile de mai sus)
-- EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1;

-- 6. Obține statistici detaliate pentru un query
SELECT
    rsi.start_time, rsi.end_time,
    rs.count_executions,
    rs.avg_duration / 1000 AS avg_ms,
    rs.avg_cpu_time / 1000 AS avg_cpu_ms,
    rs.avg_logical_io_reads
FROM sys.query_store_query AS q
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 rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE q.query_id = 1
ORDER BY rsi.start_time DESC;

Explorează UI-ul Query Store în SSMS. Click dreapta pe o bază de date → Query Store → Top Resource Consuming Queries. Graficele spun o poveste. După ce l-ai folosit de două ori, nu vei mai debugui producție fără el.

Modulul 5 e gata. Planuri de execuție, statistici, parameter sniffing, Query Store — toolkit-ul de diagnostic de performanță. Restul cursului trece spre operațiuni de DBA: lecția 30 despre filegroups și fișierul de log, apoi lecțiile 31-36 despre backup-uri, SQL Agent, securitate, tempdb, Ola și DMV-uri.

Caută