July 12, 2012 at 7:07 am
When I look in Activity Monitor it is showing that there are say 7 waiting tasks.
How can I see what these tasks are?
July 12, 2012 at 9:01 am
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?
July 12, 2012 at 10:14 am
In activity monitor right click on the process and select details.
July 13, 2012 at 2:28 am
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.
July 13, 2012 at 2:30 am
Expand processes and filter on wait type non blanks
July 13, 2012 at 2:34 am
I did try that but the number of processes shown never seems to correspond to the number in the overview panel.
July 13, 2012 at 2:38 am
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.
July 13, 2012 at 4:28 am
Ah ok. Thanks for the tip and I'll bear that in mind.
July 14, 2012 at 7:47 am
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.
March 26, 2014 at 11:04 am
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