Find Longest Running Query

Question

How I can find Longest Running Query and SPID on SQL Server 2008?

Answer 1

I usually use this script:

SELECT r.session_id as spid, r.[status], r.command, t.[text], r.blocking_session_id as blocked, r.start_time
FROM sys.dm_exec_requests AS r 
LEFT OUTER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id 
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.session_id <> @@SPID AND r.session_id > 50
ORDER BY start_time desc

There's also other columns in dm_exec_requests and dm_exec_sessions that might interest you.