Number of open connections and the meaning of sleeping status of a connection

Question

I ran this query in my database :

SELECT  
DB_NAME(dbid) as DBName,  
COUNT(dbid) as NumberOfConnections, 
loginame as LoginName 
FROM 
    sys.sysprocesses 
WHERE  dbid > 0 
GROUP BY  dbid, loginame 

---------------------------------------------------
SELECT  COUNT(dbid) as TotalConnections 
FROM sys.sysprocesses 
WHERE  
dbid > 0 
---------------------------------------------------
exec sp_who2 'Active' 

I want to know the total number of connections to my database. The sum of first query and amount of second query are equal but the third query returns a different number of rows.

I want to know what the third query returns? I see some of the status' in the result of the third query are sleeping. What does this mean? Is the connection idle, or it is ready in the pool? What does it mean if I have many sleeping connections in my result?

thanks

Answer 1

A status of sleeping means the session is connected but not actively running anything (e.g. the simplest definition, while perhaps not 100% accurate, is that there is nothing for that session_id in sys.dm_exec_requests).

sp_who2 'active' filters out any spid that has a status of sleeping or has a last command of AWAITING COMMAND, LAZY WRITER or CHECKPOINT SLEEP. No I did not memorize what sp_who2 does; I simply looked at the source code:

EXEC master..sp_helptext sp_who2;

Note that sp_who2 is undocumented and unsupported, and sysprocesses is deprecated and currently present only for backward compatibility reasons. You will be better served, I think, with a procedure like Adam Machanic's sp_whoisactive. Or at the very least knowing how to use the more modern DMVs like sys.dm_exec_sessions and sys.dm_exec_requests.

EDIT

I should add the unfortunate disclaimer that the more modern DMVs still don't properly reveal the database(s) involved. But what does it really mean anyway? If you have a query like this:

USE database_A;
GO

SELECT [column] FROM database_B.dbo.[table] 
  UNION ALL
SELECT [column] FROM database_C.dbo.[table];

What database id do you expect will get reflected in sys.sysprocesses.dbid for that session while this query is running? I'll leave it as an exercise to the reader to determine what actually happens. Long story short, that's not the view/column you want to rely on to know which databases are currently being "touched."