DMV Version of sp_who2: A Cleaner Session List

A DMV version of sp_who2 gives you the same session list from documented views. It leaves out the system tasks and lets you choose the columns. The query below needs only two views and no temporary table.

What sp_who2 Gives You

The procedure sp_who2 lists every session on the instance. Each row shows the login, host, database, command and blocking session. DBAs have typed it for decades. It is also undocumented, so its columns and behavior can change without notice. Its output includes system tasks, which most people then have to ignore.

The old way to filter that list is a script on sys.sysprocesses with a WHERE spid > 50 line. SQL Server keeps that view as a compatibility view for old scripts. Current versions give you two views that do the same job better. One row per session comes from sys.dm_exec_sessions. One row per running request comes from sys.dm_exec_requests.

The Query

Here is the DMV version of sp_who2. Join the two views with a LEFT JOIN, because an idle connection has a session row and no request row. COALESCE then fills the gaps. An idle session gets the command AWAITING COMMAND, the same words sp_who2 prints. NULLIF turns the 0 that means not blocked into a NULL.

SELECT s.session_id AS SPID,
       COALESCE(r.status, s.status) AS Status,
       s.login_name AS LoginName,
       s.host_name AS HostName,
       NULLIF(r.blocking_session_id, 0) AS BlockedBy,
       DB_NAME(COALESCE(r.database_id, s.database_id)) AS DBName,
       COALESCE(r.command, N'AWAITING COMMAND') AS Command,
       r.wait_type AS WaitType,
       s.cpu_time AS CPUTime,
       s.reads + s.writes AS PhysicalIO,
       s.last_request_start_time AS LastBatch,
       s.program_name AS ProgramName
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
WHERE s.is_user_process = 1
ORDER BY s.session_id;

The WaitType column is an extra. It names what a waiting request waits for, and sp_who2 has no such column. The PhysicalIO column adds the physical reads and writes of the session. It is the counterpart of DiskIO in sp_who2. For logical reads, add s.logical_reads AS LogicalReads, which sp_who2 does not show.

Read the BlockedBy column first. A number there is the session that holds the lock this session needs. Look up that session next, and follow the chain until you reach a row where BlockedBy is empty. That row is the head of the blocking chain.

Why a Filter on SPID 50 Fails

Old scripts filter on spid greater than 50, because session IDs up to 50 were reserved for system tasks. That rule no longer holds. System sessions get IDs above 50, and the flag is_user_process tells the truth. This query counts both kinds.

SELECT COUNT(*) AS AllSessions,
       SUM(CASE WHEN is_user_process = 1 THEN 1 ELSE 0 END) AS UserSessions,
       SUM(CASE WHEN session_id > 50 AND is_user_process = 0 THEN 1 ELSE 0 END) AS SystemAbove50,
       SUM(CASE WHEN session_id <= 50 AND is_user_process = 1 THEN 1 ELSE 0 END) AS UserAtOrBelow50
FROM sys.dm_exec_sessions;

In this run, 78 of 131 sessions were system sessions with an ID above 50. A filter on spid > 50 would have listed all of them next to the 3 user sessions. Your numbers will differ, and they change as sessions come and go. The WHERE is_user_process = 1 line in the query above is the filter to keep.

Look at Your Own Session

A busy server prints rows you do not own, so test the query on your own session first. The next query joins the session to its request and reads the text of the statement that is running. Your SPID will differ from the one on another machine.

SELECT s.session_id AS SPID, r.status AS Status, r.command AS Command,
       DB_NAME(r.database_id) AS DBName, LEFT(st.text, 30) AS CurrentStatement
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.session_id = @@SPID;

The statement that runs is the query itself, so the status is running and the command is SELECT. DBName is the database the query ran in. The text comes from sys.dm_exec_sql_text, which takes the SQL handle of the request. Add that function to the main query when you want the full text of every running statement.

Store the Result in a Table

To keep the list, put INTO #SessionLog between the column list and the FROM. SQL Server creates the table from the query. A column with SYSDATETIME() records when the capture ran. This script stores the user sessions and counts the row of the current session.

SELECT SYSDATETIME() AS CapturedAt,
       s.session_id AS SPID,
       COALESCE(r.status, s.status) AS Status,
       s.login_name AS LoginName,
       DB_NAME(COALESCE(r.database_id, s.database_id)) AS DBName,
       COALESCE(r.command, N'AWAITING COMMAND') AS Command
INTO #SessionLog
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
WHERE s.is_user_process = 1;
SELECT COUNT(*) AS RowsForThisSession, MIN(Command) AS Command FROM #SessionLog WHERE SPID = @@SPID;
DROP TABLE #SessionLog;

The command is SELECT INTO because the capture is itself the request that runs. The INSERT route with the old procedure is in Capture sp_who2 Output Into a Table With INSERT EXEC. Its parameters are in Parameters of sp_who2: Active, Session ID and the Login Trap.

Permissions

Reading these views needs the VIEW SERVER STATE permission, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. Without it a login sees only its own session. A monitoring login with that single permission is safer than a login in the sysadmin role.

You could argue that sp_who2 is quicker, because it is one word. That is true for a glance. The DMV version of sp_who2 takes a minute to paste once. After that it shows only user sessions, adds a wait type, and joins to the statement text on demand.

What to Remember

Build the DMV version of sp_who2 on sys.dm_exec_sessions with a LEFT JOIN to sys.dm_exec_requests. Filter on is_user_process. Never filter on spid > 50. Read PhysicalIO as the counterpart of DiskIO, and add logical_reads when you need the logical work. Store the list with INTO when you need history, and add the capture time.

A session list is not a procedure you remember, it is a query you own.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.