Query Store: diagnosticar y corregir regresiones de rendimiento en SQL Server

Query Store, disponible desde SQL Server 2016, es una caja negra del motor de ejecución que registra automáticamente consultas, planes de ejecución y estadísticas de runtime. Es la herramienta más valiosa para diagnosticar regresiones de rendimiento.

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,           -- solo consultas relevantes
    SIZE_BASED_CLEANUP_MODE = AUTO,
    DATA_FLUSH_INTERVAL_SECONDS = 900    -- flush cada 15 min
);

Encontrar consultas con regresión de plan

-- Consultas con múltiples planes donde el reciente es más 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;

Forzar un plan estable

-- Forzar el plan anterior (más rápido) para una consulta específica
EXEC sp_query_store_force_plan
    @query_id = 42,
    @plan_id  = 7;

-- Verificar planes forzados
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 regresiones y fuerza el último buen plan automáticamente

Conclusión

Query Store debe estar activo en todas las bases de datos de producción. Tras una actualización de SQL Server, cambio de estadísticas o de índices, es la primera herramienta a consultar para identificar regresiones antes de que los usuarios las reporten.