Diagnosticar concesiones de memoria en SQL Server

Un informe puede ejecutarse rápido en solitario y formar una cola cuando varios usuarios lo lanzan a la vez. Una posible explicación es la memoria de trabajo reservada para ordenar y realizar operaciones hash. Optimizar únicamente una ejecución aislada puede ocultar cuánta memoria retiene y cuánto obliga a esperar a otras solicitudes.

Capturar la situación mientras ocurre

Recoge los datos durante el incidente. Esta consulta muestra solicitudes de memoria activas junto con información de las peticiones actuales. Solo lee información, pero requiere permisos de diagnóstico apropiados: VIEW SERVER PERFORMANCE STATE en SQL Server 2022 y posteriores, y normalmente VIEW SERVER STATE en versiones anteriores.

SELECT
    mg.session_id,
    mg.request_id,
    mg.request_time,
    mg.grant_time,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.used_memory_kb,
    mg.max_used_memory_kb,
    mg.wait_time_ms,
    r.status,
    r.wait_type,
    r.total_elapsed_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_memory_grants AS mg
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = mg.session_id
   AND r.request_id = mg.request_id
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
ORDER BY mg.requested_memory_kb DESC;

El texto pertenece al lote completo. Un procedimiento puede contener más instrucciones que la responsable de la concesión. Usa los desplazamientos de la instrucción o el plan para localizarla. Conserva juntos los identificadores de sesión y solicitud; la misma sesión puede estar haciendo otro trabajo cuando vuelvas a observarla.

Si grant_time no tiene valor, la solicitud todavía espera la concesión. Compara kilobytes solicitados y concedidos con uso actual y máximo observado. Son datos en curso, no un historial terminado. Una consulta recién iniciada puede utilizar todavía una parte pequeña de lo reservado. Guarda varias muestras con fecha y hora.

RESOURCE_SEMAPHORE señala espera para obtener memoria de ejecución. RESOURCE_SEMAPHORE_QUERY_COMPILE corresponde a compilación. Examina también las consultas que ya poseen reservas grandes. La consulta en espera puede ser la víctima, mientras otra mantiene ocupado el espacio de trabajo.

Separar tres problemas distintos

Una concesión sobredimensionada reserva mucho más de lo necesario. Una insuficiente puede provocar que un sort o hash derrame datos intermedios a tempdb. Una cola también puede surgir con concesiones individuales razonables si coinciden demasiadas operaciones grandes. Un porcentaje global de memoria no diferencia estas situaciones.

Supón un informe que pide 600 MB y utiliza repetidamente solo 25 MB. Investiga la estimación de filas y el ancho de las filas que llegan a los operadores intensivos en memoria. No concluyas que hay desperdicio por una única muestra temprana. Confirma con el plan real terminado y parámetros que produzcan resultados grandes.

Para una consulta con derrames, compara las filas de entrada estimadas y reales del operador. Un join subestimado puede aumentar mucho el volumen que necesita hash. Una proyección ancha puede volver costoso un conjunto de tamaño moderado. Eliminar columnas innecesarias antes de ordenar ayuda únicamente si se conserva el significado de la consulta.

Un índice que suministra el orden requerido puede evitar una ordenación. Una preagregación con la granularidad correcta puede reducir las filas de un join. Ninguna es una receta universal: el índice añade coste de escritura y una agregación equivocada puede producir totales plausibles pero incorrectos.

Validar con concurrencia representativa

Ejecuta en paralelo una mezcla realista de informes y parámetros. Mide solicitudes completadas por minuto y latencias altas además de la duración individual. Una reserva menor puede permitir más admisiones pero aumentar el trabajo de tempdb y reducir el rendimiento total. Una ejecución aislada más rápida con una concesión enorme puede perjudicar al resto.

Memory Grant Feedback puede ajustar concesiones entre ejecuciones en versiones y modos compatibles. Su comportamiento y persistencia dependen de la versión y configuración. Obsérvalo como una optimización, sin garantizar un tamaño adecuado para la primera ejecución, distribuciones distintas o planes recién compilados.

No empieces con hints de memoria generalizados ni con un aumento global de memoria del servidor. Determina primero si la presión procede de estimaciones, ancho, ordenación o solapamiento excesivo. Escalonar varios exports grandes puede ayudar más que limitar cada consulta.

Termina comparando ventanas de carga equivalentes: menos solicitudes pendientes, derrames aceptables, rendimiento estable y resultados correctos. Conserva planes y marcas de tiempo. Así podrás explicar si el cambio redujo demanda, mejoró estimaciones o simplemente trasladó la espera a otro recurso.

Referencias técnicas: Microsoft Learn: Memory grant diagnostics · Microsoft Learn: sys.dm_exec_query_memory_grants · Microsoft Learn: Memory grant feedback.