SQL Server provides several built-in system stored procedures and dynamic management views (DMVs) to monitor activity, diagnose issues, and manage sessions. Here’s a breakdown of sp_who2, its alternatives, and more advanced tools you can use:

sp_who2: Quick Session Overview

• Purpose: Lists current user sessions, processes, and resource usage.
• Output: Includes SPID, status, login, hostname, database, command, CPU time, I/O, and blocking info.
• Limitation: Cannot be filtered directly; lacks flexibility.

You can log its output into a temp table for filtering:

DECLARE @who2 TABLE (
    SPID INT, Status VARCHAR(50), Login VARCHAR(100), HostName VARCHAR(100),
    BlkBy VARCHAR(10), DBName VARCHAR(100), Command VARCHAR(100),
    CPUTime INT, DiskIO INT, LastBatch VARCHAR(100), ProgramName VARCHAR(100),
    SPID2 INT, RequestID INT
);
INSERT INTO @who2 EXEC sp_who2;
SELECT * FROM @who2 WHERE DBName = 'YourDatabase';

More on this: Log and Filter sp_who2 Results

Alternatives to sp_who2

sp_who

• Older version of sp_who2
• Less detailed output

sys.dm_exec_sessions and sys.dm_exec_requests

• DMVs that provide detailed, real-time session and request info
• Fully filterable and joinable

SELECT s.session_id, s.login_name, r.status, r.command, r.cpu_time, r.blocking_session_id
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id;

sp_whoisactive (by Adam Machanic)

• Community-developed stored procedure
• Highly customisable, filterable, and widely used by DBAs
• Not built-in, but easy to install

More info: sp_whoisactive GitHub

When to Use What

Tool	Best For	Flexibility	Built-In
sp_who2	Quick glance at sessions	Low	Yes
sys.dm_exec_*	Custom queries and deep analysis	High	Yes
sp_whoisactive	Advanced diagnostics and logging	Very High	No (install manually)

From my SQL Server DBA handbook.