SQL – Sessions – Blocked Sessions

Background

One of our Subject Matter Experts ( SMEs) called yesterday asking that we terminate sessions that are blocking other users.

Before terminating the block header, we reached for better situational awareness.

 

Code

Script

[dbo].[sp_SessionTransactionListing]



use [master]
go

if object_id('[dbo].[sp_SessionTransactionListing]') is null
begin

	exec('create procedure [dbo].[sp_SessionTransactionListing] as ')

end
go

alter procedure [dbo].[sp_SessionTransactionListing] 
(
	  @includeSystemSessions bit = 0
	, @loginName			 sysname = null
)
as
begin

	set nocount on;
	set XACT_ABORT on;

	declare @id	   int	
	declare @idMax int

	declare @tblSession TABLE
	(
		  [id]							int not null identity(1,1)
		, [spid]						int not null
		, [loginame]					sysname not null
		, [hostname]					sysname null
		, [programName]					sysname null
		, [blocked]						int		not null
		, [transactionIsolationLevel]	sysname not null
		, [loginTime]					datetime not null	
		, [numberofOpenedTransactions]	int		not null
		, [openTransactionCount]		int		not null
		, [clientInterfaceName]			sysname null
		, [clientVersion]				int		null
		, [textSize]					bigint	not null

		, [databaseID]					int	    not null
		, [database]				
			as 
				case
					when [databaseID] = 32767 then 'Resource DB'
					else db_name(databaseID)
				end	

		, [endpointID]					int		not null
		, [endpoint]					sysname  null

		, [requestStartTS]				datetime null	
		, [requestEndTS]				datetime null	

		, [durationInSeconds]
				as datediff
				(
					  second
					, [requestStartTS]
					, [requestEndTS]
				)


		, [timeSinceLastRequestInMinutes]
				as datediff
				(
					  minute
					, [requestEndTS]
					, getdate()
				)

		, [sqlCommand]				nvarchar(4000) null
		, [sqlText]					nvarchar(4000) null

		, [transactionName]			sysname null
		, [transactionType]			sysname null
		, [transactionBeginTS]		datetime null

		, primary key
			(
				[spid]
			)

	)

	declare @spid			int

	declare @tblInputBuffer TABLE
	(
		  [eventType]   sysname not null
		, [parameters]	int		not null
		, [eventInfo]	nvarchar(4000) null
	)

	declare @sqlText varchar(300)

	declare @iNumberofRecordsAffected int

	;with cteTransaction
	(
		  [sessionID]
		, [transactionName]
		, [transactionType]
		, [transactionBeginTS]
	)
	as
	(
		select 
			  [sessionID]
				= tblDTST.session_id

			, [transactionName]
				= tblDTAT.[name]

			, [transactionType]
				= case tblDTAT.transaction_type
						when 1 then 'Read/write transaction'
						when 2 then 'Read-only transaction'
						when 3 then 'System transaction'
						when 4 then 'Distributed transaction'
				end

			, [transactionBeginTS]
				= tblDTAT.transaction_begin_time

	from   sys.dm_tran_session_transactions tblDTST

	inner join sys.dm_tran_active_transactions tblDTAT

			on tblDTST.transaction_id = tblDTAT.[transaction_id]

	)
	insert into @tblSession
	(

		  [spid]						
		, [loginame]					
		, [hostname]					
		, [programName]					
		, [blocked]						
		, [transactionIsolationLevel]	
		, [numberofOpenedTransactions]	
		, [loginTime]
		, [openTransactionCount]		
		, [clientInterfaceName]			
		, [clientVersion]				
		, [textSize]					
		, [databaseID]					
		, [endpointID]					
		, [endpoint]					

		, [requestStartTS]				
		, [requestEndTS]				

		, [sqlCommand]

		, [transactionName]
		, [transactionType]
		, [transactionBeginTS]	

	)	
	select 
			distinct
			  tblSP.spid

			, [login] = tblSP.[loginame]

			, [hostname] = tblSP.[hostname]

			, tblSP.[program_name]

			, tblSP.[blocked]	

			, [transactionIsolationLevel]
				= case tblDMES.transaction_isolation_level 
						WHEN 0 THEN 'Unspecified' 
						WHEN 1 THEN 'ReadUncommitted' 
						WHEN 2 THEN 'ReadCommitted' 
						WHEN 3 THEN 'Repeatable' 
						WHEN 4 THEN 'Serializable' 
						WHEN 5 THEN 'Snapshot'
				  end

			, [numberofOpenedTransactions]
				= tblSP.[open_tran]

			, [loginTime]
				= tblSP.login_time

			, [openTransactionCount]
				= tblDMES.[open_transaction_count]

			, [clientInterfaceName]
				= tblDMES.client_interface_name

			, [clientVersion]
				= tblDMES.client_version

			, [textSize]
				= tblDMES.text_size

			, [databaseID]
				= tblDMES.database_id

			, [endpointID]
				= tblDMES.endpoint_id

			, [endPoint]
				= tblTE.[name]

			, tblDMES.last_request_start_time
								
			, tblDMES.last_request_end_time

			, [SQLCommand]
			  = coalesce
				(
					  tblSP.cmd
					, tblDMER.[command]
				)


			, cteT.[transactionName]
			, cteT.[transactionType]
			, cteT.[transactionBeginTS]	

	from   sys.sysprocesses tblSP

	left outer join sys.dm_exec_sessions tblDMES

			on tblSP.spid = tblDMES.session_id

	left outer join sys.tcp_endpoints tblTE
			on tblDMES.endpoint_id = tblTE.endpoint_id

	left outer join sys.dm_exec_requests tblDMER

			on tblSP.spid = tblDMER.session_id

	left outer join sys.dm_tran_session_transactions tblSDTS

			on tblSP.spid = tblSDTS.session_id

	left outer join cteTransaction cteT
		on tblSP.[spid] = cteT.sessionID

			
	where

			(

				-- Exclude system or background proceses
				(

					    ( @includeSystemSessions = 0 )
					and ( tblSP.[spid] > 50 )

				)
				or
				(

					( @includeSystemSessions = 1 )

				)


			)


	and     (
					(tblSP.[loginame] = @loginName )
				or  (@loginName is null)                                                                                                                       
			)

	/*
		Exclude this Session
	*/
	and     (
					(tblSP.[spid] != @@SPID )
			)

	order by
			tblDMES.open_transaction_count desc


	set @id = 1
	set @idMax = ( select max([id]) from @tblSession )


	while (@id <= @idMax)
	begin

		select @spid = [spid]
		from   @tblSession
		where  [id] = @id

		delete 
		from    @tblInputBuffer

		set @sqlText = 'dbcc inputbuffer(' + cast(@spid as varchar(4)) + ')'

		insert into @tblInputBuffer
		(
			  [eventType]   
			, [parameters]	
			, [eventInfo]	
		)
		exec (@sqlText)


		update tblS

		set   [sqlText] = tblIB.eventInfo

		from  @tblSession  tblS

		cross apply @tblInputBuffer tblIB

		where tblS.[id] = @id

		set @iNumberofRecordsAffected = @@ROWCOUNT

		set @id = @id + 1

	end

	select 

		  [spid]						
		, [loginame]					
		, [hostname]					
		, [programName]					
		, [blocked]						
		, [transactionIsolationLevel]	
		, [numberofOpenedTransactions]	
		, [openTransactionCount]		
		, [clientInterfaceName]			
		, [clientVersion]				
		, [textSize]					
		, [database]						
		, [endpoint]					
		, [durationInSeconds]
		, [timeSinceLastRequestInMinutes]
		, [sqlText]

		, [transactionName]
		, [transactionType]

		, [loginTime]
		, [requestStartTS]	
		, [requestEndTS]	
		, [transactionBeginTS]	

	from   @tblSession tblS

	order by
			tblS.numberofOpenedTransactions desc

end
go


grant execute on [dbo].[sp_SessionTransactionListing]  to [public]
go


 

Invoke


declare  @includeSystemSessions bit
declare  @loginName			    sysname

set @includeSystemSessions = 0
set  @loginName = null 

/*
	Query for user sessions
*/                                                                                                                        
exec [dbo].[sp_SessionTransactionListing]
		  @includeSystemSessions = @includeSystemSessions	
		, @loginName = @loginName

 

Output

sessions_prod_20170215_1128am

 

Explanation

  1. sys.sysprocesses.open_tran & sys.dm_exec_sessions
    • open_tran
    • open_transaction_count
      • Transaction Count
        • We have opened Transactions from simply select statements or fetches
    • Transaction Isolation Level is “Read Committed” for the uncommitted transactions
    • The Transactions have been left uncommitted for the last 164 minutes for two sessions and 46 minutes for one session

 

Version Control

GitHub

DanielAdeniji/SQLServerSessionTransactionListing
Link

 

References

  1. Kalen Delaney
    • Geek City: Why I still need Sysprocesses
      Link

One thought on “SQL – Sessions – Blocked Sessions

Leave a Reply

Please log in using one of these methods to post your comment:

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