SQLSERVER 中sp_who, sp_who2和sp_who3（转载）

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

 

 

原文链接