MSSQL query store

Attivare e Disattivare

attivare query store

ALTER DATABASE [NomeTuoDatabase] 
SET QUERY_STORE = ON (
   OPERATION_MODE = READ_WRITE,                -- Attiva la raccolta dati
   CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), -- Conserva i dati per 30 giorni (regolabile)
   INTERVAL_LENGTH_MINUTES = 60,               -- Intervallo di aggregazione (es. 1 ora)
   MAX_STORAGE_SIZE_MB = 1000,                 -- Spazio massimo occupato dal Query Store (es. 1 GB)
   QUERY_CAPTURE_MODE = AUTO                   -- Modalità di cattura (cattura le query rilevanti evitando il rumore)
);

disattiva query store

ALTER DATABASE [NomeTuoDatabase]
SET QUERY_STORE = ON (
   OPERATION_MODE = READ_ONLY,                         -- Impedisce qualsiasi scrittura/cancellazione
   QUERY_CAPTURE_MODE = NONE,                          -- Disattiva la cattura di nuove query
   CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 0), -- 0 = Disabilita la pulizia automatica per età
   SIZE_BASED_CLEANUP_MODE = OFF                       -- Disabilita la pulizia al raggiungimento del limite di spazio
);

o più drastico:

ALTER DATABASE [NomeTuoDatabase] SET QUERY_STORE = OFF;

interrogare query store

Verifica se attivo.

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

Analisi

analizzare query

SELECT
    qt.query_sql_text,
    rs.avg_duration,
    rs.count_executions,
    rs.avg_cpu_time,
    rsi.start_time,
    rsi.end_time
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_runtime_stats_interval rsi
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
JOIN sys.query_store_plan p
    ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q
    ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
WHERE rsi.start_time >= '2026-06-11 14:00'
  AND rsi.end_time   <= '2026-06-11 16:00';

query onerose per risorse

SELECT TOP 50
    q.query_id,
    p.plan_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_executions,
    CAST(AVG(rs.avg_logical_io_reads) AS BIGINT) AS avg_logical_reads,
    SUM(CAST(rs.avg_logical_io_reads AS BIGINT) * rs.count_executions) AS total_logical_reads,
    CAST(AVG(rs.avg_duration / 1000.0) AS DECIMAL(38, 2)) AS avg_duration_ms
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_query_text AS qt 
    ON q.query_text_id = qt.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
GROUP BY 
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY 
    avg_logical_reads DESC;

query con full scan onerose

SET NOCOUNT ON;

-- Tabella temporanea per raccogliere i risultati
IF OBJECT_ID('tempdb..#ScanResults') IS NOT NULL DROP TABLE #ScanResults;

CREATE TABLE #ScanResults (
    query_id BIGINT,
    plan_id BIGINT,
    query_sql_text NVARCHAR(MAX),
    scan_type VARCHAR(50),
    scan_cost_percent DECIMAL(5,2),
    avg_duration_ms DECIMAL(38,2),
    avg_logical_io_reads BIGINT,
    total_executions BIGINT
);

-- Step 1: Estrazione rapida dei soli 50 piani a più alto I/O
IF OBJECT_ID('tempdb..#TopPlans') IS NOT NULL DROP TABLE #TopPlans;

SELECT TOP 50
    p.plan_id,
    p.query_id,
    qt.query_sql_text,
    p.query_plan,
    SUM(rs.count_executions) AS total_executions,
    CAST(AVG(rs.avg_duration / 1000.0) AS DECIMAL(38,2)) AS avg_duration_ms,
    CAST(AVG(rs.avg_logical_io_reads) AS BIGINT) AS avg_logical_io_reads
INTO #TopPlans
FROM sys.query_store_plan p
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 rs ON p.plan_id = rs.plan_id
WHERE p.query_plan LIKE '%PhysicalOp="Table Scan"%'
   OR p.query_plan LIKE '%PhysicalOp="Clustered Index Scan"%'
   OR p.query_plan LIKE '%PhysicalOp="Index Scan"%'
GROUP BY p.plan_id, p.query_id, qt.query_sql_text, p.query_plan
ORDER BY avg_logical_io_reads DESC;

-- Step 2: Ciclo protetto sui 50 piani per gestire gli errori XML individualmente
DECLARE @plan_id BIGINT, @query_id BIGINT, @sql_text NVARCHAR(MAX), @plan_xml_str NVARCHAR(MAX);
DECLARE @avg_dur DECIMAL(38,2), @avg_io BIGINT, @tot_exec BIGINT;

DECLARE plan_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT plan_id, query_id, query_sql_text, query_plan, avg_duration_ms, avg_logical_io_reads, total_executions
FROM #TopPlans;

OPEN plan_cursor;
FETCH NEXT FROM plan_cursor INTO @plan_id, @query_id, @sql_text, @plan_xml_str, @avg_dur, @avg_io, @tot_exec;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        -- Esegue il parsing XML preciso per il singolo piano
        WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
        INSERT INTO #ScanResults
        SELECT 
            @query_id,
            @plan_id,
            @sql_text,
            scan_nodes.scan_op.value('@PhysicalOp', 'VARCHAR(50)') AS scan_type,
            CAST((scan_nodes.scan_op.value('@EstimatedTotalSubtreeCost', 'FLOAT') / NULLIF(stmt.stmt_xml.value('@StatementSubTreeCost', 'FLOAT'), 0)) * 100 AS DECIMAL(5,2)) AS scan_cost_percent,
            @avg_dur,
            @avg_io,
            @tot_exec
        FROM (SELECT CAST(@plan_xml_str AS XML) AS xPlan) AS xml_plan
        CROSS APPLY xml_plan.xPlan.nodes('//StmtSimple') AS stmt(stmt_xml)
        CROSS APPLY stmt.stmt_xml.nodes('.//RelOp[@PhysicalOp="Table Scan" or @PhysicalOp="Clustered Index Scan" or @PhysicalOp="Index Scan"]') AS scan_nodes(scan_op)
        WHERE stmt.stmt_xml.value('@StatementSubTreeCost', 'FLOAT') > 0
          AND (scan_nodes.scan_op.value('@EstimatedTotalSubtreeCost', 'FLOAT') / stmt.stmt_xml.value('@StatementSubTreeCost', 'FLOAT')) >= 0.50;
    END TRY
    BEGIN CATCH
        -- In caso di errore XML (>128 livelli di annidamento), ignora solo questo piano e prosegue
    END CATCH;

    FETCH NEXT FROM plan_cursor INTO @plan_id, @query_id, @sql_text, @plan_xml_str, @avg_dur, @avg_io, @tot_exec;
END

CLOSE plan_cursor;
DEALLOCATE plan_cursor;

-- Step 3: Estrazione del risultato finale
SELECT 
    query_id,
    plan_id,
    query_sql_text,
    scan_type,
    scan_cost_percent,
    avg_duration_ms,
    avg_logical_io_reads,
    total_executions
FROM #ScanResults
ORDER BY avg_logical_io_reads DESC;