Script for TempDB Sessions, The Queries, and (Nearly) Everything

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;

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s