TempDB Monitoring – Important SQL Queries

SELECT top 10
	transaction_id as [Transacton ID],
	transaction_sequence_num as [Transation Sequence Number],
	elapsed_time_seconds as [Elapsed Time (in sec)]
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC

select
	transaction_id as [Transacton ID],
	[name]      as [Transaction Name],
	transaction_begin_time as [Transaction Begin Time],
	datediff(mi, transaction_begin_time, getdate()) as [Elapsed Time (in Min)],
	case transaction_type
		when 1 then 'Read/write'
      	when 2 then 'Read-only'
      	when 3 then 'System'
      	when 4 then 'Distributed'
	end as [Transaction Type],
	case transaction_state
		when 0 then 'The transaction has not been completely initialized yet.'
		when 1 then 'The transaction has been initialized but has not started.'
		when 2 then 'The transaction is active.'
		when 3 then 'The transaction has ended. This is used for read-only transactions.'
		when 4 then 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
		when 5 then 'The transaction is in a prepared state and waiting resolution.'
		when 6 then 'The transaction has been committed.'
		when 7 then 'The transaction is being rolled back.'
		when 8 then 'The transaction has been rolled back.'
	end as [Transaction Description]
from sys.dm_tran_active_transactions

select
	host_name 			as [System Name],
	program_name 			as [Application Name],
	DB_NAME(database_id)    	as [Database Name],
	User_Name(user_id)            	as [User Name],
	connection_id 			as [Connection ID],
	sys.dm_exec_requests.session_id as [Current Session ID],
	blocking_session_id 		as [Blocking Session ID],
	start_time 			as [Request Start Time],
	sys.dm_exec_requests.status 	as [Status],
	command                         as [Command Type],
	(select text from sys.dm_exec_sql_text(sql_handle)) AS [Query Text],
	wait_type 			as [Waiting Type],
	wait_time 			as [Waiting Duration],
	wait_resource 			as [Waiting for Resource],
	sys.dm_exec_requests.transaction_id as [Transaction ID],
	percent_complete 		as [Percent Completed],
	estimated_completion_time 	as [Estimated Completion Time (in mili sec)],
	sys.dm_exec_requests.cpu_time 	as [CPU time used (in mili sec)],
	(memory_usage *  8   )            	as [Memory Usage (in KB)],
	sys.dm_exec_requests.total_elapsed_time as [Elapsed Time (in mili sec)]
from sys.dm_exec_requests
		inner join
	sys.dm_exec_sessions
		on sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
where DB_NAME(database_id) = 'tempdb'