SQL Server lento: los 8 lugares donde siempre está el problema

Las consultas más costosas

SELECT TOP 15
       qs.execution_count                                AS ejecuciones,
       qs.total_worker_time / 1000                       AS cpu_total_ms,
       qs.total_worker_time / qs.execution_count / 1000  AS cpu_promedio_ms,
       qs.total_logical_reads / qs.execution_count       AS lecturas_promedio,
       qs.total_elapsed_time / qs.execution_count / 1000 AS duracion_promedio_ms,
       SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
           ((CASE qs.statement_end_offset
               WHEN -1 THEN DATALENGTH(st.text)
               ELSE qs.statement_end_offset END
             - qs.statement_start_offset) / 2) + 1)      AS consulta
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

Qué buscar

Ordena por CPU total y luego por lecturas lógicas promedio. Una consulta con 200,000 lecturas lógicas por ejecución está recorriendo tablas completas: casi siempre le falta un índice o tiene una función aplicada sobre la columna del WHERE, lo que impide usar el que ya existe.

Ojo con el peor sospechoso

La consulta más lenta no siempre es la más dañina. Una que tarda 3 segundos y corre 40,000 veces al día hace más daño que una de 40 segundos que corre dos veces. Prioriza por costo total, no por duración individual.

Índices: faltantes, inútiles y duplicados

SELECT TOP 10
       ROUND(s.avg_total_user_cost * s.avg_user_impact
             * (s.user_seeks + s.user_scans), 0)  AS beneficio_estimado,
       d.statement                                 AS tabla,
       d.equality_columns, d.inequality_columns, d.included_columns,
       s.user_seeks, s.user_scans, s.last_user_seek
FROM   sys.dm_db_missing_index_group_stats s
JOIN   sys.dm_db_missing_index_groups g  ON s.group_handle = g.index_group_handle
JOIN   sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
ORDER BY beneficio_estimado DESC;

SELECT OBJECT_NAME(i.object_id) AS tabla,
       i.name                    AS indice,
       us.user_updates           AS escrituras_pagadas,
       ISNULL(us.user_seeks,0) + ISNULL(us.user_scans,0)
       + ISNULL(us.user_lookups,0) AS lecturas
FROM   sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
       ON  i.object_id = us.object_id
       AND i.index_id  = us.index_id
       AND us.database_id = DB_ID()
WHERE  i.type_desc = 'NONCLUSTERED'
  AND  OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND  ISNULL(us.user_seeks,0) + ISNULL(us.user_scans,0)
       + ISNULL(us.user_lookups,0) = 0
ORDER BY us.user_updates DESC;

Cómo actuar

No apliques la sugerencia tal cual: consolida. Si el motor pide tres índices parecidos sobre la misma tabla, casi siempre uno bien diseñado cubre los tres. Y antes de borrar un índice sin uso, confirma que las estadísticas de uso llevan acumulándose desde el último reinicio del servicio —no desde ayer.

Estadísticas desactualizadas y parameter sniffing

SELECT OBJECT_NAME(s.object_id)  AS tabla,
       s.name                    AS estadistica,
       sp.last_updated,
       sp.rows,
       sp.modification_counter   AS filas_cambiadas_desde_entonces
FROM   sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE  sp.modification_counter > 0
ORDER BY sp.modification_counter DESC;

Acción

Actualiza con UPDATE STATISTICS y muestreo suficiente en las tablas grandes; el muestreo por defecto se queda corto en tablas de decenas de millones de filas. Si el problema reaparece con ciertos parámetros y no con otros, ya no es estadísticas: es parameter sniffing, y se ataca con OPTIMIZE FOR UNKNOWN, recompilación selectiva o reescribiendo el procedimiento.

Bloqueos entre sesiones

SELECT r.session_id            AS bloqueada,
       r.blocking_session_id   AS bloqueadora,
       r.wait_type, r.wait_time / 1000.0 AS esperando_s,
       r.wait_resource,
       DB_NAME(r.database_id)  AS base,
       t.text                  AS consulta
