Query Store: diagnosticar e corrigir regressões de performance no SQL Server

O Query Store, disponível desde SQL Server 2016, é uma caixa negra do motor de execução que regista automaticamente queries, planos de execução e estatísticas de runtime. É a ferramenta mais valiosa para diagnosticar regressões de performance.

Activar 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,           -- só queries relevantes
    SIZE_BASED_CLEANUP_MODE = AUTO,
    DATA_FLUSH_INTERVAL_SECONDS = 900    -- flush cada 15 min
);

Encontrar queries com regressão de plano

-- Queries com múltiplos planos onde o recente é mais lento
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;

Forçar um plano estável

-- Forçar o plano anterior (mais rápido) para uma query específica
EXEC sp_query_store_force_plan
    @query_id = 42,
    @plan_id  = 7;

-- Verificar planos forçados
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 detecta regressões e força o último bom plano automaticamente

Conclusão

O Query Store deve estar activo em todas as bases de dados de produção. Depois de uma actualização de SQL Server, estatísticas ou mudança de índices, é a primeira ferramenta a consultar para identificar regressões antes que os utilizadores as reportem.