SQL Server – Transact SQL – Get Network Info

Background

Wanted to review the IP Addresses for some of our SQL Server Instances that are hosted on Cloud Servers.

Outline

  1. CONNECTIONPROPERTY
  2. sys.dm_exec_connections
  3. sys.dm_tcp_listener_states

Code

CONNECTIONPROPERTY

SQL

Sample

SELECT 

          [netTransport]
            = CONNECTIONPROPERTY('net_transport')

        , [authScheme]
            = CONNECTIONPROPERTY('auth_scheme')

        , [ipAddress]
            = CONNECTIONPROPERTY('local_net_address')

        , [iPort]
            = CONNECTIONPROPERTY('local_tcp_port')

Output

connectionProperty_20181107_0732AM

sys.dm_exec_connections

SQL

Sample

SELECT
              [machineName]
                = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 

            , [sqlInstance]
                = SERVERPROPERTY('servername') 

            , [instance]
                = SERVERPROPERTY('InstanceName')

            , [ipAddress]
                = tblDMEC.[LOCAL_NET_ADDRESS]

            , [numberofConnections]
                = count(*)

 FROM sys.dm_exec_connections tblDMEC

 where
        (

            ( tblDMEC.LOCAL_NET_ADDRESS is not null )

        )

 group by

         tblDMEC.LOCAL_NET_ADDRESS

Output

sys.dm_exec_connections.20181107.0738AM.PNG

sys.dm_tcp_listener_states

SQL

Sample

SELECT * 

FROM   sys.dm_tcp_listener_states tblTLS

Output

sys.dm_tcp_listener_states.20181107.0811AM.PNG

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