How to find which query is causing tempdb growing? [duplicate]

Question

Good day. I'm trying to discover which query is causing tempdb growing too much (around 100gbyte). In my new company, people is using a lot of dynamic Excel files with various query. I'm pretty sure that one of them is "guilty" for tempdb growing. The only solution I've discovered is to restart sql server service (it delete and recreate tempdb). Is there a way to discover which query is allocating so much space ? Thanks

Answer 1

--Lists the TempDB usage per each active session.
--It helps identifying the sessions that use the tempdb heavily with internal objects. 

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                   EST.text, 
                   ERQ.statement_start_offset / 2, 
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
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
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC

On a TEST SERVER you can start this query

--USE TempDB;
--GO
--SELECT TOP 1000000000
        IDENTITY(INT,1,1) AS RowNum
   INTO #StressTempDB
   FROM Master.sys.All_Columns ac1,
        Master.sys.All_Columns ac2,
        Master.sys.All_Columns ac3;
GO

and then start the above script to see which sessions are causing tempDB growth.