SQL Server
Cómo encontrar regresiones de rendimiento en SQL Server con Query Store
Un flujo práctico con Query Store para regresiones de SQL Server: compara intervalos, encuentra consultas que se volvieron lentas, revisa cambios de plan, fuerza planes con seguridad y deshazlos.
En este artículo
«La semana pasada iba rápido» es una de las quejas más habituales sobre SQL Server, y una de las más difíciles de responder solo con las DMV en vivo. Cuando alguien se pone a mirar, puede que la consulta lenta ya haya terminado, que el plan haya salido de la caché y que el servidor se haya reiniciado. Lo que necesitas es historial: qué consultas se ejecutaron, con qué planes y cómo rindieron antes y después del cambio.
Query Store guarda ese historial dentro de la base de datos. Este artículo es un flujo práctico para usarlo al investigar una regresión de rendimiento, desde el primer síntoma hasta una corrección validada:
síntoma → evidencias → regresión → plan candidato → validación → acción
Los ejemplos usan las vistas de catálogo y los procedimientos almacenados documentados de Query Store, y funcionan en SQL Server 2017 y versiones posteriores, Azure SQL Database y Azure SQL Managed Instance, salvo que se indique lo contrario.
Qué es una regresión de rendimiento
Una regresión es una consulta, o una carga de trabajo, que rinde peor que antes para un trabajo comparable. Esa última parte importa. Un informe que tarda más porque ahora lee diez veces más datos no ha sufrido una regresión; lo que cambió fue su carga de trabajo. Una regresión es un cambio de eficiencia, no solo de duración.
Causas habituales:
- Cambios de plan. El optimizador elige un plan diferente tras una actualización de estadísticas, una recompilación, un cambio de índices, una compilación sensible a parámetros, un cambio de nivel de compatibilidad o una actualización de versión.
- Crecimiento de los datos o cambios en su distribución. El mismo plan ahora es malo para los datos con los que se encuentra.
- Cambios de código. Un despliegue cambió el texto de la consulta, que Query Store trata como una consulta nueva.
- Contención de recursos. Los bloqueos, la presión de memoria o la latencia de E/S hacen más lento un plan que no ha cambiado.
Query Store es más fuerte en las dos primeras, y ayuda a separarlas de las dos últimas.
Por qué Query Store es la herramienta adecuada
Query Store captura, por base de datos:
- Las consultas y su texto (
sys.query_store_query,sys.query_store_query_text). - Cada plan que ha usado cada consulta (
sys.query_store_plan), incluido el showplan XML, los tiempos de compilación, y la versión del motor y el nivel de compatibilidad que lo generaron. - Estadísticas de ejecución por plan y por intervalo de tiempo (
sys.query_store_runtime_statsconsys.query_store_runtime_stats): ejecuciones, duración, CPU, lecturas lógicas, escrituras y más._interval - Estadísticas de esperas por plan e intervalo (
sys.query_store_wait_stats), cuando la captura de esperas está habilitada.
Las duraciones y los tiempos de CPU se almacenan en microsegundos, y las lecturas lógicas en páginas de 8 KB. Las estadísticas se agregan por intervalo (60 minutos de forma predeterminada), así que comparas intervalos, no ejecuciones individuales.
La vista del estado actual de las solicitudes y sesiones activas te dice qué está pasando ahora. Query Store te dice qué cambió.
Paso 0: asegúrate de que Query Store está recopilando datos
Query Store está habilitado de forma predeterminada en las bases de datos nuevas de Azure SQL y, desde SQL Server 2022, en las bases de datos nuevas de SQL Server. Las bases de datos más antiguas, y las actualizadas desde versiones anteriores, pueden no tenerlo activado. Compruébalo antes de depender de él:
SELECT
actual_state_desc,
desired_state_desc,
readonly_reason,
current_storage_size_mb,
max_storage_size_mb,
interval_length_minutes,
stale_query_threshold_days,
query_capture_mode_desc,
size_based_cleanup_mode_desc
FROM sys.database_query_store_options;El resultado que buscas es READ_WRITE en ambas columnas de estado. Si actual_state_desc es READ_ONLY mientras desired_state_desc es READ_WRITE, Query Store dejó de recopilar datos, casi siempre porque alcanzó su tamaño máximo. En ese caso tienes historial hasta ese momento y nada después, lo que puede hacer invisible una regresión.
Una configuración razonable para producción:
ALTER DATABASE CURRENT
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 2048,
SIZE_BASED_CLEANUP_MODE = AUTO
);QUERY_CAPTURE_MODE = AUTO omite las consultas ad hoc insignificantes, lo que mantiene el almacén centrado en lo relevante. Dimensiona el almacenamiento según el historial que quieras conservar y mantén SIZE_BASED_CLEANUP_MODE = AUTO para que la limpieza se ejecute antes de que el almacén se llene. La retención se controla con el umbral de consultas obsoletas (stale query threshold); asegúrate de que cubre los intervalos de comparación que necesitas, como «el mismo día de la semana pasada».
Paso 1: define el síntoma y los intervalos
Antes de consultar nada, anota:
- Qué va lento: un informe, un endpoint de API, un proceso por lotes o «todo».
- Cuándo empezó: un despliegue, una reconstrucción de índices, un job de estadísticas, una actualización de versión, una carga de datos.
- Un intervalo de referencia: un periodo de carga comparable en el que todo era normal, idealmente el mismo día de la semana y a las mismas horas.
- Un intervalo del incidente: de la misma duración, posterior al inicio del problema.
Comparar un domingo tranquilo con un lunes con mucha actividad detecta cambios de carga, no regresiones. Lo que hace que la comparación tenga sentido son intervalos de igual duración con una actividad de negocio similar. Si el problema está ocurriendo ahora mismo, empieza con SQL Server va lento: un flujo práctico de diagnóstico y con la revisión de las solicitudes activas para descartar primero bloqueos y saturación.
Paso 2: encuentra las consultas que se volvieron más lentas
Esta consulta compara dos intervalos para cada consulta. Pondera los promedios de cada intervalo por su número de ejecuciones, de modo que un intervalo con mucha actividad cuente más que uno tranquilo, e informa por separado de las ejecuciones abortadas y fallidas. Los tiempos de espera agotados del cliente se registran como ejecuciones abortadas, así que una regresión que termina en timeouts puede parecer una disminución de las ejecuciones lentas en lugar de un aumento.
DECLARE @baseline_start datetimeoffset = '2026-09-28T08:00:00+00:00';
DECLARE @baseline_end datetimeoffset = '2026-09-28T12:00:00+00:00';
DECLARE @incident_start datetimeoffset = '2026-10-05T08:00:00+00:00';
DECLARE @incident_end datetimeoffset = '2026-10-05T12:00:00+00:00';
WITH runtime AS (
SELECT
p.query_id,
CASE
WHEN rsi.start_time >= @incident_start AND rsi.start_time < @incident_end
THEN 'incident'
ELSE 'baseline'
END AS period,
rs.execution_type,
rs.count_executions,
rs.avg_duration,
rs.avg_cpu_time,
rs.avg_logical_io_reads
FROM sys.query_store_runtime_stats AS rs
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p
ON p.plan_id = rs.plan_id
WHERE (rsi.start_time >= @baseline_start AND rsi.start_time < @baseline_end)
OR (rsi.start_time >= @incident_start AND rsi.start_time < @incident_end)
),
per_period AS (
SELECT
query_id,
period,
SUM(CASE WHEN execution_type = 0 THEN count_executions ELSE 0 END) AS regular_executions,
SUM(CASE WHEN execution_type <> 0 THEN count_executions ELSE 0 END) AS aborted_or_failed,
SUM(CASE WHEN execution_type = 0 THEN avg_duration * count_executions END)
/ NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) / 1000.0 AS avg_duration_ms,
SUM(CASE WHEN execution_type = 0 THEN avg_cpu_time * count_executions END)
/ NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) / 1000.0 AS avg_cpu_ms,
SUM(CASE WHEN execution_type = 0 THEN avg_logical_io_reads * count_executions END)
/ NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) AS avg_logical_reads
FROM runtime
GROUP BY query_id, period
)
SELECT TOP (25)
i.query_id,
b.regular_executions AS baseline_executions,
i.regular_executions AS incident_executions,
i.aborted_or_failed AS incident_aborted_or_failed,
CAST(b.avg_duration_ms AS decimal(18, 2)) AS baseline_avg_ms,
CAST(i.avg_duration_ms AS decimal(18, 2)) AS incident_avg_ms,
CAST(b.avg_cpu_ms AS decimal(18, 2)) AS baseline_cpu_ms,
CAST(i.avg_cpu_ms AS decimal(18, 2)) AS incident_cpu_ms,
CAST(b.avg_logical_reads AS decimal(18, 0)) AS baseline_reads,
CAST(i.avg_logical_reads AS decimal(18, 0)) AS incident_reads,
CAST((i.avg_duration_ms - b.avg_duration_ms) * i.regular_executions / 1000.0 AS decimal(18, 1))
AS additional_seconds,
LEFT(qt.query_sql_text, 200) AS query_text_start
FROM per_period AS i
INNER JOIN per_period AS b
ON b.query_id = i.query_id
AND b.period = 'baseline'
INNER JOIN sys.query_store_query AS q
ON q.query_id = i.query_id
INNER JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
WHERE i.period = 'incident'
AND i.avg_duration_ms > 1.5 * b.avg_duration_ms
ORDER BY additional_seconds DESC;Notas
- líneas 1–4Las dos ventanas que se comparan. Dales la misma duración y la misma hora del día.
- líneas 33–34Query Store guarda las duraciones en microsegundos: al dividir entre 1000.0 se obtienen milisegundos.
- líneas 53–54El tiempo adicional que costó la degradación durante la ventana del incidente: la lista se ordena por este valor.
- línea 65Conserva las consultas al menos un 50 % más lentas de media. Cambia 1.5 para ampliar o reducir la lista.
Lee el resultado en este orden:
additional_secondsordena las consultas según el tiempo extra que añadieron al intervalo del incidente. Una consulta que se volvió 3× más lenta pero se ejecuta dos veces al día importa menos que una que se volvió un 30 % más lenta y se ejecuta diez mil veces por hora.- Duración frente a CPU frente a lecturas. Si la CPU y las lecturas subieron junto con la duración, la consulta está haciendo más trabajo, lo que sugiere un cambio de plan o de datos. Si la duración subió pero la CPU y las lecturas no, la consulta está esperando, lo que apunta a bloqueos, E/S o memoria más que al plan.
- Ejecuciones. Un cambio grande en el número de ejecuciones es un cambio de carga de trabajo. Trátalo por separado de las regresiones por ejecución.
- Abortadas o fallidas. Un aumento aquí puede indicar timeouts. Comprueba si el cliente cortó las ejecuciones lentas.
Las consultas que solo aparecen en el intervalo del incidente no están en este resultado, porque no tienen referencia. Suelen ser código nuevo o un texto de consulta nuevo tras un despliegue; enuméralas por separado si sospechas del despliegue.
Paso 3: comprueba si el plan cambió
Para cada consulta candidata, revisa sus planes y su rendimiento a lo largo del tiempo:
DECLARE @query_id bigint = 4711;
SELECT
p.plan_id,
p.is_forced_plan,
p.compatibility_level,
p.engine_version,
p.last_compile_start_time,
rsi.start_time AS interval_start,
rs.execution_type_desc,
rs.count_executions,
CAST(rs.avg_duration / 1000.0 AS decimal(18, 2)) AS avg_duration_ms,
CAST(rs.avg_cpu_time / 1000.0 AS decimal(18, 2)) AS avg_cpu_ms,
CAST(rs.avg_logical_io_reads AS decimal(18, 0)) AS avg_logical_reads,
CAST(rs.max_duration / 1000.0 AS decimal(18, 2)) AS max_duration_ms
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE p.query_id = @query_id
AND rsi.start_time >= DATEADD(day, -14, SYSUTCDATETIME())
ORDER BY rsi.start_time, p.plan_id;Notas
- línea 1Sustituye 4711 por un query_id del resultado anterior.
Hay tres patrones habituales:
- Un plan antes y otro plan después, y el nuevo plan es sistemáticamente peor. Es una regresión de plan clásica, y la mejor candidata para el forzado de planes.
- El mismo plan antes y después, pero se volvió más lento. El plan no cambió; cambiaron los datos o el entorno. Revisa el crecimiento de las tablas, las estadísticas, los bloqueos y la presión sobre los recursos. El tamaño de las tablas y la lista de verificación de optimización del rendimiento son las siguientes paradas.
- Varios planes que se alternan, cada uno bueno para algunas ejecuciones y malo para otras. Eso suele indicar sensibilidad a parámetros: un plan compilado para un valor de parámetro se reutiliza con valores muy diferentes. Forzar un único plan puede ayudar a algunos llamadores y perjudicar a otros.
En SQL Server 2022 y versiones posteriores, la optimización de planes sensibles a parámetros (parameter sensitive plan optimization) puede almacenar varios planes variantes para una misma consulta parametrizada. sys.query_store_plan.plan_type_desc distingue los planes distribuidores (dispatcher) y los planes variantes, y sys.query_store_query_variant los relaciona. Tenlo en cuenta antes de concluir que una consulta tiene «demasiados planes».
Paso 4: compara los planes candidatos
Extrae los planes y compáralos en SQL Server Management Studio o en otro visor de planes:
SELECT
plan_id,
query_id,
last_compile_start_time,
TRY_CONVERT(xml, query_plan) AS query_plan_xml
FROM sys.query_store_plan
WHERE plan_id IN (82, 97);TRY_CONVERT devuelve NULL para los pocos planes con un anidamiento demasiado profundo para convertirse a xml; en ese caso, guarda el texto de query_plan con la extensión .sqlplan y ábrelo directamente.
Al comparar, busca el operador que cambió el perfil de coste: un scan donde antes había un seek, un nested loop sobre una entrada estimada grande, un orden de join diferente, la ausencia de paralelismo o una gran diferencia entre las filas estimadas y las reales si también tienes un plan real. Anota la hora de compilación y el nivel de compatibilidad de cada plan; un plan compilado justo después de una actualización de versión o de un cambio de nivel de compatibilidad es una pista sólida.
Los informes integrados Regressed Queries y Queries With Forced Plans de Management Studio muestran los mismos datos de forma visual. Son una buena manera de confirmar lo que te dijo el T-SQL.
Paso 5: decide si forzar un plan
Si un plan anterior era sistemáticamente mejor para la misma carga de trabajo, forzarlo es una mitigación rápida y reversible:
EXEC sys.sp_query_store_force_plan @query_id = 4711, @plan_id = 82;Forzar un plan indica al optimizador que use ese plan para la consulta. No cambia el texto de la consulta y entra en vigor en la siguiente compilación.
No fuerces un plan cuando:
- El plan no cambió. Forzar el mismo plan no arregla nada; la causa está en otra parte.
- El plan anterior solo era mejor para algunos valores de parámetros. Cambias un grupo de llamadores lentos por otro.
- El plan anterior depende de un índice u objeto que ha cambiado desde entonces. El forzado puede fallar, y el optimizador vuelve a la compilación normal.
- No has comparado cargas de trabajo equivalentes. Un plan que parecía mejor en un día tranquilo puede no serlo bajo carga.
- La corrección real es barata y está disponible, como corregir un problema de estadísticas o un índice que falta de forma evidente. El forzado no debería ocultar una causa que puedes eliminar.
Forzar un plan es una mitigación. Registra por qué forzaste el plan, quién lo hizo y cuándo se revisará.
Paso 6: valida el resultado
Después de forzar el plan, espera a que la consulta se recompile y se ejecute con una carga representativa, y luego repite la comparación del paso 2 con un nuevo intervalo «después». Lo que quieres ver es menos duración, CPU y lecturas por ejecución, con un número de ejecuciones comparable y sin aumento de las ejecuciones abortadas.
Confirma también que el forzado realmente funciona. No está garantizado que se use un plan forzado; si el optimizador no puede generarlo (por ejemplo, porque se eliminó un índice que utiliza), el forzado falla y la consulta se compila normalmente:
SELECT
p.plan_id,
p.query_id,
q.object_id AS containing_object_id,
p.plan_forcing_type_desc,
p.force_failure_count,
p.last_force_failure_reason_desc
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1;force_failure_count solo aumenta cuando la consulta se recompila, no en cada ejecución. Revisa esta lista con regularidad, no solo después de un incidente. Cambiar el nombre de la base de datos, eliminar un índice que usa el plan o hacer cambios de esquema en los objetos referenciados puede romper el forzado sin avisar.
Paso 7: deshaz o sustituye el plan forzado
Quita el forzado de un plan cuando deje de ayudar, cuando haya una corrección duradera aplicada o cuando la validación muestre que empeoró las cosas:
EXEC sys.sp_query_store_unforce_plan @query_id = 4711, @plan_id = 82;El forzado de planes es una solución de espera. La corrección duradera suele ser una de estas: un mejor mantenimiento de estadísticas, un índice que dé soporte al predicado, reescribir la consulta, separar perfiles de carga realmente diferentes o una sugerencia (hint) específica. En SQL Server 2022 y versiones posteriores y en Azure SQL, las sugerencias de Query Store (Query Store hints: sys.sp_query_store_set_hints y sys.sp_query_store_clear_hints) pueden aplicar una sugerencia de consulta sin cambiar el código de la aplicación, lo que a menudo es un mejor control a largo plazo que forzar un plan completo.
Corrección automática de planes
SQL Server 2017 y versiones posteriores pueden hacer automáticamente la parte de este flujo que corresponde a las regresiones de plan. Con el ajuste automático (automatic tuning) habilitado, el motor detecta las regresiones en la elección del plan, fuerza el último plan bueno conocido, verifica que ayuda y lo revierte si no es así:
ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);Incluso sin habilitarlo, puedes leer las recomendaciones que haría:
SELECT
name,
reason,
score,
JSON_VALUE(state, '$.currentValue') AS current_state,
JSON_VALUE(details, '$.implementationDetails.script') AS script
FROM sys.dm_db_tuning_recommendations;sys.dm_db_tuning_recommendations no se conserva tras un reinicio, y la corrección automática solo trata las regresiones en la elección del plan. No corrige el crecimiento de los datos, los bloqueos ni la presión sobre los recursos. Trátala como un primer respondedor útil y mantén el flujo manual para todo lo demás.
Consideraciones sobre almacenamiento y captura
- Dimensiónalo para el historial que necesitas. Un almacén que se llena pasa a solo lectura y deja de registrar justo cuando lo necesitas.
- Modo de captura.
AUTOes el valor predeterminado práctico.ALLcaptura todas las consultas y puede inflar el almacén con cargas ad hoc;CUSTOMte permite definir umbrales. - Las cargas no parametrizadas crean una consulta nueva por cada valor literal, lo que llena el almacén y dificulta la comparación. Parametriza en la aplicación, o considera la parametrización forzada cuando proceda.
- La duración del intervalo equilibra nivel de detalle y espacio. Los intervalos más cortos ayudan a precisar cuándo se produjo un cambio; 60 minutos es un valor predeterminado sensato para la mayoría de los sistemas.
- Acceso de lectura. El texto de las consultas puede contener valores sensibles. Controla quién puede leer las vistas de Query Store en producción.
Errores comunes
- Comparar intervalos con cargas diferentes, y luego llamar regresión a un cambio de carga de trabajo.
- Usar promedios no ponderados entre intervalos, de modo que un intervalo tranquilo cuenta tanto como uno con mucha actividad.
- Forzar un plan sin comprobar que el plan cambió.
- Forzar un plan para una consulta sensible a parámetros y trasladar el problema a otros llamadores.
- No volver a revisar nunca los planes forzados, de modo que los fallos pasan desapercibidos durante meses.
- Ignorar las ejecuciones abortadas, lo que oculta los timeouts.
- Dejar que Query Store pase a solo lectura e investigar un periodo que nunca registró.
Recomendación
Haz que Query Store forme parte de las operaciones normales, no una herramienta de emergencia: mantenlo en READ_WRITE, dimensiónalo para al menos unas semanas de historial y aprende a comparar dos intervalos antes de necesitarlo.
Cuando aparezca una regresión, sigue las evidencias: confirma que los intervalos son comparables, ordena las consultas por el tiempo extra que añadieron, comprueba si el plan cambió, compara los planes candidatos, valida el resultado y programa la corrección duradera. Vuelve al flujo de optimización para el orden completo, usa el monitoreo en tiempo real para la actividad actual y conserva a mano la referencia de Query Store.
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Sigue aprendiendo
- Guía rápidaReferencia rápida de Query StoreUsa Query Store de SQL Server para encontrar regresiones, comparar planes, analizar el historial de ejecución, forzar planes o deshacer su forzado con seguridad, y entender cuándo los datos de Query Store pueden llevarte a conclusiones equivocadas.Abrir guía rápida
- Hoja de rutaSQL Server Troubleshooting & DiagnosticsUn flujo práctico con Query Store para regresiones de SQL Server: compara intervalos, encuentra consultas que se volvieron lentas, revisa cambios de plan, fuerza planes con seguridad y deshazlos.Ver hoja de ruta
¿Qué debería crear después?
Estoy construyendo Javi Builds alrededor de problemas reales que enfrentan los ingenieros de datos. Dime qué tema te gustaría ver después.
Mantente al día
Recibe solo las novedades de Javi Builds que realmente te interesan.
Revisa tu bandeja de entrada
Te he enviado un enlace de confirmación. Tu suscripción se activará cuando la confirmes.