TempDB Monitoring – Important SQL Queries

0
76

Here are few important TSQL queries to get the long running transactions and queries in TempDB for monitoring purposes.

  • Long-running transaction:

Sometimes transactions may run for long time preventing the shrinking of version store data.
The below queries will list the transactions that are running for long time.

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

A long running transaction may prevent cleanup of transaction log thus eating up all log space available resulting space crisis for all other applications.

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
  • Long running Queries:

sys.dm_exec_requests : Returns information regarding the requests made to the database server.

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'

Status: Status of the request.

This can be from one of the following options:
Background
Running
Runnable
Sleeping
Suspended

Command: Identifies the current type of command that is being processed.

Common command types include the following:
SELECT
INSERT
UPDATE
DELETE
BACKUP
LOG
BACKUP
DB DBCC
WAITFOR

Thanks,
Web Editor

LEAVE A REPLY

Please enter your comment!
Please enter your name here

2 × 5 =