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