Hace un par de semanas, os preparé un artículo sobre la TempDB en el que pudimos ver, qué es, para qué sirve, por qué se llena y cómo optimizarla. Sin embargo, nos dejamos una cosa en el tintero y es algo que muchos me habéis señalado. Aunque hablamos también de cómo vaciar la TempDB no profundizamos en cómo saber qué consultas son las causantes del llenado de la TempDB. Hoy vamos a ver justo eso, y para ello nos vamos a apoyar en una herramienta de la que también os he hablado, los eventos extendidos de SQL Server.
Un pequeño repaso de la TempDB
No es el objetivo de este artículo profundizar en la teoría de la TempDB, para eso tenemos el artículo del pasado 13/02/2024. Pero, si que tenemos que repasar algunos de esos conceptos para no perdernos ahora. La TempDB es la base de datos de sistema con ID número 2 que se crea cada vez que se inicia el servicio de SQL Server y se elimina al pararlo.
En esta base de datos se almacenan datos de intercambio de las consultas en curso ya sean datos de paginación de memoria para ordenar u operar con registros como datos almacenados por petición de los usuarios en tablas temporales (de una sesión o globales). Estas funcionalidades son las que hacen esta base de datos susceptible de crecer muy rápido e incluso de terminar llenándose completamente. Como comprenderás, llenar completamente una base de datos que se usa para prácticamente todo puede tener consecuencias fatales, llegando a dejar inoperativo nuestro servidor.
Otro pequeño repaso a xEevents
Como ya vimos hace un par de días, los eventos extendidos son una poderosa herramienta de SQL Server para los DBAs. No solo nos van a ayudar, como hoy, a detectar errores en la TempDB, sino también a monitorizar completamente el estado de nuestro servidor. Sin ir más lejos, ayer pudimos ver como usar xEvents para localizar consultas con mucho consumo de CPU.
Errores de TempDB
Para nuestro ejercicio de hoy vamos a partir de la base de los errores de falta de espacio en el archivo de datos de SQL Server y vamos a crear una sesión de xEvents que nos almacene la consulta que los ha provocado cuando se den en la base de datos TempDB. Estos errores son el 1101 y el 1105
Error 1101 - Could not allocate a new page for database 'tempdb' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.Error 1105 - Could not allocate space for object '%.*ls'%.*ls in database 'tempdb' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.Como podéis ver, ambos errores son muy parecidos y la solución para ambos es la misma: revisar que el crecimiento sea el correcto, añadir espacio para que pueda crecer el fichero o liberar espacio en el fichero.
Sesión de xEvents
Nosotros nos vamos a centrar hoy en detectar las consultas causantes del problema para poder trabajar sobre ellas y no necesitar tanto espacio en TempDB. Para ello, vamos a hacer uso de una sesión de xEvents.
No me lio más, vamos a ponernos manos a la obra. Para nuestra sesión de XE vamos a capturar los eventos sqlserver.error_reported. Cómo vimos en el artículo de los eventos extendidos, vamos a poder usar las acciones para capturar información extra. En este caso vamos a capturar la base de datos y toda la información relevante sobre la consulta que ha disparado el error. Con esto, estaríamos capturando todas las consultas que generen un error en SQL Server pero como lo que nosotros queremos son solo los errores de espacio en TempDB vamos a hacer uso de los filtros. En concreto vamos a crear 3 filtros, el primero será que la base de datos sea la de id número 2 que ya hemos visto que es la que nos interesa, crearemos también un filtro por los errores 1101 y 1105 que nos interesan y terminaremos con un filtro por id de sesión superior a 50 para quitarnos las consultas del sistema de la captura. El resto de configuraciones son las genéricas que ya vimos.
Este sería el script de nuestra sesión de xEvents:
CREATE EVENT SESSION [TempDB_error] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.session_nt_username,sqlserver.plan_handle)
WHERE ([database_id] = (2) AND [session_id] > (50) AND ([error_number]=(1101) OR [error_number]=(1105))))
ADD TARGET package0.event_file(SET filename=N’TempDB_error.xel’,max_file_size=(100),max_rollover_files=(10)),
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
Revisando los resultados de xEvents:
Una vez creada e inicializada nuestra sesión podremos ver los datos en tiempo real desde la interfaz gráfica de nuestro SSMS:
Otra opción será consultar el fichero de la sesión con una consulta T-SQL.
SELECT TOP 100 * FROM (
SELECT DISTINCT
n.value('@name', 'VARCHAR(100)') AS EventName,
n.value('@timestamp', 'DATETIME') AS Timestamp,
n.value('(data[@name="error_number"]/value)[1]', 'INT') AS ErrorNumber,
n.value('(data[@name="severity"]/value)[1]', 'INT') AS Severity,
n.value('(data[@name="state"]/value)[1]', 'INT') AS State,
n.value('(data[@name="user_defined"]/value)[1]', 'BIT') AS UserDefined,
n.value('(data[@name="category"]/text)[1]', 'VARCHAR(100)') AS Category,
n.value('(data[@name="destination"]/text)[1]', 'VARCHAR(100)') AS Destination,
n.value('(data[@name="is_intercepted"]/value)[1]', 'BIT') AS IsIntercepted,
n.value('(data[@name="message"]/value)[1]', 'VARCHAR(MAX)') AS ErrorMessage,
n.value('(action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS SqlText,
n.value('(action[@name="session_id"]/value)[1]', 'INT') AS SessionID,
n.value('(action[@name="database_id"]/value)[1]', 'INT') AS DatabaseID,
n.value('(action[@name="session_nt_username"]/value)[1]', 'VARCHAR(MAX)') AS UserName,
n.value('(action[@name="plan_handle"]/value)[1]', 'VARCHAR(4000)') AS PlanHandle
FROM (select cast(event_data as XML) as event_data
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\TempDB_error*.xel', null, null, null)) ed
CROSS APPLY ed.event_data.nodes('event') as q(n)
) t
WHERE EventName = 'error_reported'
ORDER BY TimeStamp DESCEn cualquiera de los casos podremos ver todas las consultas que han dado error por falta de espacio en TempDB y cómo lo vamos a ver ordenado por fecha solo nos tendremos que ir a la primera para descubrir al causante de la incidencia y poder reeducarle (siempre respetando los derechos humanos, ya sabéis).
Conclusión
Enfrentarse a un problema de consumo de TempDB puede ser un verdadero dolor de cabeza (por no decir otra parte de la anatomía masculina) pero, con las herramientas adecuadas podremos afrontarlo de manera sencilla. Para luego es tarde, no esperes a tener un problema en TempDB y echar de menos esta sesión de xEvents, ponla en tu servidor de pruebas (puedes jugar con el tamaño máximo del fichero de TempDB para reproducir el error) y, si te convence, aplicalo en tus servidores más críticos.
Esperamos que este artículo os haya sido útil y os animamos a seguir aprendiendo sobre SQL Server con nosotros. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir. ¡Hasta la próxima!
