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), ''), * ACTIVITY MONITOR'S OUTPUT along with statement_text and command_text */ /* Processes */ I have commented out some columns in the query, like: - = ISNULL(r.open_transaction_count,0), - = s.login_time, - = s.last_request_start_time, So if you want can also add or remove the columns as per your requirement and you can also filter out the data DatabaseName wise. You can also add or remove the columns whichever you need. - It will display the Stored Procedure's Name.-It will display the statement which is being executed presently and it can be from the SP or the normal T-sql.This script will display the following things: You can also view the currently running expensive queries by using this script and for that just need to do ORDER BY desc. You can play the activity monitor on one side and this script in another window and verify the output. I have modified the given script as follows. To get the exact output as Activity Monitor:
0 kommentar(er)
