In this post I demo a query to get connection information in SQL Server aggregated by database, host, client and login name.
Intro
I’ve spent some time this week working on the latest release of PSBlitz, and, as part of that, I’ve added 2 new columns to the “Top 10 client connections” table of the “Instance Overview” report page.
For reference, this is how that table looks in the HTML version of the report.
Since I was already working on this, I’ve figured I’d also make the query available in a blog post.
I opted to remove the 10 rows limit here for anyone looking for a query that returns connection information for all the connections on a given SQL Server instance.
Why?
I generally find it useful to see the most active databases and logins, especially for tracking down applications with poor connection management.
Creating a store procedure that inserts the result of the query in a table, and having it run periodically through an Agent job is also a resource friendly option to identify databases that are no longer being used.
The query
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | SELECT [d].[name] AS [database], COUNT([c].[connection_id]) AS [connections_count], RTRIM(LTRIM([s].[login_name])) AS [login_name], ISNULL([s].[host_name], N'N/A') AS [client_host_name], REPLACE(REPLACE([c].[client_net_address], N'<', N''), N'>', N'') AS [client_IP], [c].[net_transport] AS [protocol], ISNULL(NULLIF(CAST(SUM(CASE WHEN LOWER([s].[status]) = N'preconnect' THEN 1 ELSE 0 END) AS VARCHAR(20))+ ' preconnect', '0 preconnect')+'; ', '') +ISNULL(NULLIF(CAST(SUM(CASE WHEN LOWER([s].[status]) = N'dormant' THEN 1 ELSE 0 END) AS VARCHAR(20))+' dormant', '0 dormant')+'; ', '') +ISNULL(NULLIF(CAST(SUM(CASE WHEN LOWER([s].[status]) = N'running' THEN 1 ELSE 0 END) AS VARCHAR(20))+' running', '0 running')+'; ', '') +ISNULL(NULLIF(CAST(SUM(CASE WHEN LOWER([s].[status]) = N'sleeping' THEN 1 ELSE 0 END) AS VARCHAR(20))+' sleeping', '0 sleeping'), '') AS [sessions_by_state], MAX([c].[connect_time]) AS [oldest_connection_time], MIN([c].[connect_time]) AS [newest_connection_time], [s].[program_name] AS [program] FROM sys.[dm_exec_sessions] AS [s] LEFT JOIN sys.[databases] AS [d] ON [d].[database_id] = [s].[database_id] INNER JOIN sys.[dm_exec_connections] AS [c] ON [s].[session_id] = [c].[session_id] GROUP BY [d].[database_id], [d].[name], [s].[login_name], [s].[security_id], [s].[host_name], [c].[client_net_address], [c].[net_transport], [s].[program_name] ORDER BY [connections_count] DESC; |
You can also find it in my SQL Server Scripts repo as GetClientConnectionInfo.sql
Connection information result set
The result set is pretty self-explanatory, so there’s no need for me to go into details.
The connection details are aggregated per database, login, client host and IP, protocol, and program name to make the result set more manageable on busy instances.
Note that you get trailing “;” if there are only running sessions for a given record.
I can sort this out easily in PowerShell for PSBlitz, but I didn’t want to invest too much time on a Sunday to address it in T-SQL and I also wanted to keep the query fairly small.
Conclusion
I wrote a query and thought I’d share it for anyone who might want to view connection details in SQL Server.
Also, feel free to check out PSBlitz if you haven’t already.
If you’re interested in more utilitarian scripts, you might be interested in:
- get table sizes in SQL Server
- search for a string in an entire SQL Server database
- kill multiple sessions in one go (based on various conditions)
Or just browse the Scripts category of my blog.