SQL Server get current executing statements - SP_WHO or SP_WHO2 or sys.dm_exec_requests

As a SQL Server user or DBAs using SP_WHO or undocumented stored procedure SP_WHO2 is a common occurrence.

For this there are times when you will need to run sp_who on your SQL Server to figure out who is on and what are they doing. The output is quite clear to get a list of processes with SPID, but how about more detailed information on these processes. Until SQL Server 2005 it is bit tricky to get such detailed information, so what 2005 version offers you with system catalogs such as sys.dm_exec_requests. Here is the script you can obtain information that is freely available on Technet site too:

select r.session_id
,status
,substring(qt.text,r.statement_start_offset/2,
(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 query_text   --- this is the statement executing right now
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id

Until SQL Server 2005 SP_WHO2 was my favourite and using system catalogs & DMVs is much flexible to find out the culprit.