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
  2. FrankKalis Moderator

    What about querying the sysprocesses system table?


    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  3. Chappy New Member

    try sp_who and sp_who2
  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>
  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 />
  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=':)' />
  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.
  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 />
  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>
  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 />
  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.
  12. v1rtu0s1ty New Member

    Thanks satya. It worked!
  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 />
  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.
  15. v1rtu0s1ty New Member

    What do you mean? I checked your query first in my test machine. Is it bad to query sysprocesses table?
  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.
  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
    -----------------------
  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=';)' />
  19. Player New Member

    How about Using : sp_who2 active

Share This Page