Finding the active SQL connections in SQL Server

There are several ways to find the active SQL connections is SQL Server. Let us see few of the simple and quick methods which are using T-SQL queries.

SP_WHO

Several years ago, when I started developing applications using SQL Server database, I used SP_WHO as a means of finding the active SQL connections running on a SQL Server. I used SP_WHO especially when there are blocks on the SQL Server to find the blocking and blocked process. Compared to the other methods explained below SP_WHO will have minimum columns, but a quick way to list down the active connections.

SP_WHO
Find Active SQL Connections using SP_WHO

SP_WHO2

Later, I came to know that there is an advanced version of SP_WHO, called as SP_WHO2. Even-though SP_WHO2 is not documented, it has more columns than SP_WHO like CPUTime, DiskIO, ProgramName, etc… These columns give us little more detail on the nature of the SQL Connections.

SP_WHO2
Find Active SQL Connections using SP_WHO2

SYS.SYSPROCESSES

Another wonderful method to find the active SQL connections is by using the system compatibility view SYS.SYSPROCESSES. This view has lot of columns with lot of information which are helpful for you to find out the active sql connections, especially when you want to find the blocked processes. However, this is a backward compatibility view. So instead of using SYS.SYSPROCESSES use the system view SYS.DM_EXEC_SESSIONS which I have explained below. You can get more details about SYS.SYSPROCESSES and its columns at Microsoft Docs.

SELECT * FROM SYS.SYSPROCESSES

SYS.DM_EXEC_SESSIONS

SYS.DM_EXEC_SESSIONS is one of the dynamic management views which replaces the old system table sysprocesses. The advantage of SYS.DM_EXEC_SESSIONS is its column is_user_process. Using this column, you can easily filter out the system processes.

SELECT is_user_process, * FROM SYS.DM_EXEC_SESSIONS

/* To list down only the user processes */
SELECT * FROM SYS.DM_EXEC_SESSIONS where is_user_process = 1
Find Active SQL Connections using SYS.DM_EXEC_SESSIONS

Conclusion

Nowadays, I’m using SYS.DM_EXEC_SESSIONS for finding the list of active SQL connections.

Related Article


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.