SQL SERVER – Identifying Query Growing TempDB

During the recent Comprehensive Database Performance Health Check we observed that the size of the TempDB is very big for our customer. The first question which they asked was if there is any way they can identify query growing TempDB.

We ran the following query to identify which are all the queries from the cache which are actually filling up space in the TempDB. The customer immediately identified one of their most frequently ran the query from the list generated by the following query.

SELECT TSU.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],
EST.text
FROM sys.dm_db_task_space_usage TSU WITH (NOLOCK)
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
WHERE EST.text IS NOT NULL AND TSU.session_id <> @@SPID
GROUP BY TSU.session_id, EST.text
ORDER BY [internal object MB] DESC;

When you run above query, it will give you details about query growing TempDB which from the cache. If any query which is currently running or removed from the cache will be not part of the result of this query.

Once at the customer place we identified the query which is taking too many resources from the TempDB, we eventually tuned the query to use next to nothing TempDB resources and eventually improved performance of the server multifold.

I suggest you run the above query on your TempDB and update all of us, with a number of queries which are using TempDB very heavily.

Please post your queries in the comments section of this blog post.

Reference: Pinal Dave (https://blog.SQLAuthority.com)