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
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
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
Conclusion
Nowadays, I’m using SYS.DM_EXEC_SESSIONS for finding the list of active SQL connections.
Related Article
- For extensive tracking and research of deadlocks because of blocking processes, I use Extended Events.
- A simple SQL statement to find the ongoing deadlocks on the SQL Server.