LLI Wiki — My Digital Knowledge Base

SQL Server: find long-running transactions and requests

DMV queries to find the transactions that hold the log and the longest-running requests.

Queries to find the open transactions that hold the transaction log, and the requests that consume the most elapsed time. Reference articles on long-running query alerts are linked at the end.

Long transactions and log usage

SELECT b.session_id 'Session ID'
	, CAST(Db_name(a.database_id) AS VARCHAR(20)) 'Database Name'
	, c.command
	, Substring(st.TEXT, ( c.statement_start_offset / 2 ) + 1,
		((
		 CASE c.statement_end_offset 
		wHEN -1 THEN Datalength(st.TEXT) 
		ELSE c.statement_end_offset 
	END
	-
	c.statement_start_offset ) / 2 ) + 1)  statement_text
	, Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename( 
	  Object_schema_name(st.objectid, 
						 st.dbid)) + 
						 N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')  command_text
	, c.wait_type
	, c.wait_time
	, a.database_transaction_log_bytes_used / 1024.0 / 1024.0  'MB used'
	, a.database_transaction_log_bytes_used_system / 1024.0 / 1024.0 'MB used system'
	, a.database_transaction_log_bytes_reserved / 1024.0 / 1024.0 'MB reserved'
	, a.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0 'MB reserved system'
	, a.database_transaction_log_record_count 'Record count' 
FROM 
	sys.dm_tran_database_transactions a 
		JOIN sys.dm_tran_session_transactions b ON a.transaction_id = b.transaction_id 
		JOIN sys.dm_exec_requests c CROSS APPLY sys.Dm_exec_sql_text(c.sql_handle) AS st ON b.session_id = c.session_id 
ORDER BY 'MB used' DESC

Execution plan and statistics of a query from its handle

Replace 0x<SQL_HANDLE> with a sql_handle taken from sys.dm_exec_requests or sys.dm_exec_query_stats.

SELECT qp.query_plan, sql_handle, plan_handle, creation_time, last_execution_time, plan_generation_num, execution_count
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) AS qp
WHERE eqs.sql_handle=0x<SQL_HANDLE>
ORDER BY eqs.last_execution_time DESC

Running requests with their plan

SELECT
	r.session_id
,	r.start_time
,	TotalElapsedTime_ms = r.total_elapsed_time
,	r.[status]
,	r.command
,	DatabaseName = DB_Name(r.database_id)
,	r.wait_type
,	r.last_wait_type
,	r.wait_resource
,	r.cpu_time
,	r.reads
,	r.writes
,	r.logical_reads
,	t.[text] AS [executing batch]
,	SUBSTRING(
				t.[text], r.statement_start_offset / 2, 
				(	CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) 
						 ELSE r.statement_end_offset 
					END - r.statement_start_offset ) / 2 
			 ) AS [executing statement] 
,	p.query_plan
FROM
	sys.dm_exec_requests r
CROSS APPLY
	sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY	
	sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY 
	r.total_elapsed_time DESC;

Other tools

  1. Activity Monitor (SSMS)
  2. Server standard reports
  3. Database standard reports

Notes

Mastodon LLIMastodon SocialFosstodon