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:-
