Query Store: diagnose and fix performance regressions in SQL Server

Query Store, available since SQL Server 2016, is a black box of the execution engine that automatically records queries, execution plans and runtime statistics. It is the most valuable tool for diagnosing performance regressions.

Enable Query Store

ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;
ALTER DATABASE AdventureWorks SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,           -- only relevant queries
    SIZE_BASED_CLEANUP_MODE = AUTO,
    DATA_FLUSH_INTERVAL_SECONDS = 900    -- flush every 15 min
);

Find queries with plan regression

-- Queries with multiple plans where the recent one is slower
SELECT TOP 10
    qsq.query_id,
    qsqt.query_sql_text,
    qsp.plan_id,
    qsrs.avg_duration / 1000.0 AS avg_ms,
    qsrs.count_executions,
    TRY_CAST(qsp.query_plan AS XML) AS execution_plan
FROM sys.query_store_query         qsq
JOIN sys.query_store_query_text    qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan          qsp  ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id  = qsrs.plan_id
ORDER BY qsrs.avg_duration DESC;

Force a stable plan

-- Force the previous (faster) plan for a specific query
EXEC sp_query_store_force_plan
    @query_id = 42,
    @plan_id  = 7;

-- Check forced plans
SELECT query_id, plan_id, is_forced_plan
FROM sys.query_store_plan
WHERE is_forced_plan = 1;

Automatic Plan Correction (SQL Server 2017+)

ALTER DATABASE AdventureWorks
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
-- SQL Server detects regressions and forces the last good plan automatically

Conclusion

Query Store should be enabled on all production databases. After a SQL Server upgrade, statistics change or index change, it is the first tool to check to identify regressions before users report them.