SQL Server DBA Tools: List All Connections and Activities

Server Processes

Here's a straight forward query that every SQL Server DBA should have in their toolbox. This query lists all connections as well as blockings for a SQL Server.  It's very similar to the SQL Activity Monitor found in SSMS but more versatile and customizable.

--sp_who2

SELECT a1.SPID,
       [Database] = DB_NAME(a1.dbid),
       [User] = a1.loginame,
       Host = a1.hostname,
       a1.Status,
       OpenTransaction = a1.Open_Tran,
       Command = CASE WHEN a1.cmd = 'AWAITING COMMAND' THEN '' ELSE a1.cmd END,
       [Individual Query] = SUBSTRING(a2.[text], a4.statement_start_offset/2, (CASE WHEN a4.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), a2.[text])) * 2 ELSE a4.statement_end_offset END - a4.statement_start_offset)/2),
       CommandText = a2.[text],
       Application = a1.program_name,
       BlockedBy = CASE WHEN ISNULL(a4.blocking_session_id, 0) > 0 THEN a4.blocking_session_id END,
       Blocking = CASE WHEN a5.blocking_session_id IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END,
       a5.BlockingCount,
       a1.WaitTime,
       WaitType = CASE WHEN a1.lastwaittype = 'MISCELLANEOUS' THEN '' ELSE a1.lastwaittype END,
       a1.WaitResource,
       a1.CPU,
       CPU_DELTA = ISNULL(a4.cpu_time, 0),
       a1.PHYSICAL_IO,
       a3.READS,
       a3.WRITES,
       a1.MEMUSAGE,
       a1.Login_Time,
       a1.Last_Batch
FROM sysprocesses a1
JOIN sys.dm_exec_sessions a3 ON a1.spid = a3.session_id
LEFT JOIN sys.dm_exec_requests a4 ON a3.session_id = a4.session_id
LEFT JOIN (
    SELECT blocking_session_id, BlockingCount = COUNT(1)
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
    GROUP BY blocking_session_id
) a5 ON a1.spid = a5.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(a1.sql_handle) a2
--WHERE command is not null
ORDER BY command DESC