I’ve been sifting through a few of my older sql files on my hard drive and found this one I wrote a few years back. For each connected session to the SQL instance this script shows the session id’s pages allocated in tempDB and the queries that were last run.
There’s quite a lot of variations of this script available on the web. but I like this one because it does not go into too much detail, but is rather useful for quick troublshooting.
SELECT u.session_id AS [spid], DB_NAME (s.database_id) AS [database], s.login_name AS [login], s.host_name[origin], u.internal_objects_alloc_page_count/128.0 AS [allocated tempDb Session Object Space], u.user_objects_alloc_page_count/128.0 AS [ allocated tempDb Task User Space], u.user_objects_dealloc_page_count AS [deallocated tempDb Task User Space], tsu.internal_objects_alloc_page_count/128.0 AS [tempDb Task Object Space], tsu.user_objects_alloc_page_count/128.0 AS [tempDb Task User Space], tsu.user_objects_dealloc_page_count AS [deallocated Task tempDb User Space], tsk.task_state AS [state], t.text FROM sys.dm_db_session_space_usage u INNER JOIN sys.dm_exec_sessions s ON u.session_id = s.session_id INNER JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id INNER JOIN sys.dm_exec_connections c ON c.session_id = s.session_id INNER JOIN sys.dm_os_tasks tsk on tsk.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) t ORDER BY u.session_id;