By Richie Lee

Hello,

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;