SQL consume mucha RAM. Solución a problemas de memoria

Bienvenidos a este tercer artículo sobre el uso que hace SQL Server de la memoria RAM. En los últimos días ya hemos visto cómo se gestiona la RAM en SQL y la configuración especial LPIM para optimizar el rendimiento en caso de presión de memoria. Pero si recordáis, en este último artículo, os decía que antes de tocar LPIM lo mejor era afrontar el problema. Por ello vamos a dedicar este artículo a ver cómo podemos detectar y afrontar problemas de consumo excesivo de memoria RAM.

Determinando el consumo excesivo de memoria

¿Realmente tenemos un problema? Esta es la principal pregunta, normalmente, a ojos del que no conoce el funcionamiento de SQL Server puede ser preocupante la gran cantidad de memoria que SQL Server suele tener reservada. Sin embargo, nosotros que somos buenos DBAs (y hemos leído el artículo sobre la RAM del otro día) sabemos que no nos podemos fiar de eso pues SQL Server siempre va a reservar toda la RAM que tenga a su disposición hasta el máximo que le hayamos asignado. Esto no quiere decir que la esté usando así que vamos a ver cómo podemos realmente darnos cuenta. 

Analizando el consumo de memoria RAM de SQL Server

SQL Server pone a nuestra disposición unas vistas de administración dinámica (dm views) que nos pueden dar cierta información sobre la RAM. Estas vistas son, según la documentación oficial, sys.dm_os_process_memory y sys.dm_os_sys_memory. Aunque a mi me gusta también consultar sys.dm_os_sys_info y sys.dm_os_performance_counters.

Vista sys.dm_os_process_memory

En esta vista podemos encontrar datos sobre la cantidad de memoria en uso y disponible en nuestro SQL Server. Podéis consultar la documentación oficial de la vista aquí. Yo os recomiendo estas:

En esta consulta de Pinal Dave, muy interesante podemos ver la memoria en uso y un par de avisos sobre presión de memoria. Si los dos avisos están en 0 es que todo está bien.

SELECT physical_memory_in_use_kb/1024 [Physical Memory Used in MB],
process_physical_memory_low [Physical Memory Low],
process_virtual_memory_low [Virtual Memory Low]
FROM sys.dm_os_process_memory;

Esta otra consulta nos da un poco más de información, en ella vamos a poder ver el detalle de lo que está consumiendo la memoria RAM por parte de SQL Server:

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage,
   process_physical_memory_low AS sql_process_physical_memory_low,
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

Vista sys.dm_os_sys_memory

Esta vista de sistema nos va a dar menos variedad de información pero no por ello menos útil. A diferencia de la anterior aquí veremos los datos de memoria de nuestro servidor no solo de SQL Server. Podéis ver todas las columnas disponibles en la vista aquí, aunque yo os recomiendo esta consulta.

SELECT total_physical_memory_kb/1024 [Total Physical Memory in MB],
available_physical_memory_kb/1024 [Physical Memory Available in MB],
system_memory_state_desc
FROM sys.dm_os_sys_memory;

Vista sys.dm_os_sys_info;

Esta vista es muy interesante pues además de la memoria en uso por SQL Server nos va a mostrar una estimación de lo que SQL Server espera necesitar. Si combinado con las anteriores vemos que el valor necesitado es menor que el disponible podemos respirar tranquilos. Os dejo un ejemplo de uso sencillo:

SELECT committed_kb/1024 [SQL Server Committed Memory in MB],
committed_target_kb/1024 [SQL Server Target Committed Memory in MB]
FROM sys.dm_os_sys_info;

Vista sys.dm_os_performance_counters

Esta vista nos va a mostrar todos los contadores de rendimiento, incluidos los de SQL Server. Con ella podremos tener una visión más completa de lo que está pasando. Para el caso que nos ocupa os recomiendo filtrar por la palabra memoria:

SELECT object_name, counter_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name like '%Memory%'

Bonus 

Además de todas estas vistas de sistema, SQL pone a nuestra disposición el comando DBCC MEMORYSTATUS. Este comando nos va a dar mucha información (demasiada) sobre todo el estado de la memoria. Está diseñado para ser una herramienta de diagnóstico para los ingenieros de soporte de Microsoft por lo que, a nosotros, simples mortales, nos va a confundir más que otra cosa. Si tienes curiosidad sobre este comando puedes pasarte por su página en los books online de SQL Server dónde lo explican en detalle.

¿Qué hago si realmente hay un problema con la memoria?

Si llegados a este punto has detectado que si que tenias un problema real con el consumo de memoria RAM, te compadezco. Estás en una situación en la que a ninguno nos gustaría estar, pero no te preocupes, vivimos de esto y es lo que nos da dinero. Sigue leyendo que aún hay más artículo para que puedas salir airoso de este marronazo.

Lo primero que tendrás que determinar es si el problema está ocurriendo ahora mismo o ya ha pasado. Si estás en el primer caso puedes hacer uso de procedimientos almacenados como sp_who3, sp_whoisactive o sp_blitzwho para determinar el consumo de memoria de cada una de las consultas en ejecución y tirar de las orejas a alguien.

Si, como me pasa a mi, en tu país también está mal visto (y hasta penado por ley) el castigo físico, analiza las consultas que más memoria consumen y trata de optimizarlas. Quizá solo te falta un índice. O a lo mejor es al revés y te sobran. O están bien definidos pero sin mantenimiento. Te aseguro que con esto de los índices vas a resolver el 70% de las incidencias que te puedas encontrar. El resto de casos puede ser debido a ordenaciones sin miedo a lo que pueda pasar o al uso de operadores como UNION, DISTINCT, etc… que cargan el resultado en memoria para eliminar registros duplicados.

Si el problema ya ha pasado y no tienes tanta suerte de acceder a las consultas en ejecución, revisa los índices que te faltan y los que no se usan que es probable que con eso lo soluciones. Te dejo por aquí todo lo que se ha hablado en este blog sobre índices.

Conclusión

Afrontar una incidencia sobre el consumo de memoria en SQL Server puede ser un desafío. Espero que con estas herramientas de diagnóstico, nativas y de terceros, podáis enfrentaros mejor a ellas. Tampoco está de más ser proactivos y revisar de vez en cuando el uso de índices para ahorrarnos problemas. Un buen mantenimiento de índices y estadísticas nos evitará más de un disgusto. Recordad que un diagnóstico y una resolución rápida son fundamentales para mantener un entorno SQL Server saludable y eficiente. La superación de estos retos garantiza un funcionamiento fluido de la base de datos y contribuye a la eficacia general de la empresa, a la satisfacción del cliente y, en última instancia, a la cuenta de resultados.

Espero que este artículo te haya resultado útil e interesante. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.

CRÉDITOS: Varias de las consultas de este post son originales de Pinal Dave de SQLAuthority y del blog SQLHack.