Script to find TEMP use by SPID in SQL Server
The script will list the temporary space use by SPID in SQL Server. This script is great to identify query's that consume allot of your temp files.
USE tempdb
Go
SELECT t.text,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count,
st.session_id
FROM sys.dm_db_task_space_usage st
JOIN sys.sysprocesses sp
ON sp.spid = st.session_id
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) t
GROUP BY t.text,st.session_id
ORDER BY 2 DESC
