By Richie Lee

Last month I posted a view that looked at TempDB usage. I use it primarily as a monitor for TempDB usage on instances that have databases that have Read Committed Snapshot isolation enabled.

Recently I made a change to the view; it includes the snapshot isolation level of the session. If it is using snapshots then would expect TempDB to be greater for this session/task.

if exists (select * from sys.sysobjects where name = 'vw_all_temp_db_usage')
drop view vw_all_temp_db_usage
GO
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,
 CASE s.transaction_isolation_level
 WHEN 0 THEN 'Unspecified'
 WHEN 1 THEN 'ReadUncomitted'
 WHEN 2 THEN 'ReadCommitted'
 WHEN 3 THEN 'Repeatable'
 WHEN 4 THEN 'Serializable'
 WHEN 5 THEN 'Snapshot'
 END
 AS [transaction Isolation Level],
 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

--select * from all_temp_db_usage

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


select * from vw_all_temp_db_usage