SQL Server Performance

getting total active connections

Discussion in 'Performance Tuning for DBAs' started by v1rtu0s1ty, Sep 23, 2004.

  1. v1rtu0s1ty New Member

    Hey guys,

    I am currently using "\SQLServer:General Statistics\User Connections" counter on the MSSQL server to find out how many active connections it currently have. I would like to know if there is a SELECT query that will tell me how many active connections there are?

    Thanks.

    V1rt
    v1rtu0s1ty, Sep 23, 2004
    #2

  2. FrankKalis Moderator

    What about querying the sysprocesses system table?


    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
    FrankKalis, Sep 23, 2004
    #2

  3. Chappy New Member

    try sp_who and sp_who2
    Chappy, Sep 24, 2004
    #2

  4. satya Moderator

    I think Frank require another revision of BOL for statements. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] (its friday)<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
    satya, Sep 24, 2004
    #2

  5. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />I think Frank require another revision of BOL for statements. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] (its friday)<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />[<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
    FrankKalis, Sep 24, 2004
    #2

  6. v1rtu0s1ty New Member

    I queried sysprocesses but I don't know what to look for. Please help. <img src='/community/emoticons/emotion-1.gif' alt=':)' />
    v1rtu0s1ty, Sep 24, 2004
    #2

  7. satya Moderator


    select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
    from sysprocesses
    group by substring(db_name(dbid),1,30)

    or

    SP_WHO2 to give you list for a database.

    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.
    satya, Sep 26, 2004
    #2

  8. FrankKalis Moderator

    Get another cup of coffee, satya!<br />Since when is [query] [/query] allowed forum code? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
    FrankKalis, Sep 27, 2004
    #2

  9. satya Moderator

    Doh!... thanks Frank its corrected and yes its time to get another cup of aromatic coffee [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />(had a busy weekend at office so bit stressed & tired)<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
    satya, Sep 27, 2004
    #2

  10. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />Doh!... thanks Frank its corrected and yes its time to get another cup of aromatic coffee [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />(had a busy weekend at office so bit stressed & tired)<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Haha, I don't need to get to the office to get stressed and tired [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
    FrankKalis, Sep 27, 2004
    #2

  11. satya Moderator

    Haha, I don't need to get to the office to get stressed and tired
    Don't you get tired after working 20 hours continuously ...[|)]

    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.
    satya, Sep 27, 2004
    #2

  12. v1rtu0s1ty New Member

    Thanks satya. It worked!
    v1rtu0s1ty, Sep 27, 2004
    #2

  13. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br />Don't you get tired after working 20 hours continuously ...[|)]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Well, it's sometimes much better than having the kids around [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
    FrankKalis, Sep 28, 2004
    #2

  14. satya Moderator

    LOL

    V1rtu0s1ty,
    I'm sceptical to run any queries against system tables (as always) when the pre-supplied statements are available.
    (just keep in mind)
    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.
    satya, Sep 28, 2004
    #2

  15. v1rtu0s1ty New Member

    What do you mean? I checked your query first in my test machine. Is it bad to query sysprocesses table?
    v1rtu0s1ty, Sep 30, 2004
    #2

  16. satya Moderator

    I don't say it is bad to query sysprocesses table for the results, as long as you're comfortable with pre-supplied statements. Its a general recommendation from MS and experience.

    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.
    satya, Oct 1, 2004
    #2

  17. FrankKalis Moderator

    The reason is that MS explicitely states that system tables might change on a service pack level. So there is no guarantee that your queries will work then. This is different from system procedures or the INFORMATION_SCHEMA views.

    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
    FrankKalis, Oct 1, 2004
    #2

  18. v1rtu0s1ty New Member

    Oh, it is ok. The reason I only need to grab the number of connection is to graph it. I can see trend now. It changes from 8am to 12pm(incrementing) then down back to 4pm(decrementing). The graph is nice. Along with this, I am also capturing the CPU. I then correlate them. <img src='/community/emoticons/emotion-5.gif' alt=';)' />
    v1rtu0s1ty, Oct 1, 2004
    #2

  19. Player New Member

    How about Using : sp_who2 active
    Player, Oct 19, 2004
    #2

Share This Page