Bits of .NET

📊 Key Takeaways: Query Store captures query performance history, helps identify regressions, and provides insights for optimization.

Query performance degrades over time. Query Store helps you detect and fix it.

🚀 Enable Query Store

-- ✅ Enable Query Store
ALTER DATABASE MyDatabase
SET QUERY_STORE = ON;

-- ✅ Configure Query Store
ALTER DATABASE MyDatabase
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);

🔍 Query Store Queries

-- ✅ Find regressed queries (slower than before)
SELECT 
    q.query_id,
    q.query_sql_text,
    p.plan_id,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.avg_duration > 100000 -- 100ms
ORDER BY rs.avg_duration DESC;

-- ✅ Find top resource consumers
SELECT TOP 10
    q.query_id,
    qt.query_sql_text,
    SUM(rs.avg_duration) AS total_duration,
    SUM(rs.avg_cpu_time) AS total_cpu,
    SUM(rs.avg_logical_io_reads) AS total_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_duration DESC;