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. Pokémon Nut. 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