Consumo excesivo de memoria de SQL Server
«Mi SQL consume mucha RAM». Esta es seguramente una de las frases que más hemos oído en los últimos meses. La experiencia nos ha demostrado que SQL Server, el sistema para la gestión de la base de datos de Microsoft, tiene un método de funcionamiento bastante poco pragmático en lo que al uso de memoria se refiere. Veamos como reducir el consumo excesivo de memoria de SQL.
¿Cuánta memoria necesito?
Pongamos por ejemplo que un servidor tiene 10GB de memoria física RAM y poseé una instancia de Microsoft SQL Server 2008 instalada con 10 Bases de Datos (BBDD). La pregunta inmediata es: ¿Cuánta memoria RAM va a necesitar SQL?
Mediante una consulta SQL, podemos, por ejemplo, ver el consumo de memoria RAM del total de las Bases de Datos:
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE ‘%Buffer Manager’
AND counter_name = ‘Total Pages’;
;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
–WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN ‘Resource DB’
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
En función de qué producto sea el front-end de esas BBDD (un SharePoint Server, pongamos por caso), las recomendaciones varían. Pero esas instrucciones son de fabricante y realmente no se centran en todas las variables que habrá en un entorno de producción. Es decir, supongamos que la recomendación de Microsoft para SharePoint es que SQL debe tener un mínimo de 6GB de memoria RAM disponibles. Esa capacidad podría fluctuar atendiendo a otros factores como el número de usuarios, el tamaño de la Base de Datos, el número de usuarios concurrentes, etc.
Liberar al servidor
SQL puede configurarse para que utilice sólo una cantidad limitada de memoria RAM. De lo contrario, consumirá el máximo. Esta es la práctica habitual después de investigar el servidor y cerciorarse de que de los 10GB de RAM de los que dispone, SQL Server consume 9GB. Para evitar el colapso del servidor y que tenga un poco de aire, cualquiera limitaría la memoria para SQL en, por ejemplo, 6 GB. Y si echa un vistazo al rendimiento verá que obedece.
El consumo óptimo
Aunque la solución anterior puede sacarnos del apuro, a nadie se le escapa que lo ideal sería hallar la manera de conocer con exactitud qué memoria consume cada Base de Datos (BBDD) para así determinar qué consumo necesita la instancia SQL Server. Fácil. Basta con realizar una consulta SQL para verlo.
Con el resultado que obtenemos de observar el total de memoria RAM, podemos sumar el total de ‘db_buffer_MB’ y observar el total de memoria RAM que consume SQL. Retomando el ejemplo anterior, si rebajamos el límite de SQL a 9GB, si ejecutamos la consulta vemos que las BBDD solamente consumen 3GB de memoria RAM.
Es decir, se confirma la tesis inicial del consumo excesivo de memoria de SQL Server. Aunque las BBDD consumen únicamente 3GB, si limitamos la instancia a 6GB, SQL Server se as ingenia para hacer uso de la totalidad de esos 6GB. Estaríamos hablando de una auto-reserva de memoria.
Aplicación real
Lo importante de este resultado es su aplicación práctica. Dado que que SQL necesita sólo 3GB para funcionar correctamente, ¿podríamos llegar a limitar a 4GB su uso de memoria RAM y no desperdiciarla toda? Sí.
Si realizamos una nueva query (ver imagen) podemos ver el consumo concreto de una base de datos. Y si esta consulta la ejecutamos de forma regular y tomamos nota de la media, podemos observar si el consumo fluctúa o no.
COUNT (*) * 8 / 1024 AS MB_EN_USO
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
HAVING DB_NAME (database_id) LIKE ‘nombre_de_la_BBDD’
ORDER BY COUNT (*) * 8 / 1024 DESC
Claro, para tener estas medias anotadas y poder sacar conclusiones luego, es necesario tener un histórico que almacene toda esa información. Con él sabremos por ejemplo si el total de las BBDD en una semana no consumen más de 4GB.
En cuanto nos hagamos con una imagen clara del consumo de memoria de cada BBDD, sacando la media prudente (unos cuantos días, semanas), podemos asegurarnos de qué picos están por encima y por debajo de su «normalidad».
En algunos casos, el consumo puede fluctuar bastante y llegar a crecer hasta los 5GB, pero lo importante es que el histórico nos proporcionará una información muy valiosa a la hora de limitar los consumos de memoria de forma segura y sin necesidad de colapsar el servidor sin motivo aparente.
La importancia de la monitorización
La importancia de la monitorización se hace patente en casos como el que nos ocupa. Si tenemos un histórico del consumo de memoria RAM que hace SQL Server, es posible extraer una media razonable y después limitar su uso.
La monitorización nos permite establecer un baremo que determina unos máximos y mínimos. Si se sobrepasan en cualquiera de los dos casos, si ocupa más memoria de la deseada, tanto el cliente como el técnico recibirán un correo de alarma.
Así, mediante una query, podemos llamar al resultado final de un plan de mantenimiento. Siendo 1 positivo y 0 negativo.
spl.succeeded AS [Succeeded]
FROM msdb.dbo.sysmaintplan_plans AS s
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id
WHERE s.name=’nombre_del_plan_de_mantenimiento’
ORDER BY spl.end_time DESC;
En el ejemplo con el que hemos estado trabajando, el resultado es -1. Esto es una simple interpretación del sistema de monitorización, pero se traduce en que -1 es positivo y 0 negativo. E igual que en la situación anterior, si obtenemos un resultado negativo en su seguimiento, nos llegará una alarma de inmediato.
En resumen…
¿Qué hemos explicado en este artículo?
- Que SQL Server hace un consumo excesivo de memoria, y que necesita que le ayudemos y adaptemos a nuestras necesidades
- Que las BBDD realizan un consumo de memoria concreto, y podemos obtener esa información para trabajar con ella
- Que los Planes de Mantenimiento tienen un resultado que se debe revisar muy regularmente
- Que la monitorización es imprescindible para –por lo menos- tenerlo todo en la misma pantalla, automatizado, y con toda esta información, anticiparnos a muchos problemas
Si necesitas formación para mejorar la gestión de SQL, desde AWERTY podemos ofrecértela. Si deseas contratarla, o que te demos más información a este respecto, sólo tienes que ponerte en contacto con nosotros. Sin compromiso. Estaremos encantados de darte una respuesta. Además, a través de nuestro departamento de Infraestructura Cloud, podemos ofrecerte múltiples servicios y soluciones diseñados para mejorar los sistemas de tu empresa, asistirte en la migración al Cloud y multiplicar la seguridad de tu negocio.
15/12/2015 @ 4:36 pm
Encuentro el articulo interesante pero me queda una duda. tu consulta de consumo de ram parece que excluye el consumo de las dbs del sistema. tengo un caso en el que la que mas memoria toma es tempdb (3GB). no se si esto habría que tenerlo en cuenta para el calculo de memoria, o es que es un error que la tempdb use tanta memoria