TempDB is an important system database in SQL Server that is used for various purposes, such as storing temporary objects, sorting and joining data, and managing version store for concurrency control. However, if the size of TempDB grows excessively, it can lead to performance issues and disk space problems.

In order to identify the queries that are causing TempDB to grow, you can use 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;

This query retrieves information from the system views to identify the queries that are using the most space in TempDB. It provides details such as the session ID, the amount of space used by internal objects (in megabytes), and the text of the query.

By running this query, you can quickly identify the queries that are causing TempDB to grow. Once you have identified the problematic queries, you can then focus on optimizing them to reduce their impact on TempDB.

For example, if you find a frequently executed query that is using a significant amount of TempDB resources, you can analyze its execution plan, optimize the query, or consider alternative approaches to achieve the desired result with less TempDB usage.

Optimizing queries that heavily use TempDB can have a significant impact on the overall performance of your SQL Server. By reducing the resource consumption in TempDB, you can improve the response time of queries, reduce contention, and ensure efficient utilization of system resources.

If you are experiencing issues with TempDB size or performance, I encourage you to run the above query on your SQL Server and share the queries that are using TempDB heavily in the comments section below. Together, we can discuss and find solutions to optimize those queries and improve the performance of your SQL Server.

Remember, monitoring and optimizing TempDB usage is an important aspect of SQL Server performance tuning. By proactively identifying and addressing queries that are growing TempDB, you can ensure the smooth operation of your SQL Server environment.