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

At the beginning of last month I posted an old query that showed the usage of TempDB. Since then I’ve modified it and added the query plan and have created a view out of the query.

I find using this view has helped me since Read Committed Snapshot isolation (RCSI) was turned on for a few of our databases as TempDB usage increases when RCSI is enabled.

CREATE VIEW vw_all_temp_db_usage
AS
 SELECT
 u.session_id
 AS [spid],
 DB_NAME (s.database_id)
 AS [database],
 s.original_login_name
 AS [login],
 s.host_name
 AS [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/128.0
 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/128.0
 AS [deallocated Task tempDb User Space],
 tsk.task_state
 AS [state],
 t.text,
 qp.query_plan
 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
 INNER JOIN sys.dm_exec_requests req
 on u.session_id = req.session_id
 CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) t
 CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) qp
GO

Once you have run the above SQL you can execute as follows:


select * from vw_all_temp_db_usage

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