July 21, 2014 at 8:54 am
Hi all,
I can't find perfect answer for this question: Can you check how many users are connected to the Sql Server (instance)?
July 21, 2014 at 9:05 am
You can try this-
SELECT COUNT(*) FROM sys.sysprocesses
WHERE dbid>0
July 21, 2014 at 9:32 am
When I run your query gives me 31.
when I run:
sp_who2
or
select * from sys.dm_exec_connections
gives me 10 (which is >50 spid) !!!!!
July 21, 2014 at 9:56 am
The query to provide you the number of connections:
SELECT COUNT(*)
,host_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY host_name
You can no longer use the rule of session_id greater than 51, because system sessions are no longer restricted in that manner. You should update your scripts to use the is_user_process bit column.
Also since you are already querying this DMV and sp_who why is it you cannot determine how many users are connected to your instance?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 21, 2014 at 11:06 am
Thank you Shawn for your reply. I am bit confuse here, when I run your query I get total no. 97 (when i add all) but when I run Sp_who2 i get 300+ results (including 'sleeping' and 'suspended').
My question is when somebody ask you "how many connections are currently connected to the instance"? then should you say 97 (according to your script) or 300+ (according to sp_who2)? And lastly, If I go by Sp_who2 results, shouldn't I subtract 50 because it is from internal connection??
July 21, 2014 at 11:14 am
smtzac (7/21/2014)
Thank you Shawn for your reply. I am bit confuse here, when I run your query I get total no. 97 (when i add all) but when I run Sp_who2 i get 300+ results (including 'sleeping' and 'suspended').My question is when somebody ask you "how many connections are currently connected to the instance"? then should you say 97 (according to your script) or 300+ (according to sp_who2)? And lastly, If I go by Sp_who2 results, shouldn't I subtract 50 because it is from internal connection??
sp_who2 shows inactive sessions, as well. You can kind of get around that by using sp_Who2 ACTIVE.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2014 at 11:21 am
smtzac (7/21/2014)
Thank you Shawn for your reply. I am bit confuse here, when I run your query I get total no. 97 (when i add all) but when I run Sp_who2 i get 300+ results (including 'sleeping' and 'suspended').My question is when somebody ask you "how many connections are currently connected to the instance"? then should you say 97 (according to your script) or 300+ (according to sp_who2)? And lastly, If I go by Sp_who2 results, shouldn't I subtract 50 because it is from internal connection??
Go by what dm_exec_sessions reports. That DMV is tracking the sessions (connections).
sp_who2 will often show an entry for each session X the number of threads being used for that session (e.g. can and will show separate entries for queries running parallel).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 21, 2014 at 1:04 pm
smtzac (7/21/2014)
Thank you Shawn for your reply. I am bit confuse here, when I run your query I get total no. 97 (when i add all) but when I run Sp_who2 i get 300+ results (including 'sleeping' and 'suspended').My question is when somebody ask you "how many connections are currently connected to the instance"? then should you say 97 (according to your script) or 300+ (according to sp_who2)? And lastly, If I go by Sp_who2 results, shouldn't I subtract 50 because it is from internal connection??
sp_who and sp_who2 from what I recall will use the older catalog views that still exist in SQL Server 2008. I tend to stop using those just for the sake of not filtering out the noise.
You have to differentiate between physical and logical connections. I state them as host connections (physical) and user connections (logical). When you open SSMS and connect to an instance, that is a physical connection to the instance. When you go and open three new query windows, that is three sessions or logical connections to a database. When I am asked by management how many connections occur against any database server I am going to give him the logical connections, count from dm_exec_sessions. This number can change constantly so you might run the query one time and get a value, then immediately hit execute again and a lower or higher number will be shown.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 22, 2014 at 6:08 am
Thank you Mr. Shawn!!!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply