SQL Server Performance Forum – Threads Archive
Determining the number of connections
Group, How can I determine the total current number of connections that my SQL Server is using? Jim James E Mace Flying Ace Software Solutionshttp://www.jimmace.com
try<br /><br />CREATE TABLE #who<br /> ( spid SMALLINT,<br />ecid SMALLINT,<br />status NCHAR(30),<br />loginame NCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,<br />hostname NCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,<br />blk CHAR(5),<br />dbname NCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,<br />cmd NCHAR(16))<br /><br />INSERT #who<br /> EXEC sp_who<br /><br />select count(*) from #who<br /><br />You could filter out connections that are not of interest. For example all connections made by sa(loginame = ‘sa’) of connections made by the login id of the SQL Server service.
At home I don’t remember well, but I’m sure there is a counter in Performance Monitor: SQL Server General: User connections. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
If you want count you can get:
select count(*) as TotalConnections from master..sysprocesses
select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from master..sysprocesses
group by substring(db_name(dbid),1,30)
select * from master..sysprocesses Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>