Is it normal to constantly have 30+ connections to ‘master’ database in MS SQL Server 2012?

Question

In troubleshooting why I was seeing so many connections to one of our internally used databases in SQL Server 2012.

A query I found online that had been helpful to me was this:

 SELECT DB_NAME(sP.dbid) AS the_database
         , COUNT(sP.spid) AS total_database_connections
 FROM sys.sysprocesses sP
 GROUP BY DB_NAME(sP.dbid)
 ORDER BY 1;

However, although after fixing an issue, I've seen connections to our own custom database go back to normal levels, I'm always seeing the number of connections to the master database always at over 30 connections.

Is this normal for SQL Server 2012? Anyone else getting 30 or more connections to master when running this query?

Answer 1

30 system processes is absolutely not unheard of, and actually sounds better than normal.

However, you should not be using sysprocesses, which is deprecated and only exists for backward compatibility reasons - see the important note right at the top of the doc. (If you're using it solely for the database column, see this recent question, and the comments.)

You should be looking at sys.dm_exec_sessions - this view has a column called is_user_process that allows you to identify your processes and distinguish them from system processes (the old < 50 trick is no longer reliable on modern versions of SQL Server).

Just as an example, on my local dev machine - where I can assure you nothing is going on - I have the following rows:

sys.sysprocesses                                    62
sys.dm_exec_sessions                                68
sys.dm_exec_sessions where is_user_process = 1      26

The majority of those 26 user processes are just connections to the instance (which is on a VM guest) held open by Visual Studio Code on the host.

The other 42 are normal, sleeping, background system processes like LOG WRITER, LOCK MONITOR, XE DISPATCHER, 'CHECKPOINT', etc. It's not like you can get rid of these.

You could also compare the output of the undocumented sys.sp_who2:

EXEC sys.sp_who2;                       -- 64 rows in my case
EXEC sys.sp_who2 @loginame = N'active'; -- 40 rows in my case

Or limiting to actual running processes in sys.dm_exec_requests.

But for deeper insight into what processes are doing on your system, you're better off using something like Adam Machanic's sp_whoisactive or a proper monitoring tool (disclaimer: I work for one of those).