sp_who可以返回如下信息： (可选参数LoginName, 或active代表活动会话数)
Spid (系统进程ID)
status (进程状态)
loginame (用户登录名)
hostname(用户主机名)
blk (阻塞进程的SPID)
dbname (进程正在使用的数据库名)
Cmd (当前正在执行的命令类型)
sp_who2除了显示上面sp_who的输出信息外，还显示下面的信息： (可选参数LoginName, 或active代表活动会话数)
CPUTime (进程占用的总CPU时间)
DiskIO (进程对磁盘读的总次数)
LastBatch (客户最后一次调用存储过程或者执行查询的时间)
ProgramName (用来初始化连接的应用程序名称，或者主机名)
sp_who3是某牛人自定义的存储过程，(可选参数spid)，显示“非系统会话，且是活动的会话”的详细情况
CREATE PROCEDURE sp_who3
(
@SessionID INT = NULL
)
AS
BEGIN
SELECT
SPID = er.session_id
,STATUS = ses.STATUS
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,SQLStatement = st.text
,ObjectName = OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme FROM
sys.dm_exec_requests er
OUTER APPLY
sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN
sys.dm_exec_sessions ses
ON
ses.session_id = er.session_id
LEFT JOIN
sys.dm_exec_connections con
ON
con.session_id = ses.session_id
WHERE
er.session_id > 50
AND
@SessionID IS NULL OR er.session_id = @SessionID
ORDER BY
er.blocking_session_id DESC
,er.session_id END
GO
下面是另外一个版本的sp_who3，只显示活动中的非系统会话，并且不会显示当前数据库连接自己的会话：
CREATE PROCEDURE [dbo].[sp_who3]
@SPID SMALLINT = NULL
AS
BEGIN
SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT er.session_id AS SPID
,er.blocking_session_id AS BlkBy
,er.total_elapsed_time AS ElapsedMS
,er.cpu_time AS CPU
,er.logical_reads + er.reads AS IOReads
,er.writes AS IOWrites
,ec.execution_count AS Executions
,er.command AS CommandType
,OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) AS ObjectName
,SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) AS SQLStatement
,ses.STATUS
,ses.login_name
,ses.host_name AS Host
,DB_Name(er.database_id) AS DBName
,er.last_wait_type AS LastWaitType
,er.start_time AS StartTime
,con.net_transport AS Protocol
,CASE ses.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation
,con.num_writes AS ConnectionWrites
,con.num_reads AS ConnectionReads
,con.client_net_address AS ClientAddress
,con.auth_scheme AS Authentication
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY
( SELECT execution_count = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans cp
WHERE cp.plan_handle = er.plan_handle
) ec
WHERE er.session_id = ISNULL(@SPID, er.session_id)
AND er.session_id != @@Spid
ORDER BY
er.blocking_session_id DESC,
er.logical_reads + er.reads DESC,
er.session_id ASC END
GO