Un recurso muy valioso para cualquier motor de base de datos es la memoria. Esta memoria se utilizará con muchos fines, destacando especialmente el cacheo de páginas/segmentos de datos, así como planes de ejecución. También se necesitará memoria para la propia ejecución de las consultas, para mantener temporalmente los buffers necesarios para el procesamiento de los datos, para su ordenación, para almacenar tablas hash, etc. Desgraciadamente, no existe un punto único desde donde podamos determinar y diagnosticar todos los consumos de memoria independientemente de su naturaleza, por lo que al final tendremos que ir analizando desde un punto de vista más alto hasta uno más bajo para poder poner el dedo exactamente en la llaga que origina el dolor al servidor.
Caso de memoria en SQL Server
Recientemente nos encontramos un caso curioso donde el “vampiro de memoria” no era uno de los casos más habituales y nos costó encontrar la causa raíz del abultado consumo de memoria. En este post vamos a exponer los pasos que seguimos para encontrar el problema (forzando su simulación en nuestro equipo) de forma que pueda ayudar a diagnosticar casos similares.
Los síntomas que nos encontramos fueron de un alto consumo de memoria y de disco (aunque éste no era tan aparente en el caso real, solo el consumo de memoria).
Desde el punto de vista del operativo la utilidad RamMap, bastante útil cuando queremos conocer el desglose de uso de memoria de un servidor, no nos proporcionaba información, lo que nos podría llevar a pensar que se trata de algún problema a nivel de OS/HW:
Visto que el mayor consumo de memoria ocurría en el proceso de SQL Server, una de las primeras cosas que solemos revisar es si se encuentra la memoria de la instancia limitada. En este caso, se encontraba sin limitar, lo cual puede ser problemático en muchos escenarios:
También en ocasiones pueden existir leaks, por ejemplo en drivers de linked servers, o por algún bug en la gestión de cachés internas de SQL Server (como la USERSTORE_TOKENPERM que se hizo “famosa” por un bug en SQL 2005) que pueden provocar aumentos incontrolados de la memoria. Sin embargo, en la mayor parte de los casos el consumo de memoria está justificado en base al uso que hacemos de la base de datos. Precisamente son aquellos casos de usos menos habituales los que suelen esconder sorpresas como ocurrió en este caso.
Tras conectarnos a la instancia cuando estaba sufriendo los problemas de memoria comenzamos echando una ojeada a la memoria usada en las consultas en ejecución sin obtener ningún dato relevante. Hemos marcado en amarillo la sesión 55 ya que es la que realmente está causando el problema, de forma que podamos ver claramente que nada parece indicarlo en el resultado de esta consulta:
-- El uso de memoria de las sesiones no dice nada significativo select memory_usage,session_id from sys.dm_exec_sessions order by memory_usage desc
Si consultamos el monitor de actividad obtenemos este mismo valor convertido a KB por lo que tampoco nos sirve como pista:
Una consulta a las entradas en la caché tampoco revela nada que tenga un volumen elevado y tampoco si agrupáramos por tipo tampoco encontramos nada sospechoso:
-- DMV de entradas en caché tampoco dice nada... select pages_kb, * from sys.dm_os_memory_cache_entries order by 1 desc -- DMV de entradas en caché agrupadas tampoco dice nada... select sum(pages_kb), type from sys.dm_os_memory_cache_entries group by type order by sum(pages_kb) desc
Revisamos también la dmv interna sys.dm_os_memory_allocations (tras la activación del trace flag 3654) por si pudiéramos obtener alguna pista en base a las peticiones a nivel de OS. Sin embargo, tampoco nos da ninguna pista:
-- No nos dicen nada las memory allocations... dbcc traceon(3654,-1) select * from sys.dm_os_memory_allocations order by size_in_bytes desc
No podía faltar un clásico, el DBCC MemoryStatus de donde sacamos alguna pista referente a que el consumo exagerado de memoria se producía en el buffer pool exclusivamente. A la izquierda podemos ver los valores que tenemos en el sistema antes de ejecutar el spid 55 y a la derecha después de ponerlo en marcha:
-- Vemos que los aumentos de uso de memoria vienen en el buffer pool, ya es una pista... dbcc memorystatus
Una vez tenemos este uso de buffer pool elevado, investigamos un poco más y vemos que tenemos un gran uso de memoria asignada a base de datos tempdb, algo relativamente anómalo:
-- Contamos cuantos buffer descriptors tenemos por base de datos, saliendo tempdb como la que parece desbocada con 1.5 millones de páginas que son aproximadamente 12 GB select database_id, count(*) total from sys.dm_os_buffer_descriptors group by database_id order by count(*) desc
Una vez que sospechamos de algo de tempdb revisamos la actividad de disco y efectivamente se confirmaba que es mayoritariamente de escritura y sobre los ficheros de datos de tempdb:
Desde el punto de vista de tareas en esperas tenemos esperas pageiolatch_ex sobre páginas de tempdb:
-- Waiting tasks dice que tenemos pageiolatch_ex de tempdb (dbid:2) select * from sys.dm_os_waiting_tasks where session_id > 50 order by wait_duration_ms desc
Llegados a este punto, como hipótesis, cuando algo consume mucha memoria del buffer pool, llena el buffer pool de páginas de tempdb y escribe en los ficheros de tempdb de forma masiva suelen ser un caso de tablas temporales enormes. Sin embargo vemos que no tenemos ninguna tabla temporal en la instancia en ese momento:
El siguiente paso es mirar las DMV´s de tempdb para ver si hay algo extraño que nos llame la atención y vemos la sesión 55 con un alto número de objetos internal:
select session_id, user_objects_alloc_page_count,internal_objects_alloc_page_count from sys.dm_db_task_space_usage order by user_objects_alloc_page_count+internal_objects_alloc_page_count desc
Dentro de internal objects se incluye la memoria utilizada por workfiles, worktables y spills de tipo hash/sort así que el siguiente paso es monitorizar este tipo de artefactos. Desde performance monitor encontramos que el ratio de workfiles y worktables creados por segundo era nulo:
Desde profiler intentamos capturar eventos asociados a los spills y tampoco capturamos absolutamente nada significativo:
Si consutamos la DMV de allocations (sustituto del DBCC IND) tampoco nos dice nada significativo al tratarse de objetos internos, ya que solo nos devuelve unas pocas filas, imposible que esté ahi el grueso del consumo:
select count(*) from sys.dm_db_database_page_allocations(2,null,null,null,'detailed')
Tampoco la DMV interna (system_internals_allocations_units) que en teoría debería mostrarnos el problema con algo más de detalle nos muestra nada en absoluto, lo cual ya resulta frustrante:
SELECT * from tempdb.sys.system_internals_allocation_units AU order by total_pages desc
Llegados a este punto analizamos las ejecuciones de consultas generadas por la sesión con session_id 55. Una vez analizadas el tipo de consultas y operaciones que se realizaban, probamos a reproducir en nuestro equipo la potencial problemática con un script como el siguiente:
declare @a varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a1 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a2 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a3 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a4 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a5 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a6 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a7 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a8 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a9 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a10 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a11 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a12 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a13 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a14 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a15 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a16 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a17 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a18 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a19 varchar(max)=replicate(convert(varchar(max),'a'),2000000000) declare @a20 varchar(max)=replicate(convert(varchar(max),'a'),2000000000)
Básicamente lo que estamos forzando es la creación de variables de tipo varchar(max), que forman parte de los tipos LOB, e inicializarlas con un valor bastante grande cada una de ellas. Estas variables se volcarán en tempdb como internal objects debido a su tipo LOB y harán crecer tanto el buffer pool como el espacio usado en tempdb. De hecho, creemos que podría utilizarse esta técnica de forma sencilla (no requiere permisos especiales, cualquier usuario que tenga permisos de conexión puede lanzarla) para realizar un ataque de denegación/degradación de servicio a SQL Server.
Una posible situación en la que nos podamos encontrar con esta situación es si utilizamos variables de tipo LOB, de tipo texto o binario, como parámetros de un componente CLR para enviar los datos de un fichero de cierto tamaño para su procesamiento/manipulación (compresión, conversión de tipos de ficheros, OCR, etc.). Podemos también caer en esta situación si leemos con OPENROWSET BULK un fichero grande y lo cargamos en una variable para realizar inserciones posteriormente. En este tipo de situaciones no se produce un “streaming” del dato sino que se usa memoria del buffer pool ocasionando los problemas anteriores.
En resumen, que se puedan manejar datos de hasta 2 GB en una variable no significa que sea eficiente realizarlo de esta forma, y menos si lo hacemos de forma habitual y con concurrencia. En casos donde se requieran estos volúmenes de datos es preferible utilizar otras técnicas, como por ejemplo, filestream que sí proporciona ese funcionamiento orientado a streaming para no “contaminar” el buffer pool con los accesos a los ficheros.
Si os ha gustado nuestro post sobre memoria en SQL Server, desde SolidQ os invitamos a descubrir más visitando nuestro blog o a suscribiros a nuestra newsletter para recibir las últimas novedades del sector en vuestro correo 🙂
