T-SQL query to get the list of currently executing queries in particular session | SansSQL

Thursday, February 27, 2014

T-SQL query to get the list of currently executing queries in particular session

This query can be used as a alternative for using Profiler.

SELECT Sess.session_id, 
requests.status, 
requests.blocking_session_id 'Blocked By', 
requests.wait_type, 
wait_resource, 
requests.wait_time / (1000.0) 'Wait Time (sec)', 
requests.cpu_time, 
requests.logical_reads, 
requests.reads, 
requests.writes, 
requests.total_elapsed_time / (1000.0) 'Total Elapsed Time (sec)', 
SUBSTRING(st.TEXT,(requests.statement_start_offset / 2) + 1, 
((CASE requests.statement_end_offset 
WHEN -1 
THEN DATALENGTH(st.TEXT) 
ELSE requests.statement_end_offset 
END - requests.statement_start_offset) / 2) + 1) AS statement_text, 
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid,st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid,st.dbid)), 
'') AS command_text, 
requests.command, 
Sess.login_name, 
Sess.HOST_NAME, 
Sess.PROGRAM_NAME, 
Sess.last_request_end_time, 
Sess.login_time, 
requests.open_transaction_count 
FROM sys.dm_exec_sessions AS Sess
JOIN sys.dm_exec_requests AS requests
ON requests.session_id = Sess.session_id 
CROSS APPLY sys.Dm_exec_sql_text(requests.sql_handle) AS st 
WHERE requests.session_id <> @@SPID 
ORDER BY requests.cpu_time desc, requests.status, 
requests.blocking_session_id, 
Sess.session_id

No comments:

Post a Comment