Analyzing SQL SERVER 2005 LONG RUNNING QUERIES
Posted by Paulo Condeça on Monday, August 11, 2008
I’m leaving this tip for you to find what exactly is running on sql server.
SELECT r.session_id, s.HOST_NAME, s.PROGRAM_NAME,
s.host_process_id, r.status, r.wait_time,
wait_type, r.wait_resource,
SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
(CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
ELSE r.statement_end_offset
END -r.statement_start_offset)/2)
AS stmt_executing,r.blocking_session_id,
r.cpu_time,r.total_elapsed_time,r.reads,r.writes,
r.logical_reads, r.plan_handle
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions s
WHERE r.session_id > 50 and r.session_id=s.session_id
ORDER BY r.session_id, s.host_name, s.program_name, r.status
