SQL Server – Aggregate Current Sessions Uptake

Background

We had an outage this morning.

Not sure what really triggered it.

But, as part of our investigative track wanted to get an aggregate view of the users that were connected, the commands they were running, and summation of resource usage.

Query



use [master]
go

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

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

end
go

alter procedure [dbo].[sp_sessions_aggregatedView]
(
	  @filterConnectionTypeSystemOrUser bit = null
	, @filterProgramName varchar(60) = null
	, @orderBy varchar(60) = null
)
as
begin

	declare @tblLogin TABLE
	(
		  [id]			int not null identity(1,1)
		, [loginame]	sysname
	)

	;with cte
	(
			  [systemOrUser]
			, [isUserProcess]
			, [sessionIDMin]
			, [sessionIDMax]
			, [status]
			, [hostname]
			, [loginame]
			, [program_name]
			, [dbid]
			, [database]
			, [command]
			, [numberofConnections]
			, [physicalIO]
			, [numberofLogicalReads]
			, [numberofReads]
			, [numberofWrites]
			, [totalElapsedTime]
			, [cpuTime]
			, [memoryUsedCurrent]
			, [numberofRows]
			, [clientInterfaceName]
			, [lastRequestStartTime]
			, [minutesSinceLastRequest]
			, [openTransactionCount]

	)
	as
	(

		select 

				[systemOrUser]
					 = case
							when tblSP.[spid] <= 50  then 'Background'
							else 'User'
					   end

			   , [isUserProcess]
				   =  case tblSES.is_user_process
	   					when 1 then 'Yes'
						else 'No'
					  end

			   ,  [sessionIDMin]
					= min(tblSP.[spid])


				,  [sessionIDMax]
					= max(tblSP.[spid])

				, [status]
					= tblSP.[status]
				
				,  tblSP.[hostname]

				, tblSP.[loginame]

				, tblSP.[program_name]

				, [dbid]
					= tblSP.[dbid]

				, [database]
					= db_name
						(
							tblSP.[dbid]
						)

				, [command]
					= tblSP.[cmd]

				, [numberofConnections]
					= count(*)

				, [physicalIO]
				   = sum(tblSP.physical_io)

				, [numberofLogicalReads]
					= sum(
							tblSES.[logical_reads]
						 )

				, [numberofReads]
					= sum(
							tblSES.[reads]
						 )

				, [numberofWrites]
					= sum(
							tblSES.[writes]
						 )

				, [totalElapsedTime]
					= sum
					   (
						   isNull
						   (
								  tblSES.[total_elapsed_time]
								, 0
							)
						)

				, [cpuTime]
					= sum
					   (
						   isNull
						   (
								  tblSES.[cpu_time]
								, 0
							)
						)

				, [memoryUsedCurrent]
					= sum
					   (
						   isNull
						   (
								  tblSES.[memory_usage]
								, 0
							)
						)


				, [numberofRows]
					= sum
					   (
						   isNull
						   (
								  cast( tblSES.[row_count] as bigint)
								, 0
							)
						)


				, [clientInterfaceName]
					= isNull(tblSES.client_interface_name, '')

				, [lastRequestStartTime]
					= max(tblSES.last_request_start_time)

				, [minutesSinceLastRequest]
					= datediff
						(
							  minute
							, max(tblSES.last_request_start_time)
							, getdate()
						)

				, [openTransactionCount]
					= sum(
							coalesce
								(
									  tblSP.[open_tran]
									, tblSES.[open_transaction_count]
								)
						 )

		from master.sys.sysprocesses tblSP

		left outer join sys.dm_exec_connections tblSEC

			on tblSP.spid = tblSEC.[session_id]

		left outer join [master].[sys].[dm_exec_sessions] tblSES

				on tblSEC.[session_id] = tblSES.[session_id]

	
		left outer join [master].[sys].[dm_exec_requests] tblSER

				on tblSEC.[session_id] = tblSER.[session_id]

		where tblSP.loginame not in
						(
							select [loginame]
							from   @tblLogin
						)

							
		and
			(
					( @filterConnectionTypeSystemOrUser is null )

				or ( isNull(tblSES.is_user_process, 0)
						= @filterConnectionTypeSystemOrUser 
					)
					
			)

		and
			(
					( @filterProgramName is null )

				or ( tblSP.[program_name]
						like @filterProgramName + '%'
					)
					
			)
	
	group by

				 case
					when tblSP.[spid] <= 50  then 'Background'
					else 'User'
				 end

			   , case tblSES.is_user_process
	   					when 1 then 'Yes'
						else 'No'
					  end

				, tblSP.[status]

				, tblSP.[hostname]
				, tblSP.[loginame]
				, tblSP.[program_name]
				, tblSP.[dbid]
				, tblSP.[cmd]
				, isNull
				(
					tblSES.client_interface_name
					, ''
				)

	)

	select 
			  [cte].[systemOrUser]
			, [cte].[isUserProcess]
			, [cte].[sessionIDMin]
			, [cte].[sessionIDMax]
			, [cte].[status]
			, [cte].[hostname]
			, [cte].[loginame]
			, [cte].[program_name]
			--, [cte].[dbid]
			, [cte].[database]
			, [cte].[command]
			, [cte].[numberofConnections]
			, [cte].[physicalIO]
			, [cte].[numberofLogicalReads]
			, [cte].[numberofReads]
			, [cte].[numberofWrites]
			, [cte].[totalElapsedTime]
			, [cte].[cpuTime]
			, [cte].[memoryUsedCurrent]
			, [cte].[numberofRows]
			, [cte].[clientInterfaceName]
			, [cte].[lastRequestStartTime]
			, [cte].[minutesSinceLastRequest]
			, [cte].[openTransactionCount]
				
	from   [cte]
	
	order by

			case
			 
				when @orderBy = 'numberofConnections' then [numberofConnections]
				when @orderBy = 'physicalIO' then [physicalIO]
				when @orderBy = 'numberofLogicalReads' then [numberofLogicalReads]
				when @orderBy = 'totalElapsedTime' then [totalElapsedTime]
				when @orderBy = 'cpuTime' then [cpuTime]
				when @orderBy = 'memoryUsedCurrent' then [memoryUsedCurrent]

				when @orderBy = 'database' then -[dbid]

				else [numberofLogicalReads]
			end 
					desc
	

end
go

Output

aggregatedusersview

 

Dedicated

Dedicated to my manager who said “even if it is or not a problem with the database, we can not rub it in“.

Listening

Martina McBride – For These Times
Link

In these times in which we live
Where the worst of what we live
Is laid out for all the world on the front page
And the sound of someone’s heartbreak
Is a soundbite at the news break
With a close shot of the tears rollin’ down their face
Blessed be the child who turns a loving eye
And stops to pray
For these times in which we live

 

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