How to determine total number of open/active connections in MS SQL Server?

There are two main methods to determine the total number of open/active connections in MS SQL Server:

1. Using a SQL Query:

You can use a Transact-SQL (T-SQL) query to retrieve the number of active connections. Here's an example:

SELECT COUNT(*) AS TotalConnections
FROM sys.dm_exec_sessions;        

This query uses the sys.dm_exec_sessions dynamic management view (DMV) which is recommended over the deprecated sys.sysprocesses view in newer versions of SQL Server. It simply counts the total number of active sessions.

SELECT 
    dbid AS [DbId],
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    SYS.SYSPROCESSES
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame
ORDER BY 
	DB_NAME(DBID), LOGINAME        

Using sys.dm_exec_sessions and sys.dm_exec_connections :

SELECT
    COUNT(*) AS TotalConnections
FROM
    sys.dm_exec_sessions AS s
INNER JOIN
    sys.dm_exec_connections AS c
ON
    s.session_id = c.session_id
WHERE
    s.is_user_process = 1;        

• sys.dm_exec_sessions: Contains information about all active sessions.
• sys.dm_exec_connections: Contains information about all active connections.
• s.is_user_process = 1: Filters out system processes, leaving only user connections.

This query counts only the user-initiated connections by joining the two views on the session_id.

2. Using SQL Server Management Studio (SSMS):

You can use the graphical interface of SSMS to view active connections. Here's how:

• Open SQL Server Management Studio.
• Connect to your SQL Server instance.
• Right-click on the server name in the Object Explorer pane and select "Activity Monitor" or Navigate to "Management" -> "Activity Monitor"..

The Activity Monitor will display a list of all active sessions, including details like login name, database being used, and wait status.

Both methods provide the total number of active connections in your SQL Server instance. The query offers a quick programmatic way to retrieve the count, while SSMS allows for further exploration of individual connections.