Not exactly sure what you are looking for, but this should give you something similar what you see on the activity monitor (not completely but similar). WHERE s.session_Id > 50 - Ignore system spids. LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id) OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st ORDER BY wait_duration_ms DESC) AS row_numįROM sys.dm_os_waiting_tasks ) w ON (t.session_id = w.session_id) ROW_NUMBER() OVER (PARTITION BY waiting_task_address and use it as representative of the other wait relationships this thread is involved in. Use ROW_NUMBER to select the longest wait for each thread, This will cause that thread to show up in multiple rows parallel queries, also waiting for a worker), one thread can be flagged as LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id) LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id) R.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)', =Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), ''), - It will display the Stored Procedure's Name. ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT)ĮND - r.statement_start_offset ) / 2 ) + 1), -It will display the statement which is being executed presently. = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, session is either not blocking someone, or is blocking someone but is blocked by another party WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1' session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others = ISNULL(CONVERT (varchar, w.blocking_session_id), ''), My Activity Monitor's Script is as follows: /* ACTIVITY MONITOR'S OUTPUT along with statement_text and command_text */ /* Processes */
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |