SQL Server DBA Tools: List All Connections and Activities

Server Processes

Here is a straightforward 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 is very similar to the SQL Activity Monitor found in SSMS but more versatile and customizable.

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
ORDER BY command DESC

Key Columns

• SPID — The SQL Server process ID for the session.
• BlockedBy — If non-null, the SPID of the session that is blocking this one.
• Blocking — TRUE if this session is blocking one or more other sessions.
• BlockingCount — How many sessions this SPID is currently blocking.
• WaitType — The current wait type, useful for diagnosing performance issues.
• Individual Query — The specific statement currently executing within the batch.

Usage Tips

Remove the ORDER BY or add a WHERE clause to filter by specific databases, users, or commands. Remove the comment on the WHERE clause to filter for sessions that have an active command running.

Datazip provides 24/7 SQL Server monitoring and managed DBA services. Call 1-833-DATAZIP or visit www.SQLBrainBox.com for our monitoring tool.