Checking for Active Connections to SQL Instance

SQL Server Administration
1

Hi experts,
I select from sys.sm_exec_sessions to see all db connections. The total is 6,100 which seems very high. The program name is .NET SLQ Client Data Provider in all rows. The oldest connection is from 3 days ago which is when the server was rebooted. Shouldn't the app be releasing old connections? Each day hundreds more are added.
Thanks.

2

I'm selecting * from sys.sm_exec_sessions.
Is there a way to select only ACTIVE connections?

3

Unfortunately, whoever wrote the applications connecting to your database didn't set the Application Name setting in the connection string. I was never able to convince our C# devs to bother to set this, either, so when they'd ask me to investigate which applications are blocking, all I could tell them was "it's one of the many .NET applications hosted on xyz server".

These applications are remaining connected to the database, even if they aren't actively making queries. You can look at [last_request_start_time] to see when the last query happened, but if they appear in this dm_exec_sessions list, they are holding an active connection to the database server.

4

This is what I use to quickly check connections. Maybe you will find it useful.

USE master;
SELECT	Instance		= @@SERVERNAME
	,	HostName		= s.host_name
	,	NetworkAddr		= c.client_net_address
	,	App				= s.program_name
	,	Interface		= s.client_interface_name
	,	LoginName		= s.login_name
	,	LoginTime		= s.login_time
	,	LastReqStart	= s.last_request_start_time
	,	DatabaseName	= d.name
/*
	,	CPU_ms			= s.cpu_time
	,	PageReads		= s.logical_reads
	,	PhysReads		= s.reads
	,	PhysWrites		= s.writes
	,	Mem_KB			= s.memory_usage * 8
	,	Rows			= s.row_count
--*/
	,	SPID			= s.session_id
	,	ExecCtx			= t.exec_context_id
	,	BlockBy			= w.blocking_session_id
	,	BlockByCtx		= w.blocking_exec_context_id
	,	OpenTrans		= r.open_transaction_count
	,	ReqStatus		= r.status
	,	ReqCommand		= r.command
	,	TaskState		= t.task_state
	,	WaitTime_ms		= w.wait_duration_ms
	,	WaitType		= w.wait_type
--	,	WaitResource	= r.wait_resource
	,	WaitResrceDesc	= w.resource_description
	,	DOP				= r.dop
	,	LastCmdBatch	= q.text
	,	ExecPlan		= CONVERT(XML, x.query_plan)
FROM	sys.dm_exec_sessions s
LEFT OUTER JOIN
		sys.databases d
ON		s.database_id = d.database_id
LEFT OUTER JOIN 
		sys.dm_exec_connections c
ON		s.session_id = c.session_id
OUTER APPLY
		sys.dm_exec_sql_text(c.most_recent_sql_handle) q
LEFT OUTER JOIN 
		sys.dm_exec_requests r
ON		s.session_id = r.session_id
LEFT OUTER JOIN 
		sys.dm_exec_query_memory_grants m
	ON	r.session_id = m.session_id
	AND	r.request_id = m.request_id
LEFT OUTER JOIN 
		sys.dm_os_tasks t
ON		r.session_id = t.session_id 
	AND	r.request_id = t.request_id
OUTER APPLY
		(SELECT	TOP (1) /* longest wait for task */
				owt.*
		FROM	sys.dm_os_waiting_tasks owt
		WHERE	owt.waiting_task_address = t.task_address
		ORDER BY 
				owt.wait_duration_ms DESC
		) w
OUTER APPLY 
		sys.dm_exec_text_query_plan (r.plan_handle, r.statement_start_offset, r.statement_end_offset) x
WHERE	s.session_Id NOT IN (@@SPID)
	AND	s.is_user_process = 1
	AND	s.login_name <> 'NT SERVICE\SQLTELEMETRY'
ORDER BY
		s.is_user_process DESC
	,	s.last_request_start_time DESC
	,	s.login_name
	,	s.session_id
	,	t.exec_context_id
	;
1 Like
5

@SQLHippo Thank you! I refined the query by adding this to the WHERE
AND Last_Request_Start_TIme > 'July 31, 2024'

This pulls only Connections that were initiated today. But I want to see only connections that are currently active.
sys.dm_exec_connections is (apparently) all connections that have been started since the instance was last bounced. Is that correct?

Basically I want to see the information that shows in Activity Monitor --Processes. This the Express Edition and for some reason when I click processes it doesn't show anything.Thanks

6

No, what you're seeing is active connections.

Test this yourself.

Open SSMS, and run a simple SELECT * FROM master.dys.dm_exec_connections ORDER BY session_id DESC;. Let's say the highest session_id in the list is 81.

Now, open a new query window. You should see a session_id (SPID), a number like (82), at the bottom in the information bar next to your servername, after your username, but before the database name, like
SERVERNAME | DOMAIN\dbaforever (82) | DBNAME | 00:00:00 | 0 rows.

OK, now if you switch back to the other tab and run the dm_exec_connections query again you'll see a row with the new tab's session_id. It doesn't even matter if you've run a query in that query window, it is actively connected to the server and will be shown in sys.dm_exec_connections.

Now, close the tab with session_id 82 (or whatever it was for you), and re-run the dm_exec_connections query again. Session_id 82 will be gone.

7

Oh, I see where the confusion is now...

dm_exec_sessions does appear to keep some basic info about old sessions.
dm_exec_connections only contains active connections.

I've always INNER JOINed these together, effectively only showing me session information if there is also an active connection.

1 Like