Identifying opened connections for the specific application in SQL Server
Connection listing queries in SQL Server
Object relational mappers or ORMs are a great tool which can significantly reduce application development time, but if they are not use wisely they can cause bottlenecks and performance issues.
Same goes for dependency injection containers. The lifetime of the components, especially the ones that can cause recurse locks like SQL Server connections must be as short as possible and they should be released as soon as work is done.
If these rules are not followed, performance issues may occur and in those cases you need to narrow down application connections to a specific database server. One of the indicators to filter out connections can be database name, but often that is not clear since database can be accessed by multiple clients at the same time. The best way is to identify your application by supplying the Application Name in you connection string for SQL Server.
Application Name property is optional and therefore quite often not included in in connection string which makes narrowing down specific application issues when it comes to SQL Server connections.
The first thing that comes to when investigating ongoing thing on the SQL Server is SQL Server Profiler tool, but if you just want to see connections for specific application, trace can become a bit hard to filter especially on a busy server. Luckily SQL Server comes with few build in stored procedures and functions that can be easy used in queries.
Here are few ways of listing SQL Server connection via query
Filtering results from sp_who2 stored procedure
Unlike sp_who stored procedure, sp_who2 is not documented in official Microsoft online documentation for SQL Server. The main difference between these two stored procedures is our key value and that is Application Name which we supply in the connection string.
EXEC sp_who2 GO EXEC sp_who
In sp_who2, this value is returned as ProgramName column in the result table value.
Stored procedure result is not convenient to filter against, so to be able to filter out only records for our specific application we need to select stored procedure result into temporary table with the same structure as the sp_who2 stored procedure result and which we drop after we select values we need and apply filters.
CREATE TABLE #TEMP ( [SPID] INT, [Status] VARCHAR(150), [Login] VARCHAR(150), [HostName] VARCHAR(150), [BlkBy] VARCHAR(150), [DBName] VARCHAR(150), [Command] VARCHAR(150), [CPUTime] INT, [DiskIO] INT, [LastBatch] VARCHAR(150), [ProgramName] VARCHAR(150), [SPID2] INT, [REQUESTID] INT ) GO INSERT INTO #TEMP EXEC sp_who2 SELECT * FROM #TEMP where ProgramName='application_name' DROP TABLE #TEMP
Using sys.dm_exec_sessions function
One more way of getting the connections list is using this SQL Server system function. Since it is a function and not stored procedure, we can directly filter it's output without having to use temporary table
SELECT * FROM sys.dm_exec_sessions WHERE program_name='application_name'
Unlike sp_who2, this function is well documented in Microsoft online documentation for SQL Server (sys.dm_exec_sessions (Transact-SQL))
Using sys.sysprocesses function
Finally, connection list for the specific application can be obtained from the processes list from SQL Server with another well documented system function sys.sysprocesses
SELECT * FROM sys.sysprocesses WHERE program_name='application_name'
- SQL Server Profiler
- sys.dm_exec_sessions (Transact-SQL)
- sp_who (Transact-SQL)
- sys.sysprocesses (Transact-SQL)
Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.
Comments for this article