SQL Server
Flujo de trabajo para optimizar rendimiento en SQL Server: de consulta lenta a causa raíz
Un flujo práctico de tuning en SQL Server para pasar de una lentitud vaga a la causa raíz usando waits, Query Store, planes de ejecución, bloqueos, I/O, CPU, memoria y contexto de carga.
En este artículo
Optimizar el rendimiento no consiste en encontrar una DMV mágica, un índice que falta o un plan de ejecución.
Lo difícil es pasar de un síntoma vago:
SQL Server va lento.
a una causa raíz defendible:
Este procedimiento almacenado sufrió una regresión tras un cambio de plan: ahora recorre 80 millones de filas en lugar de buscar 40.000, provoca lecturas físicas, mantiene los bloqueos durante más tiempo y es el principal responsable de la ralentización de la carga.
Eso requiere un flujo de trabajo.
Un proceso útil de optimización en SQL Server debe responder cinco preguntas:
1. ¿Qué cambió?
2. ¿En qué se va el tiempo?
3. ¿Qué carga de trabajo es la responsable?
4. ¿Por qué esa carga es costosa?
5. ¿La corrección mejoró realmente el sistema?
Este artículo presenta un flujo práctico que puedes reutilizar en incidentes de producción, en la validación de versiones y en el trabajo continuo de rendimiento.
Empieza por el síntoma, no por la solución
Uno de los errores de optimización más comunes es empezar por una solución.
Ejemplos:
"Añadir un índice."
"Actualizar las estadísticas."
"Recompilar el procedimiento."
"Aumentar MAXDOP."
"Vaciar la caché de planes."
Cualquiera de ellas podría ayudar.
Ninguna debería ser la primera pregunta.
Empieza por definir el síntoma con precisión.
En lugar de:
Los informes van lentos.
recoge:
¿Qué informe?
¿Qué procedimiento o consulta?
¿Cuándo empezó a ir lento?
¿Afecta a todas las solicitudes?
¿El problema es constante o intermitente?
¿Cuál es la duración esperada?
¿Cuál es la duración real?
¿Cambiaron el CPU, las lecturas, los waits, los bloqueos o la memoria?
¿Cambió algún despliegue o el volumen de datos?
Una buena investigación de rendimiento empieza con una afirmación medible.
Por ejemplo:
Antes:
Duración P95 = 2,1 s
Después de la versión:
Duración P95 = 14,7 s
Ahora tienes algo que investigar y algo que validar después de la corrección.
El flujo de trabajo
Un flujo práctico de optimización en SQL Server se ve así:
- Síntoma
- Acotar el problema
- Revisar la carga actual
- Revisar waits / bloqueos
- Encontrar las consultas costosas
- Consultar el historial de Query Store
- Leer el plan de ejecución
- Comparar estimaciones con la realidad
- Revisar índices / estadísticas / forma de los datos
- Cambiar una sola cosa
- Volver a medir
El orden importa porque evita que optimices lo que no es.
Paso 1: determinar si el problema es local o sistémico
Primero decide si estás investigando:
una consulta
una aplicación
una base de datos
un servidor
o toda la plataforma
Si solo un procedimiento almacenado va lento mientras todo lo demás es normal, empieza por esa consulta.
Si muchas consultas sin relación entre sí se ralentizan al mismo tiempo, busca una restricción compartida:
- saturación de CPU;
- latencia del almacenamiento;
- presión de memoria;
- bloqueos;
- presión sobre tempdb;
- presión sobre los hilos de trabajo (workers);
- inestabilidad de la caché de planes;
- un cambio de despliegue;
- un evento de infraestructura.
Esta distinción evita esfuerzo desperdiciado.
Un mal plan en un procedimiento no explicará por qué diez bases de datos sin relación se volvieron lentas exactamente al mismo tiempo.
Paso 2: inspeccionar lo que ocurre ahora mismo
Cuando el problema está activo, captura la carga actual antes de cambiar nada.
Un punto de partida sencillo:
SELECT
r.session_id,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.writes,
r.logical_reads,
r.wait_type,
r.wait_time,
r.wait_resource,
r.blocking_session_id,
DB_NAME(r.database_id) AS database_name,
s.login_name,
s.host_name,
s.program_name,
SUBSTRING(
t.text,
(r.statement_start_offset / 2) + 1,
(
(
CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE r.statement_end_offset
END
- r.statement_start_offset
) / 2
) + 1
) AS statement_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;Esto ayuda a responder:
- qué se está ejecutando;
- cuánto tiempo lleva ejecutándose;
- cuánto CPU ha consumido;
- cuántas lecturas ha realizado;
- qué está esperando;
- si está bloqueado;
- qué aplicación y qué host lo enviaron.
Es evidencia del estado actual.
No debe confundirse con evidencia histórica.
Paso 3: leer los waits como síntomas
Los waits te dicen qué está esperando SQL Server.
No te dicen automáticamente la causa raíz.
Ejemplos:
PAGEIOLATCH_*
→ esperando páginas de datos desde el almacenamiento
LCK_M_*
→ esperando bloqueos
CXPACKET / CXCONSUMER
→ coordinación de la ejecución en paralelo
RESOURCE_SEMAPHORE
→ esperando memoria de ejecución de consultas
WRITELOG
→ esperando la escritura del log
SOS_SCHEDULER_YIELD
→ el worker cedió el scheduler de CPU
ASYNC_NETWORK_IO
→ SQL Server espera a que el cliente consuma las filas
La pregunta correcta no es:
¿Cómo elimino este wait?
La mejor pregunta es:
¿Por qué esta carga de trabajo produce tanto de este wait?
Por ejemplo, PAGEIOLATCH_SH puede deberse a la latencia del almacenamiento.
O a una consulta que de repente recorre millones de páginas por culpa de un mal plan.
Arreglar el almacenamiento no arreglará el plan.
Paso 4: revisar los bloqueos antes de optimizar el costo de las consultas
Una consulta puede ir lenta porque es costosa.
También puede ir lenta porque está esperando detrás de otra transacción.
Son problemas distintos.
Revisa:
SELECT
r.session_id,
r.blocking_session_id,
r.status,
r.wait_type,
r.wait_time,
r.wait_resource,
r.total_elapsed_time,
DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests AS r
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;Después inspecciona el bloqueador principal (head blocker).
Preguntas que conviene hacerse:
- ¿Hay una transacción abierta?
- ¿El bloqueador se está ejecutando activamente?
- ¿La aplicación mantiene una transacción abierta mientras está inactiva?
- ¿Un cambio de índice o de plan hizo que el bloqueador mantuviera los bloqueos más tiempo?
- ¿Es adecuado el nivel de aislamiento?
- ¿La carga bloqueada lee más filas que antes?
Los bloqueos suelen ser un efecto secundario de una regresión de consultas.
Un procedimiento que antes terminaba en 200 ms y ahora tarda 20 segundos mantiene los bloqueos mucho más tiempo.
Paso 5: encontrar la carga costosa con la métrica adecuada
«La consulta más costosa» significa cosas distintas según el problema.
Puede interesarte:
el CPU
las lecturas lógicas
las lecturas físicas
la duración
las escrituras
el número de ejecuciones
la concesión de memoria
el uso de tempdb
Una consulta que se ejecuta una vez al día durante 60 segundos puede importar menos que una consulta que se ejecuta 100.000 veces por hora en 20 ms.
Usa la métrica que corresponda al síntoma.
Para las estadísticas agregadas en caché:
SELECT TOP (20)
qs.execution_count,
qs.total_worker_time,
qs.total_elapsed_time,
qs.total_logical_reads,
qs.total_logical_writes,
qs.total_worker_time / NULLIF(qs.execution_count, 0) AS avg_cpu,
qs.total_elapsed_time / NULLIF(qs.execution_count, 0) AS avg_elapsed,
qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads,
DB_NAME(st.dbid) AS database_name,
st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;Recuerda que las DMV de la caché de planes se reinician cuando los planes salen de la caché o se reinicia la instancia.
Son útiles, pero no son un historial de rendimiento duradero.
Paso 6: usar Query Store para el historial y las regresiones
Query Store es una de las herramientas más útiles para responder:
¿Esta consulta siempre fue lenta?
Puede conservar:
- el texto de las consultas;
- los planes;
- las estadísticas de ejecución;
- las estadísticas de waits cuando están habilitadas y son compatibles;
- el historial de planes.
Eso facilita mucho comparar el antes y el después.
Una regresión podría verse así:
Plan 12
duración media: 400 ms
lecturas medias: 40.000
Plan 18
duración media: 8,4 s
lecturas medias: 3.200.000
Ahora tienes evidencia sólida de que el problema está relacionado con el plan.
Query Store es especialmente útil después de:
- cambios de nivel de compatibilidad;
- cambios de índices;
- cambios de estadísticas;
- un despliegue;
- cambios en la distribución de los parámetros;
- actualizaciones de SQL Server;
- una migración de base de datos.
La comparación importante no es solo la duración media.
Revisa:
- la media;
- el P95/P99 si está disponible en tu capa de monitoreo;
- el CPU;
- las lecturas lógicas;
- las escrituras;
- el número de ejecuciones;
- el ID del plan;
- el intervalo de tiempo.
Las medias pueden ocultar regresiones intermitentes.
Paso 7: leer el plan de ejecución como una historia
Un plan de ejecución no es solo una colección de iconos.
Léelo así:
¿Cómo espera SQL Server obtener las filas?
¿Cuántas filas espera?
¿Cuántas filas llegan realmente?
¿Dónde se multiplican los datos?
¿Dónde se filtran?
¿Dónde se ordenan?
¿Dónde hay derrames (spills)?
¿Dónde hay seeks o scans?
Empieza por las mayores discrepancias.
Por ejemplo:
Filas estimadas: 1
Filas reales: 4.800.000
Eso es más interesante que un icono de advertencia amarillo por sí solo.
Los errores de estimación grandes pueden influir en:
- el tipo de join;
- el orden de los joins;
- la concesión de memoria;
- el paralelismo;
- la elección entre seek y scan.
Paso 8: comparar filas estimadas y reales
La estimación de cardinalidad es fundamental para la calidad del plan.
Cuando las estimaciones son erróneas, pregúntate por qué.
Las causas habituales incluyen:
- estadísticas desactualizadas;
- datos sesgados;
- predicados correlacionados;
- sensibilidad a los parámetros;
- variables de tabla;
- expresiones escalares;
- conversiones implícitas;
- relaciones complejas entre varias columnas.
No actualices automáticamente todas las estadísticas.
Primero determina si la mala estimación contribuye realmente al mal plan.
Paso 9: buscar sensibilidad a los parámetros
Un procedimiento almacenado puede ser rápido con un parámetro y pésimo con otro.
Ejemplo:
@MerchantId = 12
→ 50 filas
@MerchantId = 999
→ 40 millones de filas
Un plan ideal para el primero puede ser pésimo para el segundo.
Las señales incluyen:
- una gran variación en el tiempo de ejecución;
- varias distribuciones de datos muy distintas;
- cambios de plan correlacionados con el rendimiento;
- un patrón de parámetros que siempre es lento.
Las respuestas posibles dependen de la situación:
- reescribir la consulta;
- mejores índices o estadísticas;
- la optimización Parameter Sensitive Plan cuando sea aplicable;
OPTION (RECOMPILE)para casos concretos;- SQL dinámico;
- forzar un plan como técnica de estabilización temporal.
No etiquetes cada regresión como «parameter sniffing».
Demuestra la relación.
Paso 10: revisar los índices después de entender el plan
Las recomendaciones de índices que faltan pueden ser útiles.
Son sugerencias, no diseños.
Antes de añadir un índice, pregúntate:
¿Qué predicado necesita soporte?
¿Qué join necesita soporte?
¿Se puede ampliar un índice existente?
¿Qué selectividad tienen las claves iniciales?
¿Cuánta sobrecarga de escritura añadirá?
¿Creará otro índice casi duplicado?
Supongamos que la consulta es:
SELECT
sale_id,
amount,
sale_timestamp
FROM dbo.Sales
WHERE merchant_id = @merchant_id
AND sale_timestamp >= @start_date
AND sale_timestamp < @end_date;Un índice posible sería:
CREATE INDEX IX_Sales_Merchant_Timestamp
ON dbo.Sales (merchant_id, sale_timestamp)
INCLUDE (sale_id, amount);Ese diseño sale del patrón de acceso.
No debe salir de copiar a ciegas la sugerencia verde de índice que falta.
Paso 11: revisar las estadísticas y la distribución de los datos
SQL Server elige los planes a partir de estimaciones.
Las estadísticas forman parte de ese modelo.
Preguntas útiles:
- ¿Cuándo se actualizaron las estadísticas por última vez?
- ¿Creció mucho la tabla?
- ¿Cambió la distribución?
- ¿El predicado está muy sesgado?
- ¿Una clave ascendente provoca problemas de estimación?
- ¿Son adecuados los umbrales de actualización automática para la carga?
Puedes inspeccionar los metadatos de las estadísticas con:
SELECT
OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
OBJECT_NAME(s.object_id) AS table_name,
s.name AS stats_name,
STATS_DATE(s.object_id, s.stats_id) AS stats_date
FROM sys.stats AS s
WHERE s.object_id = OBJECT_ID('dbo.Sales')
ORDER BY stats_date;Una fecha de estadísticas más reciente no garantiza una buena estimación.
La distribución sigue importando.
Paso 12: revisar el I/O antes de culpar al almacenamiento
Muchas lecturas y un almacenamiento lento no son lo mismo.
Supongamos:
Consulta A
lecturas lógicas: 200.000.000
latencia del almacenamiento: normal
El verdadero problema puede ser la cantidad de datos que SQL Server pide leer al almacenamiento.
Ahora supongamos:
Consulta B
lecturas lógicas: 20.000
lecturas físicas: moderadas
latencia de lectura: 80 ms
Eso apunta con más fuerza al almacenamiento.
Separa:
cuánto I/O solicita la consulta
de:
lo rápido que el almacenamiento completa ese I/O
Las dos cosas importan.
Paso 13: revisar el CPU en el contexto de la carga
Un CPU alto es un síntoma.
Averigua qué consultas lo están consumiendo.
Las causas habituales incluyen:
- scans grandes;
- joins ineficientes;
- trabajo escalar repetido;
- conversiones implícitas;
- malas estimaciones;
- compilaciones excesivas;
- consultas pequeñas de muy alta frecuencia;
- planes paralelos;
- procesamiento de JSON/XML;
- ordenar o aplicar hash a conjuntos grandes.
No empieces cambiando la configuración de paralelismo del servidor porque el CPU está alto.
Primero identifica la carga que genera ese CPU.
Paso 14: inspeccionar las concesiones de memoria y los spills
Algunas consultas van lentas porque necesitan memoria para:
- ordenar;
- hash join;
- hash aggregate.
Si la concesión es demasiado pequeña, el trabajo puede derramarse (spill) a tempdb.
Si es demasiado grande, las consultas concurrentes pueden tener que esperar memoria.
Busca:
Advertencias de ordenación
Advertencias de hash
Spills
Waits RESOURCE_SEMAPHORE
Concesiones de memoria grandes
Una vez más, las estimaciones importan.
Una consulta que espera 1.000 filas pero recibe 50 millones puede obtener una concesión de memoria completamente inadecuada.
Paso 15: prestar atención a tempdb
La presión sobre tempdb puede aparecer a través de:
- spills;
- tablas temporales;
- el version store;
- ordenaciones;
- operaciones hash;
- tablas de trabajo internas;
- muchas asignaciones concurrentes.
Pero «tempdb está ocupada» todavía no es una causa raíz.
Pregúntate:
¿Qué carga de trabajo genera la actividad en tempdb?
Después optimiza la carga o la configuración de tempdb basándote en evidencias.
Paso 16: comparar versiones con la misma carga
La optimización es mucho más fiable cuando comparas periodos controlados.
Por ejemplo:
Nivel de compatibilidad 130
vs
Nivel de compatibilidad 150
o:
versión A
vs
versión B
Recoge:
- la duración;
- el CPU;
- las lecturas lógicas;
- las escrituras;
- el número de ejecuciones;
- el plan;
- el perfil de waits.
Una tabla de comparación útil:
| Métrica | Antes | Después | Cambio |
|---|---|---|---|
| Duración media | 1,8 s | 2,3 s | +27,8 % |
| Duración P95 | 3,2 s | 18,1 s | +465,6 % |
| CPU | 910 ms | 895 ms | -1,6 % |
| Lecturas lógicas | 240k | 220k | -8,3 % |
| Escrituras | 1.200 | 1.400 | +16,7 % |
El P95 cuenta una historia muy distinta de la media.
Paso 17: cambiar una sola cosa importante cada vez
Si:
añades tres índices
actualizas todas las estadísticas
fuerzas un plan
cambias MAXDOP
reescribes la consulta
aumentas la memoria
y el rendimiento mejora, no sabes por qué.
En producción, a veces una emergencia exige varias acciones.
Para la optimización normal, prefiere cambios controlados:
hipótesis
→ un solo cambio
→ medir
→ conservar o revertir
Así la ingeniería de rendimiento se vuelve reproducible.
Paso 18: validar la corrección en varios niveles
Una consulta puede mejorar mientras el sistema empeora.
Por ejemplo:
La duración de la consulta mejora un 40 %
pero
el nuevo índice aumenta el costo de escritura un 30 %
y
el almacenamiento crece 200 GB
Valida:
A nivel de consulta
- la duración;
- el CPU;
- las lecturas;
- las escrituras;
- el plan;
- los spills.
A nivel de carga
- el rendimiento (throughput);
- la latencia P95/P99;
- los bloqueos;
- los waits;
- la concurrencia.
A nivel de sistema
- el CPU;
- la memoria;
- la latencia del almacenamiento;
- el rendimiento del log;
- tempdb.
Una buena corrección mejora el problema buscado sin crear uno mayor en otra parte.
Paso 19: conservar las evidencias
Los incidentes de rendimiento son mucho más fáciles de resolver cuando conservas historial.
Las evidencias útiles incluyen:
marca de tiempo
base de datos
query_id
plan_id
duración
CPU
logical_reads
escrituras
waits
bloqueos
versión/release
nivel de compatibilidad
Por eso Query Store y un sistema de monitoreo externo se complementan.
Query Store ofrece un historial duradero de consultas y planes dentro de SQL Server.
El monitoreo aporta contexto de la carga y de la infraestructura a lo largo del tiempo.
Paso 20: construir una lista de comprobación de optimización repetible
Cuando empiece un incidente, usa siempre las mismas preguntas.
Alcance
- ¿Una consulta o todo el servidor?
- ¿Una base de datos o varias?
- ¿Constante o intermitente?
- ¿Cuándo empezó?
Carga actual
- ¿Qué se está ejecutando?
- ¿Qué está esperando?
- ¿Qué está bloqueado?
- ¿Qué consume CPU o lecturas?
Historial
- ¿Hubo una regresión de la duración?
- ¿Cambió el plan?
- ¿Cambió el número de ejecuciones?
- ¿Cambió el volumen de datos?
Plan
- ¿Estimaciones frente a realidad?
- ¿Scan o seek?
- ¿Estrategia de join?
- ¿Ordenación/hash?
- ¿Spill?
- ¿Paralelismo?
- ¿Conversión implícita?
Estructuras de datos
- ¿Índices?
- ¿Estadísticas?
- ¿Sesgo?
- ¿Particionamiento?
- ¿Crecimiento de filas?
Validación
- ¿Mejoró la métrica?
- ¿Mejoró el P95?
- ¿Mejoraron el CPU y las lecturas?
- ¿Cambiaron los bloqueos?
- ¿Empeoró otra carga?
Errores habituales de optimización
Vaciar primero la caché de planes
Destruye evidencias útiles y afecta a cargas que no tienen relación.
Añadir todos los índices que faltan
Genera índices que se solapan, sobrecarga de escritura, costo de almacenamiento y costo de mantenimiento.
Tratar cada wait como la causa raíz
Los waits son evidencias, no diagnósticos automáticos.
Mirar solo las medias
Las regresiones intermitentes suelen aparecer primero en el P95/P99.
Ignorar el número de ejecuciones
Una consulta pequeña ejecutada millones de veces puede dominar una carga.
Optimizar una consulta sin validar la semántica de negocio
Un resultado incorrecto más rápido sigue siendo incorrecto.
Cambiar la configuración del servidor antes de entender la carga
Los cambios a nivel de instancia pueden afectar a todas las bases de datos.
Un flujo práctico ante un incidente
Cuando alguien dice:
Producción va lenta.
yo seguiría esta secuencia:
1. Confirmar la ralentización con una métrica medible.
2. Comprobar si el problema es local o sistémico.
3. Capturar las solicitudes activas, los waits y los bloqueos.
4. Identificar las consultas que impulsan el CPU, las lecturas o la duración.
5. Usar Query Store para comparar el comportamiento actual con el histórico.
6. Comparar los planes.
7. Buscar problemas de estimación y sensibilidad a los parámetros.
8. Evaluar índices y estadísticas.
9. Revisar el I/O, las concesiones de memoria, tempdb y la presión del sistema.
10. Formular una hipótesis.
11. Hacer el cambio útil más pequeño.
12. Volver a medir las mismas métricas.
Eso es optimizar el rendimiento como un proceso de ingeniería y no a base de prueba y error.
Reflexiones finales
Optimizar el rendimiento de SQL Server no consiste principalmente en conocer más consultas sobre DMV.
Consiste en usar las evidencias en el orden correcto.
Empieza por el síntoma.
Mide la carga actual.
Separa los bloqueos del costo de las consultas.
Usa los waits para entender en qué se va el tiempo.
Usa Query Store para entender el historial.
Usa el plan de ejecución para explicar el comportamiento.
Usa índices y estadísticas solo después de entender el patrón de acceso.
Después valida el resultado con las mismas métricas que demostraron que el problema existía.
Un buen flujo de optimización convierte:
SQL Server va lento.
en:
Sabemos qué carga sufrió la regresión, por qué, qué cambió, qué la corrigió y cuánto mejor está ahora.
Ese es el nivel de evidencia al que debería aspirar el trabajo de rendimiento en producción.
Seguir el recorrido principal
Pasa de las consultas en ejecución a un ID de sesión/SPID y después a la cadena de bloqueos cuando las sesiones se esperan. Usa regresiones de Query Store para el historial y el monitoreo en tiempo real para observar repetidamente el estado actual. Conserva la lista de comprobación de SQL Server como guía operativa.
Las referencias Flujo de diagnóstico, Uso de índices e Índices ausentes apoyan los pasos correspondientes sin sustituir la investigación.
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Sigue aprendiendo
- Guía rápidaEncontrar consultas con alto consumo de CPUIdentifica las consultas de SQL Server que más CPU consumen ahora y en el historial, distingue el CPU total del CPU por ejecución y elige el siguiente paso de optimización adecuado.Abrir guía rápida
- RecursoContenido en inglésSQL Server Performance Tuning ChecklistA one-page troubleshooting reference for slow SQL Server workloads: ten steps from incident scope to measured change, with three diagnostic queries.Ver recurso
¿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.