SQL Server Performance

How to find which spid is using more CPU

Discussion in 'Performance Tuning for DBAs' started by suniljk7, Sep 1, 2007.

  1. suniljk7 New Member

    Dear all,
    For last few days on my server, sql server is using more than 80% CPU almost all the time, i find out all the thread id, KPID which is using more cpu and when i query the sysprocess table to find the SPID of all these kpid's i couldn't see any there. There was around 40 KPID's which was using more cpu, but when i quried the sysprocess table i got only 10-14 spid's and in that i find only 4-5 spid's which is corresponding to my kpid's. why is this happening.. how can i find which spid is eating more cpu?? please guide me on this.
    i want to find which spid is using more CPU, sp_who2 is also not give the cumulatively cpu time only . ( my objective is to get the spid who takes more cpu and the query associated to this spid)
    Thanks
    John


  2. ndinakar Member

    do a SELECT * FROM sysprocessed order by CPU Desc. once you get the SPIDs run DBCC INPUTBUFFER(spid) to see what the spid is doing.
  3. suniljk7 New Member

    I said that when i do a query on sysprocess table i couldn't find the SPIDs of the KPIDs which is using more CPU, i could see only few kpids in sysprocess table (like 4-5). also when i check with inputbuffer(spid) i could see only (sp_cursorclose;1, sp_cursoropen;1, sp_cursorfetch;1) and not any query. acutally what is this how can i know who is creating this cursor?? please guide me on this..
    i am finding it difficult to drill down the actual cause of problem
    when i check the profiler i could lots of entry like this.. can you please comment on this..

    ------------------------------------------------------------------------------------
    declare @P1 int
    set @P1=188707183
    declare @P2 int
    set @P2=1
    declare @P3 int
    set @P3=4
    declare @P4 int
    set @P4=0
    exec sp_cursoropen @P1 output, N'select top 5 Id,smsChatURL from SMSFilterURLS (nolock)
    where smsChatstatus in(0,2)
    order by smsChatstatus', @P2 output, @P3 output, @P4 output
    select @P1, @P2, @P3, @P4

    exec sp_cursorfetch 188707183, 2, 1, 128

    declare @P1 int
    set @P1=-1
    declare @P2 int
    set @P2=0
    exec sp_cursorfetch 188707183, 256, @P1 output, @P2 output
    select @P1, @P2

    exec sp_cursorclose 188707183
    -------------------------------------------------------------------------------------

    what is this? why is this happening? is this create any over load on the server? please give me more info on this..
    Thanks
    John
  4. satya Moderator

  5. suniljk7 New Member

    I am using sql server 2000, its not working for me any other suggestion.
  6. satya Moderator

    The first reference on that blog refers the action you need to take care in this regard, high cpu usage.
  7. suniljk7 New Member

    As in my first post, i said that i have find out the thread which is using more CPU and i found KPID also, but when i try to find the spid associated with this kpid i couldn't find, when i query the sysprocess i could see only 5 -6 spid's have kpid. How can i find this..
    Thanks
    John

Share This Page