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.

Data Source=.\SQLEXPRESS;Initial Catalog=database_name;User Id=username;Password=pass;Application Name=application_name;

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'

References

Disclaimer

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.

About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN