Script that will help in Identifying query causing TempDB growth

I am not creating any indexes assuming we know how to create them

CREATE TABLE [dbo].[tempUsage](

[Date_Time] [datetime] NOT NULL,

[session_id] [smallint] NULL,

[sql_handle] [varbinary](64) NULL,

[SQL_Text] [varchar](8000) NULL,

[request_internal_objects_alloc_page_count] [bigint] NULL,

[request_internal_objects_dealloc_page_count] [bigint] NULL

) ON [PRIMARY]



GO



CREATE VIEW all_request_usage

AS

SELECT session_id, request_id,

SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,

SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count

FROM sys.dm_db_task_space_usage

GROUP BY session_id, request_id;



GO



CREATE VIEW all_query_usage

AS

SELECT R1.session_id, R1.request_id,

R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,

R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle

FROM all_request_usage R1

INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;



GO



Let’s run the statement every minute to capture SQL Statement that is causing TempDB growth.

{

INSERT INTO tempUsage

SELECT

GetDate() As Date_Time,

R1.session_id,

R1.sql_handle,

Cast ( R2.text As Varchar(8000)) SQL_Text,

R1.request_internal_objects_alloc_page_count,

R1.request_internal_objects_dealloc_page_count

FROM all_query_usage AS R1

OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2

where R1.request_internal_objects_alloc_page_count <> 0;

}