SQL SERVER – How to Identifying TempDB is growing abnormally in SQL SERVER


Recently I have observed that the size of the TempDB is growing rapidly then I tried to identify which queries can grow TempDB. Tempdb is used all of the time by SQL Server. Essentially all transactions (including Database Maintenance) and analytical processing are processed via tempdb. I executed the below query to identify which are all the queries from the cache which are actually filling up space in the TempDB. After execution of below query my team immediately identified one of their most frequently executed query from the list generated by the following query.


SELECT su.session_id,
SUM(internal_objects_alloc_page_count) * 1.0 / 128 AS [internal object MB],
SUM(internal_objects_dealloc_page_count) * 1.0 / 128 AS [internal object dealloc MB],
txt.text
FROM sys.dm_db_task_space_usage su WITH (NOLOCK)
INNER JOIN sys.dm_exec_requests rq WITH (NOLOCK)
ON su.session_id = rq.session_id
AND su.request_id = rq.request_id
OUTER APPLY sys.dm_exec_sql_text(rq.sql_handle) AS txt
WHERE txt.text IS NOT NULL AND su.session_id <> @@SPID
GROUP BY su.session_id, txt.text
ORDER BY [internal object MB] DESC;

 

When you will execute the above query then it will provides you details from the cache about queries growing in TempDB. After executing of above query you will get the output like below:-

Identifying TempDB is growing abnormally in SQL SERVER

 


 

Leave a Reply

Your email address will not be published. Required fields are marked *