SQL Server：过滤 sp_who2 的输出

Question

在SQL Server下，有没有一种简单的方法来过滤sp_who2的输出？例如，假设我只想显示某个数据库的行。

Answer 1

您可以尝试类似的方法

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE ....

并过滤您需要的内容。

Answer 2

您可以保存将结果存入临时表，但最好直接转到 master.dbo.sysprocesses 上的源代码。

这是一个查询，它将返回与 sp_who2：

SELECT  spid,
        sp.[status],
        loginame [Login],
        hostname, 
        blocked BlkBy,
        sd.name DBName, 
        cmd Command,
        cpu CPUTime,
        physical_io DiskIO,
        last_batch LastBatch,
        [program_name] ProgramName   
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
ORDER BY spid 

现在您可以轻松添加任何您希望有意义的 ORDER BY 或 WHERE 子句输出。

---

或者，您可以考虑在 SSMS 中使用活动监视器 (Ctrl + Alt + A）

Answer 3

一种方法是创建临时表：

CREATE TABLE #sp_who2 
(
   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT
) 
GO

INSERT INTO #sp_who2
EXEC sp_who2
GO

SELECT *
FROM #sp_who2
WHERE Login = 'bla'
GO

DROP TABLE #sp_who2
GO

Answer 4

基于 http://web.archive.org/web/20080218124946/http://sqlserver2005.databases.aspfaq.com/how-do-i-mimic-sp-who2.html

我创建了以下脚本，
它解决了使用 DMV 查找到任何数据库的活动连接的问题，这在 sql 2005、2008 和 2008R2 下工作

以下脚本使用 sys.dm_exec_sessions ，sys.dm_exec_requests 、 sys.dm_exec_connections , sys.dm_tran_locks

Declare @dbName varchar(1000)
set @dbName='abc'

;WITH DBConn(SPID,[Status],[Login],HostName,DBName,Command,LastBatch,ProgramName)
As
(
SELECT 
    SPID = s.session_id,
    Status = UPPER(COALESCE
        (
            r.status,
            ot.task_state,
            s.status, 
        '')),
    [Login] = s.login_name,
    HostName = COALESCE
        (
            s.[host_name],
            '  .'
        ),
    DBName = COALESCE
        (
            DB_NAME(COALESCE
            (
                r.database_id,
                t.database_id
            )),
            ''
        ),
    Command = COALESCE
        (
            r.Command,
            r.wait_type,
            wt.wait_type,
            r.last_wait_type,
            ''
        ),
    LastBatch = COALESCE
        (
            r.start_time,
            s.last_request_start_time
        ),
    ProgramName = COALESCE
        (
            s.program_name, 
            ''
        )
FROM
    sys.dm_exec_sessions s
LEFT OUTER JOIN
    sys.dm_exec_requests r
ON
    s.session_id = r.session_id
LEFT OUTER JOIN
    sys.dm_exec_connections c
ON
    s.session_id = c.session_id
LEFT OUTER JOIN
(
    SELECT 
        request_session_id,
        database_id = MAX(resource_database_id)
    FROM
        sys.dm_tran_locks
    GROUP BY
        request_session_id
) t
ON
    s.session_id = t.request_session_id
LEFT OUTER JOIN
    sys.dm_os_waiting_tasks wt
ON 
    s.session_id = wt.session_id
LEFT OUTER JOIN
    sys.dm_os_tasks ot
ON 
    s.session_id = ot.session_id
LEFT OUTER JOIN
(
    SELECT
        ot.session_id,
        CPU_Time = MAX(usermode_time)
    FROM
        sys.dm_os_tasks ot
    INNER JOIN
        sys.dm_os_workers ow
    ON
        ot.worker_address = ow.worker_address
    INNER JOIN
        sys.dm_os_threads oth
    ON
        ow.thread_address = oth.thread_address
    GROUP BY
        ot.session_id
) tt
ON
    s.session_id = tt.session_id
WHERE
    COALESCE
    (
        r.command,
        r.wait_type,
        wt.wait_type,
        r.last_wait_type,
        'a'
    ) >= COALESCE
    (
        '', 
        'a'
    )
)

Select * from DBConn
where DBName like '%'+@dbName+'%'

Answer 5

对 Astander 的回答略有改进。我喜欢将我的标准放在首位，并使其更容易日常重用：

DECLARE @Spid INT, @Status VARCHAR(MAX), @Login VARCHAR(MAX), @HostName VARCHAR(MAX), @BlkBy VARCHAR(MAX), @DBName VARCHAR(MAX), @Command VARCHAR(MAX), @CPUTime INT, @DiskIO INT, @LastBatch VARCHAR(MAX), @ProgramName VARCHAR(MAX), @SPID_1 INT, @REQUESTID INT

    --SET @SPID = 10
    --SET @Status = 'BACKGROUND'
    --SET @LOGIN = 'sa'
    --SET @HostName = 'MSSQL-1'
    --SET @BlkBy = 0
    --SET @DBName = 'master'
    --SET @Command = 'SELECT INTO'
    --SET @CPUTime = 1000
    --SET @DiskIO = 1000
    --SET @LastBatch = '10/24 10:00:00'
    --SET @ProgramName = 'Microsoft SQL Server Management Studio - Query'
    --SET @SPID_1 = 10
    --SET @REQUESTID = 0

    SET NOCOUNT ON 
    DECLARE @Table TABLE(
            SPID INT,
            Status VARCHAR(MAX),
            LOGIN VARCHAR(MAX),
            HostName VARCHAR(MAX),
            BlkBy VARCHAR(MAX),
            DBName VARCHAR(MAX),
            Command VARCHAR(MAX),
            CPUTime INT,
            DiskIO INT,
            LastBatch VARCHAR(MAX),
            ProgramName VARCHAR(MAX),
            SPID_1 INT,
            REQUESTID INT
    )
    INSERT INTO @Table EXEC sp_who2
    SET NOCOUNT OFF
    SELECT  *
    FROM    @Table
    WHERE
    (@Spid IS NULL OR SPID = @Spid)
    AND (@Status IS NULL OR Status = @Status)
    AND (@Login IS NULL OR Login = @Login)
    AND (@HostName IS NULL OR HostName = @HostName)
    AND (@BlkBy IS NULL OR BlkBy = @BlkBy)
    AND (@DBName IS NULL OR DBName = @DBName)
    AND (@Command IS NULL OR Command = @Command)
    AND (@CPUTime IS NULL OR CPUTime >= @CPUTime)
    AND (@DiskIO IS NULL OR DiskIO >= @DiskIO)
    AND (@LastBatch IS NULL OR LastBatch >= @LastBatch)
    AND (@ProgramName IS NULL OR ProgramName = @ProgramName)
    AND (@SPID_1 IS NULL OR SPID_1 = @SPID_1)
    AND (@REQUESTID IS NULL OR REQUESTID = @REQUESTID)

Answer 6

类似于 KyleMit 答案，可以直接选择 SP_WHO2 使用的表，尽管我认为它只需要 dbo.sysprocesses 表。

如果有人打开这个SP，它就能明白它的作用。这是我最好的选择，具有与 SP_WHO2 类似的输出。

select convert(char(5),sp.spid) as SPID
        ,  CASE lower(sp.status)
                 When 'sleeping' Then lower(sp.status)
                 Else  upper(sp.status)
              END as Status
        , convert(sysname, rtrim(sp.loginame)) as LOGIN
        , CASE sp.hostname
                 When Null  Then '  .'
                 When ' ' Then '  .'
                 Else    rtrim(sp.hostname)
              END as HostName
        , CASE isnull(convert(char(5),sp.blocked),'0')
                 When '0' Then '  .'
                 Else isnull(convert(char(5),sp.blocked),'0')
              END as BlkBy
        , case when sp.dbid = 0 then null when sp.dbid <> 0 then db_name(sp.dbid) end as DBName
        , sp.cmd as Command
        , sp.cpu as CPUTime
        , sp.physical_io as DiskIO
        , sp.last_batch as LastBatch
        , sp.program_name as ProgramName 
        from master.dbo.sysprocesses sp (nolock)
  ;

通过此选择，您可以选择所需的字段并具有所需的顺序。

Answer 7

那里有很多很好的 sp_who3 用户存储过程 - 我确信 Adam Machanic 做得非常好，AFAIK。

Adam 将其称为“谁是活跃的”：
http://whoisactive.com

Answer 8

第一个也是最佳答案的扩展...我在主数据库上创建了一个存储过程，然后您可以将参数传递给..例如数据库的名称：

USE master
GO

CREATE PROCEDURE sp_who_db
(
    @sDBName varchar(200)   = null,
    @sStatus varchar(200)   = null,
    @sCommand varchar(200)  = null,
    @nCPUTime int           = null
)
AS
DECLARE @Table TABLE
(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT  *
    FROM    @Table
    WHERE   (@sDBName IS NULL OR DBName = @sDBName)
    AND     (@sStatus IS NULL OR Status = @sStatus)
    AND     (@sCommand IS NULL OR Command = @sCommand)
    AND     (@nCPUTime IS NULL OR CPUTime > @nCPUTime)
GO 

我可能会扩展它以按参数添加订单，甚至是Kill paramatmer 因此它会终止与特定数据的所有连接

Answer 9

是的，通过将 sp_who2 的输出捕获到表中，然后从表中进行选择，但这将是一个糟糕的方法。首先，因为 sp_who2 尽管很受欢迎，但它是一个未记录的过程，因此您不应该依赖未记录的过程。其次，因为 sp_who2 的所有功能以及更多功能都可以从 sys.dm_exec_requests 获取 和其他 DMV，并且可以对显示进行过滤、排序、连接以及可查询行集附带的所有其他功能。

Answer 10

一种非常简单的方法是在 EXCEL 中创建 ODBC 链接并从那里运行 SP_WHO2。

您可以随时刷新，因为它是 EXCEL，所以一切都可以轻松操作！

Answer 11

我做了一个改进，以便不仅可以获得阻塞进程，还可以获取阻塞进程：

DECLARE @Table TABLE
    (
    SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT
    )

INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE
    BlkBy not like '  .'
    or
    SPID in (SELECT  BlkBy from @Table where BlkBy not like '  .')

delete from @Table

Answer 12

我写在这里是为了将来自己使用。它使用 sp_who2 并插入表变量而不是临时表，因为如果不删除临时表，则不能使用两次。
并在同一行显示被阻止和阻止者。

--blocked: waiting becaused blocked by blocker
--blocker: caused blocking
declare @sp_who2 table(
    SPID int,
    Status varchar(max),
    Login varchar(max),
    HostName varchar(max),
    BlkBy varchar(max),
    DBName varchar(max),
    Command varchar(max),
    CPUTime int,
    DiskIO int,
    LastBatch varchar(max),
    ProgramName varchar(max),
    SPID_2 int,
    REQUESTID int
)
insert into @sp_who2 exec sp_who2
select  w.SPID blocked_spid, w.BlkBy blocker_spid, tblocked.text blocked_text, tblocker.text blocker_text
from    @sp_who2 w
        inner join sys.sysprocesses pblocked on w.SPID = pblocked.spid
        cross apply sys.dm_exec_sql_text(pblocked.sql_handle) tblocked
        inner join sys.sysprocesses pblocker on case when w.BlkBy = '  .' then 0 else cast(w.BlkBy as int) end = pblocker.spid
        cross apply sys.dm_exec_sql_text(pblocker.sql_handle) tblocker
where   pblocked.Status = 'SUSPENDED'

Answer 13

这是适合您的解决方案：

select * from openrowset ('SQLOLEDB', '192.168.x.x\DATA'; 'user'; 'password', 'sp_who')