Waiting tasks

  • When I look in Activity Monitor it is showing that there are say 7 waiting tasks.

    How can I see what these tasks are?

  • Looks like sys.dm_os_waiting_tasks is what I need.

    Is there something I have to filter on to return the specific tasks indicated by Activity Monitor?

  • In activity monitor right click on the process and select details.

  • That isn't what I mean.

    In the overview panel there is a graph showing 7 waiting tasks. I'm trying to find out how I can identify these 7 tasks.

  • Expand processes and filter on wait type non blanks

  • I did try that but the number of processes shown never seems to correspond to the number in the overview panel.

  • It generally wont I have found, usually down to parallelism where one thread is wating for others to complete, in that case while it is a waiting thread, its not a waiting thread at the same time due to it actually having multiple threads some of which are still active.

    Thats what I have found in my environment, it might not be the same for you.

  • Ah ok. Thanks for the tip and I'll bear that in mind.

  • maybe you can try running try following command:

    select * from sysprocesses where spid > 50 and status <> 'running'

    spid> 50 will filter out all SQL Server system processes ( for e.g. like Checkpoint process)

    you can use dbcc inputbuffer(spid) to get additional details

    and you can also use DMVs to get the entire text for the SQL command associated with the SPID.

  • You can expand the [process] tab in Activity Monitor and that should show you. Alternatively you can run the following script (or the many variations of it) to query the sys.dm_exec_requests DMO

    SELECT

    [spid] = session_Id

    , ecid

    , [blockedBy] = blocking_session_id

    , [database] = DB_NAME(sp.dbid)

    , = nt_username

    , [status] = er.status

    , [wait] = wait_type

    , [current stmt] =

    SUBSTRING (

    qt.text,

    er.statement_start_offset/2,

    (CASE

    WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt.text)

    ELSE er.statement_end_offset

    END - er.statement_start_offset)/2)

    ,[current batch] = qt.text

    , reads

    , logical_reads

    , cpu

    , [time elapsed (ms)] = DATEDIFF(mi, start_time,getdate())

    , program = program_name

    , hostname

    --, nt_domain

    , start_time

    , qt.objectid

    FROM sys.dm_exec_requests er

    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt

    WHERE session_Id > 50 -- Ignore system spids.

    AND session_Id NOT IN (@@SPID) -- Ignore this current statement.

    ORDER BY 1, 2

    GO

Viewing 10 posts - 1 through 9 (of 9 total)

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