Monitorizar el tamaño de la base de datos temporal, tempDB, es una de las tareas que todo DBA debe enfrentarse. Antes o después, nos encontraremos con un gran problema. El tamaño de la base de datos tempDB no para de aumentar y es muy grande.
¿Qué es la tempdb?
La base de datos tempDB es una base de datos de sistema de SQL Server. Monitorizar el tamaño de la base de datos temporal es una labor necesaria, ya que su uso es muy amplio. Desde ordenaciones, hasta creación de tablas temporales. Normalmente, cuando acaba una sesión que usa la tempDB, el espacio ocupado en la misma se libera. Si esta sesión no para de crecer, tenemos un problema. Deberemos matar esta sesión para liberar espacio en la tempDB. Dicho así, suena muy fácil, pero lo difícil es encontrar esta sesión. Veamos cómo.
¿Cómo monitorizar el tamaño de la base de datos temporal?
Para monitorizar el tamaño de la tempDB podemos usar la siguiente query:
SELECT COALESCE(T1.session_id, T2.session_id) [session_id] , T1.request_id ,
COALESCE(T1.database_id, T2.database_id) [database_id],
COALESCE(T1.[Total Allocation User Objects], 0)
+ T2.[Total Allocation User Objects] [Total Allocation User Objects] ,
COALESCE(T1.[Net Allocation User Objects], 0)
+ T2.[Net Allocation User Objects] [Net Allocation User Objects] ,
COALESCE(T1.[Total Allocation Internal Objects], 0)
+ T2.[Total Allocation Internal Objects] [Total Allocation Internal Objects] ,
COALESCE(T1.[Net Allocation Internal Objects], 0)
+ T2.[Net Allocation Internal Objects] [Net Allocation Internal Objects] ,
COALESCE(T1.[Total Allocation], 0) + T2.[Total Allocation] [Total Allocation] ,
COALESCE(T1.[Net Allocation], 0) + T2.[Net Allocation] [Net Allocation] ,
COALESCE(T1.[Query Text], T2.[Query Text]) [Query Text]
FROM ( SELECT TS.session_id ,
TS.request_id ,
TS.database_id ,
CAST(TS.user_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation User Objects] ,
CAST(( TS.user_objects_alloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation User Objects] ,
CAST(TS.internal_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation Internal Objects] ,
CAST(( TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation Internal Objects] ,
CAST(( TS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,
2)) [Total Allocation] ,
CAST(( TS.user_objects_alloc_page_count
+ TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation] ,
T.text [Query Text]
FROM sys.dm_db_task_space_usage TS
INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id
AND ER.session_id = TS.session_id
OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T
) T1
RIGHT JOIN ( SELECT SS.session_id ,
SS.database_id ,
CAST(SS.user_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation User Objects] ,
CAST(( SS.user_objects_alloc_page_count
- SS.user_objects_dealloc_page_count )
/ 128 AS DECIMAL(15, 2)) [Net Allocation User Objects] ,
CAST(SS.internal_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation Internal Objects] ,
CAST(( SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count )
/ 128 AS DECIMAL(15, 2)) [Net Allocation Internal Objects] ,
CAST(( SS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,
2)) [Total Allocation] ,
CAST(( SS.user_objects_alloc_page_count
+ SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count
- SS.user_objects_dealloc_page_count )
/ 128 AS DECIMAL(15, 2)) [Net Allocation] ,
T.text [Query Text]
FROM sys.dm_db_session_space_usage SS
LEFT JOIN sys.dm_exec_connections CN ON CN.session_id = SS.session_id
OUTER APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) T
) T2 ON T1.session_id = T2.session_id
order by [Total Allocation] desc
Esta query nos proporciona mucha información pero el campo que nos interesa es principalmente «Total Allocation«, el cuál nos indica el tamaño total que está usando la misma. Una vez localizada la sesión que más consume, podremos ver la query que está provocando ese llenado. Si queremos pararla, deberemos ejecutar kill seguido del id de sesión. Por ejemplo:
kill 84
Después de matar la sesión debería liberarse el espacio de la tempDB. Si disponemos de SSMS (SQL Server Management Studio) podemos ver más información de la sesión. A través del Monitor de Actividad o Activity Monitor, podremos ver el servidor o equipo de origen, la aplicación, si está bloqueado por alguna sesión y cuál es la sesión que lo bloquea.
Esperamos que esta query os haya sido de ayuda como lo ha sido para nosotros. Si aún así quieres que nos encarguemos de este tipo de tareas por ti, calcula tu presupuesto de DBA Remoto.
Nos vemos en próximas entradas.
Un saludo,
Equipo de base de datos.
Fuente: https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/
