SQL Server Performance

status=SUSPENDED

Discussion in 'SQL Server 2005 General DBA Questions' started by mherfkens, Jul 29, 2008.

  1. mherfkens New Member

    I want to keep track of the processes that show up in sp_who2 as suspended. Is there a way to write a query against system tables to count and keep track of the number of processes whoose status is SUSPENDED at any particular time? I thinking about a query that could be executed every two minutes and return a count of the suspended processes. Is this possible? Thanks
    Michael
  2. SQL2000DBA New Member

    Please try this.select
    session_id,COUNT(*) from sys.dm_exec_requestswhere
    status='SUSPENDED'GROUP
    BY session_id
  3. Syed.Sabaur New Member

    Use this query
    SELECT STATUS, COUNT(STATUS) FROM MASTER.DBO.SYSPROCESSES WHERE STATUS = 'SUSPENDED' GROUP BY STATUS
  4. moh_hassan20 New Member

    note that suspended status may be related to sql internal session and is waiting for an event, such as I/O, to complete. (it is managed internally) , and you have to exclude it from your query.
    sp_who2 display its information from sys.sysprocesses

    in sql 2005 , The sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests are server scope dynamic management views map to the sys.sysprocesses system view (previously system table).

  5. chickisonline New Member

    Also pay attention at the execution context of the SPID, because if you have a value different from 0 then this means that that the SPID has more than one thread allocated. In this case it is possible that one ore more threads to be SUSPENDED.
    Dan

Share This Page