Narcis Miclaus

7 min di lettura

SQL Server, dalle fondamenta Lezione 28 / 40

Plan cache e Query Store: la tua macchina del tempo

La differenza tra plan cache e Query Store. Come trovare la query che è regredita martedì scorso. Forzare i piani senza modifiche di codice.

Canale ops di Runehold, martedì 09:14: “Ehi, l’endpoint di order-search è diventato lento durante la notte. Qualcuno ha cambiato qualcosa?” Metà del team nega di aver fatto deploy. L’altra metà dice “ho cambiato solo il CSS.” Il DBA apre Query Store.

Query Store è un registro storico integrato di ogni query che il tuo server ha eseguito e di ogni piano che l’ottimizzatore ha usato. È una macchina del tempo. Prima che esistesse (pre-SQL Server 2016), trovare “il piano che funzionava la settimana scorsa” era lavoro da detective. Con Query Store, sono tre clic.

Questa lezione copre il plan cache (la memoria a breve termine del motore) e Query Store (il registro a lungo termine), come si relazionano, e come usarli davvero per risolvere problemi di produzione.

Il plan cache

Il plan cache risiede in RAM. Ogni piano compilato vive lì, indicizzato dall’hash del testo della query. Quando esegui una query, SQL Server controlla prima la cache; se il piano esiste, viene riusato; altrimenti ne viene compilato uno nuovo e messo in cache.

Il contenuto della cache si svuota quando:

  • SQL Server si riavvia.
  • La pressione sulla memoria espelle i piani freddi.
  • Qualcuno esegue DBCC FREEPROCCACHE.
  • Certi cambi di schema invalidano i piani.
-- Top 20 query per CPU totale dal 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;

Clicca su query_plan nei risultati → il piano si apre in SSMS. Utile, ma questi dati spariscono al riavvio. È la limitazione che Query Store risolve.

Query Store, abilitato

Query Store è una feature a livello di database. Abilita per database:

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

Cosa fa ogni opzione:

  • OPERATION_MODE = READ_WRITE — funzionamento normale. Dati raccolti e piani forzati usati.
  • STALE_QUERY_THRESHOLD_DAYS = 30 — tieni 30 giorni di storico delle query.
  • DATA_FLUSH_INTERVAL_SECONDS = 900 — fai il flush delle stats in memoria su disco ogni 15 minuti. Più piccolo = meno perdita di dati su crash, più overhead di scrittura.
  • MAX_STORAGE_SIZE_MB = 1000 — quota da 1 GB. Ridimensiona in base al volume.
  • INTERVAL_LENGTH_MINUTES = 60 — raggruppa le statistiche in slot orari per l’analisi dei trend.
  • QUERY_CAPTURE_MODE: AUTO è intelligente (cattura le query significative, salta quelle banali). ALL cattura tutto; NONE disabilita la cattura ma mantiene i piani forzati.

AUTO è il default giusto per la maggior parte dei workload.

Una volta abilitato, Query Store sta girando. Lascialo raccogliere dati per una settimana o due prima di affidarti ai trend.

L’interfaccia di Query Store in SSMS

Espandi il tuo database in SSMS → cartella Query Store. Sette report integrati:

  • Regressed Queries — query la cui performance è peggiorata.
  • Overall Resource Consumption — quali metriche stanno crescendo.
  • Top Resource Consuming Queries — i soliti sospetti.
  • Queries With Forced Plans — cosa hai fissato.
  • Queries With High Variation — stessa query, runtime molto diversi (fumo da parameter sniffing).
  • Query Wait Statistics — breakdown per tipo di wait.
  • Tracked Queries — segui una query specifica nel tempo.

Il primo report — Regressed Queries — è quello che apri martedì mattina quando qualcosa si è rotto. Scegli una metrica (CPU, Duration, Logical Reads), scegli una finestra temporale, e SQL Server ti mostra le query le cui medie sono peggiorate notevolmente. Cliccane una e vedi ogni piano che la query ha usato nel tempo. Identifica il piano da prima della regressione, confrontalo con quello attuale, e decidi se forzare il vecchio piano.

Forzare un piano

Due clic nell’UI: seleziona il piano buono nella vista Tracked Query, clicca “Force Plan.”

Programmaticamente:

-- Trova la query che ti interessa
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%';

-- Guarda i piani per quella 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;

-- Forza il piano buono
EXEC sp_query_store_force_plan @query_id = 19, @plan_id = 42;

-- Più tardi, se cambi idea
EXEC sp_query_store_unforce_plan @query_id = 19, @plan_id = 42;

Il piano forzato viene applicato automaticamente a ogni esecuzione successiva. Nessun cambio di codice. Nessun hint. Reversibile.

Quando il forcing fallisce: se lo schema cambia in un modo che invalida il piano forzato (elimini un indice che il piano usa, per esempio), Query Store lo sforza automaticamente e lo segnala. Controlla periodicamente:

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 integrata per la caccia alle regressioni

Query utile: “query che sono diventate più lente nelle ultime 24 ore rispetto alla settimana precedente.”

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 la cui media sulle ultime 24 ore è almeno 2× la loro baseline” — alert classico di regressione. Collega questo a uno strumento di monitoring e vedrai le regressioni prima del support.

Filtro di cattura

Su server molto carichi, Query Store può crescere. Usa QUERY_CAPTURE_POLICY (2019+) per saltare le query a bassa frequenza:

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

Richiede che una query raggiunga almeno una delle soglie prima di essere catturata. Riduce drasticamente lo storage per sistemi ad alto volume senza perdere le query che contano davvero.

Azure SQL Database

Su Azure SQL Database, Query Store è attivo di default e non può essere disattivato. Ottieni anche la correzione automatica dei piani — Azure può forzare automaticamente il piano precedente quando rileva una regressione. Opt-in:

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Sorprendentemente utile. Mi ha salvato un weekend di on-call nel 2023 quando il query planner ha riscritto male un piano dopo un aggiornamento di stats e il motore Azure si è auto-corretto prima che l’alert scattasse.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- 1. Abilita Query Store se non è già attivo
ALTER DATABASE Runehold SET QUERY_STORE = ON;

-- 2. Verifica che stia girando
SELECT actual_state_desc, readonly_reason, current_storage_size_mb
FROM sys.database_query_store_options;

-- 3. Esegui qualche query per avere dati da vedere
EXEC dbo.GetOrdersForCustomer @CustomerId = 1;
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;
EXEC dbo.GetOrdersForCustomer @CustomerId = 42;

-- 4. Guarda le top query costose
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. Forza un piano (riempi con i veri query_id e plan_id dall'UI o dalle query sopra)
-- EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1;

-- 6. Ottieni le statistiche dettagliate per una 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;

Esplora l’UI di Query Store in SSMS. Tasto destro su un database → Query Store → Top Resource Consuming Queries. I grafici raccontano una storia. Dopo averlo usato due volte, non farai più debug di produzione senza.

Modulo 5 fatto. Piani di esecuzione, statistiche, parameter sniffing, Query Store — il toolkit di diagnostica delle performance. Il resto del corso si sposta verso le operazioni DBA: lezione 30 su filegroups e log file, poi lezioni 31-36 su backup, SQL Agent, sicurezza, tempdb, Ola e DMV.

Cerca