Post reply

Can you find out who is using your resources?

  • February 24, 2004 at 12:29 pm

    #84037

    Is there any easy way to find our which user is taking up the resources on a SQL Server machine? I know you can query the sysprocesses table and see in EM, but is there an easier way to view this without refreshing every few seconds?

  • February 24, 2004 at 1:58 pm

    #495412

    Try executing sp_who2 or sp_who

  • February 24, 2004 at 2:07 pm

    #495413

    Yes, I have used them, just looking for an easier way. It is hard to pick a connection that is taking up quite a few resources when you have a couple hundred connections.

    Just wondering if anybody had a quick easy way to check this. I suppose I could build something, but why reinvent the wheel, right.

    Thanks though.

  • February 24, 2004 at 3:13 pm

    #495428

    try this...

    create  procedure sp_who2c

    as

    /*************************************************************/                                                                                                                                                     

    --                                                  

    -- Module Name: sp_who2c       

    --                                               

    -- Description:

    --

    --  This procedure is "front end" to sp_who2 which provides

    -- logins in order of cpu usage.  also gives counts of

    -- current logins/connections.

    --

    -- Written By: Steve Phelps           

    --                                                 

    -- Date: November 10, 2003          

    --                                                  

    -- Modified :                                      

    -- Date:                                           

    --                                                 

    -- USAGE:                                           

    --                                                 

    --   exec sp_who2c

    --                           

    /*************************************************************/  

    declare

     @count int

    create table #who2

     (

     #SPID int NULL,

     #Status varchar(30) NULL,

     #Login sysname NULL,

     #HostName sysname NULL,

     #BlkBy varchar(128) NULL,

     #DBName sysname NULL,

     #Command varchar(128) NULL,

     #CPUTime int NULL,

     #DiskIO int NULL,

     #LastBatch char(14) NULL,

     #ProgramName sysname NULL,

     #SPIDb int NULL

    &nbsp

    create table #who2_counts

     (

     #Login sysname NULL,

     #Count int

    &nbsp

    insert #who2

     exec sp_who2

    select

      #SPID as SPID,

      #Status as Status,

      #Login as Login,

      #HostName as HostName,

      #BlkBy as BlkBy,

      #DBName as DBName,

      #Command as Command,

      #CPUTime as CPUTime,

      #DiskIO as DiskIO,

      #LastBatch as LastBatch,

      #ProgramName as ProgramName,

      #SPIDb as SPID

     from #who2

     order by #CPUTime desc, #Login, #HostName

    insert #who2_counts

     select distinct

       #Login as Login,

       count(#Login) as [Count]

      from #who2

      group by #Login

      order by [Count] desc

    select @count = count(*)

     from #who2

    insert #who2_counts

     (#Login, #Count)

     Values

     ('*Total*', @count)

    select

      #Login as Login,

      #Count as [Count]

     from #who2_counts

     order by [Count] desc

    drop table #who2

    drop table #who2_counts

     

     

  • February 24, 2004 at 3:17 pm

    #495429

    umm...   replace the "winks" with "close parentheses"

  • February 25, 2004 at 11:55 am

    #495641

    I found that handy.

    Thanks!

  • February 25, 2004 at 12:03 pm

    #495643

    I also just took a look at it and found it to be of use.

    Thanks

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply