T-SQL to Check Running Sessions and Statements

Below SQL script can be used to query the current running sessions and their executing statements. It also shows whether it's a parallel execution (multi-thread), which login account, whether it's being blocked and its blocker, what resource it's waiting for, the CPU and I/O usage, etc.

SELECT CASE WHEN (SELECT COUNT(*) FROM sysprocesses WHERE spid = r.spid) > 1 THEN 'Multithread' ELSE '' END AS Multithread, LEFT(t.[text], 255) AS [text255], SUBSTRING( t.[text], (r.stmt_start / 2) + 1, (( CASE r.stmt_end WHEN -1 THEN DATALENGTH(t.[text]) ELSE r.stmt_end END - r.stmt_start) / 2) + 1) AS stmt, DB_NAME(t.dbid) AS ObjectDB, OBJECT_NAME(t.objectid, t.dbid) AS Object, r.spid, r.ecid, r.blocked, r.waittime, r.lastwaittype, r.waitresource, DB_NAME(r.dbid) AS connectDB, r.cpu, r.physical_io, r.memusage, r.login_time, r.last_batch, r.open_tran, r.status, r.hostname, r.program_name, r.loginame, r.cmd, r.net_library, r.login_time, r.stmt_start, r.stmt_end FROM sys.sysprocesses AS r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE r.status IN ('runnable', 'suspended', 'running', 'rollback', 'pending', 'spinloop') ORDER BY spid, ecid

Below screen illustrates the query result (split to two rows)"