Post reply

Simple question: How many users are connected to the sql server (instance)?

  • July 21, 2014 at 8:54 am

    #293513

    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

    #1731054

    You can try this-

    SELECT COUNT(*) FROM sys.sysprocesses

    WHERE dbid>0

  • July 21, 2014 at 9:32 am

    #1731061

    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

    #1731073

    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

    #1731105

    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

    #1731111

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2014 at 11:21 am

    #1731117

    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

    #1731179

    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

    #1731391

    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