FROM   sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE  r.blocking_session_id <> 0;
 
-- ¿Qué está corriendo la sesión bloqueadora?
-- DBCC INPUTBUFFER(<session_id>);

Causas habituales

Transacciones abiertas demasiado tiempo —incluida la aplicación que abre transacción, llama a un servicio externo y espera—, orden inconsistente de acceso a las tablas, y reportes pesados corriendo contra producción sin aislamiento adecuado. Para lo último, un snapshot isolation bien evaluado resuelve el 80% de los casos.

Memoria mal configurada

SELECT name, value_in_use
FROM   sys.configurations
WHERE  name IN ('max server memory (MB)', 'min server memory (MB)',
                'max degree of parallelism',
                'cost threshold for parallelism');
 
SELECT counter_name, cntr_value
FROM   sys.dm_os_performance_counters
WHERE  counter_name IN ('Page life expectancy', 'Memory Grants Pending');

Valores de referencia

Deja entre 4 y 8 GB al sistema operativo, más margen si el servidor comparte con otros servicios. Page life expectancy conviene leerlo como al menos 300 segundos por cada 4 GB asignados —no el mítico 300 fijo, que viene de servidores con 4 GB—. Y Memory Grants Pending debe ser cero de forma sostenida: cualquier otra cosa es presión real de memoria.

tempdb

SELECT COUNT(*) AS archivos_de_datos
FROM   tempdb.sys.database_files
WHERE  type_desc = 'ROWS';
 
-- Contención de páginas de asignación: base_id 2 es tempdb
SELECT session_id, wait_type, wait_resource, wait_time
FROM   sys.dm_exec_requests
WHERE  wait_type LIKE 'PAGELATCH%'
  AND  wait_resource LIKE '2:%';

Regla práctica

Varios archivos de datos del mismo tamaño y con el mismo incremento: uno por núcleo hasta ocho, y si sigue habiendo contención, subir de cuatro en cuatro. Tamaño inicial generoso para que no crezca en horas pico, y tempdb en el disco más rápido que tengas.

Almacenamiento y crecimiento automático

SELECT DB_NAME(vfs.database_id)                                  AS base,
       mf.physical_name,
       vfs.num_of_reads, vfs.num_of_writes,
       vfs.io_stall_read_ms  / NULLIF(vfs.num_of_reads, 0)       AS ms_por_lectura,
       vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0)      AS ms_por_escritura
FROM   sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN   sys.master_files mf
       ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY ms_por_lectura DESC;

Referencias

Archivos de datos: por debajo de 20 ms es aceptable, por debajo de 10 es bueno. Log de transacciones: idealmente menos de 5 ms, porque cada confirmación espera ahí. Si el log está por arriba, ese solo dato explica una lentitud generalizada.

El crecimiento automático en porcentaje

Un archivo configurado para crecer en 10% funciona bien con 1 GB y es un desastre con 400 GB: cada crecimiento congela las escrituras mientras reserva 40 GB. Usa incrementos fijos y razonables —512 MB o 1 GB— y dimensiona los archivos por adelantado. Verifica también que esté habilitada la inicialización instantánea de archivos.

Configuración de instancia

Diagnóstico en una sesión

Imprimir o guardar como PDF

¿Vas a comprarle más RAM al servidor sin saber si es el problema?

Hacemos el diagnóstico completo de tu instancia: esperas, consultas costosas, índices, estadísticas, tempdb, memoria y almacenamiento. Te entregamos los hallazgos priorizados por impacto, con la corrección de cada uno y una línea base para medir la mejora. Casi siempre sale más barato que el hardware.

• —Diagnóstico y optimización de rendimiento en bases de datos
• —Mantenimiento gestionado de SQL Server y PostgreSQL
• —Respaldos, alta disponibilidad y pruebas de restauración
• —Observabilidad para aplicaciones .NET

Diagnóstico inicial sin costo. Si tu instancia está bien configurada, te lo decimos con los datos en la mano.