4 Methods of Identifying Connections Count in SQL Server

Recently I received a question about finding out how many connections were associated with a SQL Server instance.  The answers really depend upon which version of SQL Server we're talking about.  My preference focuses on diving into the Dynamic Management Objects when asking these metadata-centric questions.  The schema for these objects are always subject to change however; whether that takes place at a service pack release as I've seen with the sys.dm_os_sys_info DMV or  frequently when new versions of Microsoft SQL Server are rolled-out as we'll see with sys.dm_exec_sessions below.  When it comes to this DMV I was particularly happy to see that they added database_id to the view schema in 2014 because it not only resulted in the need to join less DMVs together in a view to get active request and transaction information (through joining sys.dm_exec_sessions to either sys.dm_exec_requests on session_id or connecting to the transaction-based DMVs through sys.dm_tran_session_transactions on session_id) but also allowed for identifying all sessions associated to a database.  Until SQL Server 2014 the information for connections to a database (and really what we're talking about are sessions associated to a database rather than connections to the instance) did not provide the full story.  For that we still were forced to rely upon a System Compatibility View that has remained unchanged since SQL 2005 (and will continue to remain so in perpetuity.)

1. Database Connections Using DMVs in SQL Server 2014

In SQL Server 2014 database_id was added as a column  to the sys.dm_exec_sessions Dynamic Management View.  This now allows us to answer this question without having to join to any other objects.  Also, since we're using sys.dm_exec_sessions we are able to identify which sessions are associated with users and which ones are internal to SQL Server through the is_user_process column.

--=============================================--Database Connections Using DMVs--=============================================SELECT DB_NAME(eS.database_id) AS the_database, eS.is_user_process, COUNT(eS.session_id) AS total_database_connectionsFROM sys.dm_exec_sessions eS GROUP BY DB_NAME(eS.database_id), eS.is_user_processORDER BY 1, 2;

2. Database Connections Using sysprocesses in SQL Server 2005 - 2014

Compatibility Views in SQL Server were created to provide an identical experience as was offered under SQL Server 2000 and earlier within the system tables in the Master database.  When SQL Server 2005 was released the underlying foundation of SQL Server changed.  The Master database was rendered into submission by the new ResourceDB; essentially making it read-only.  To allow for solutions built by DBAs, Developers and Software Solutions Providers against these system tables, the Compatibility Views were constructed using identical schema and naming as the system tables were in earlier versions of SQL Server; the only difference being the move from the dbo schema to the sys schema for these objects.  This means that the sys.sysprocesses Compatibility View looks like - and will continue to look like the dbo.sysprocesses table inside of SQL Server 2000 until the last dying mechanical gasp of the last robot DBA on the planet.  It also means that this query below should work until you retire to Florida, Bali, Bergen, Moon Base Delta or elsewhere.

--=============================================--Database Connections Using sys.sysprocesses--=============================================SELECT DB_NAME(sP.dbid) AS the_database, COUNT(sP.spid) AS total_database_connectionsFROM sys.sysprocesses sPGROUP BY DB_NAME(sP.dbid)ORDER BY 1;

If you wanted to get the granularity of internal v. user processes you could join this query to sys.dm_exec_sessions on spid = session_id and bring in the is_user_process column.  However if you intend to do that why not simply use the first query that only uses sys.dm_exec_sessions?

3. Database Connections Using DMVs in SQL Server 2005-2012

What you'll notice though when using the DMVs in this fashion is that not all results are returned due to some limits of what falls in scope for sys.dm_exec_connections and session state.

--=============================================--Database Connections Using DMVs Pre-SQL 2014--=============================================SELECT DB_NAME(ST.dbid) AS the_database, COUNT(eC.connection_id) AS total_database_connectionsFROM sys.dm_exec_connections eC CROSS APPLY sys.dm_exec_sql_text (eC.most_recent_sql_handle) STLEFT JOIN sys.dm_exec_sessions eS ON eC.most_recent_session_id = eS.session_idGROUP BY DB_NAME(ST.dbid)ORDER BY 1;

4. Database Connections Using Performance Counters

Connection information can be collected using Performance Monitor as well.  The method I prefer to get performance counter information for SQL Server objects is by using the sys.dm_os_performance_counters DMV.  This allows me full access to the data without needing to launch PerfMon and go through the laborious process of mining this information from the GUI.  What you'll see here though is the similar dearth of information as we did in the previous option since not all data falls within scope of this Dynamic Management View (or the underlying performance counters.)

--=====================================================--Database Connections Using dm_os_performance_counters--=====================================================SELECT oPC.cntr_value AS connection_countFROM sys.dm_os_performance_counters oPCWHERE (oPC.[object_name] = 'SQLServer:General Statistics'AND oPC.counter_name = 'User Connections')ORDER BY 1;