SQL Server:过滤sp_who2的输出

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

当前回答

对阿斯坦德的回答略有改进。我喜欢把我的标准放在最上面，让它更容易日复一日地重用:

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)

2013-10-24 15:49:55

其他回答

我写在这里是为了将来自己使用。它使用sp_who2并插入到表变量中，而不是temp表，因为如果不删除temp表，它就不能使用两次。并显示阻塞和阻塞程序在同一行。

--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'

2020-07-14 00:23:13

对阿斯坦德的回答略有改进。我喜欢把我的标准放在最上面，让它更容易日复一日地重用:

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)

2013-10-24 15:49:55

基于http://web.archive.org/web/20080218124946/http://sqlserver2005.databases.aspfaq.com/how-do-i-mimic-sp-who2.html 我已经创建了以下脚本，它解决了使用DMV在sql 2005, 2008和2008R2下查找到任何数据库的活动连接

以下脚本使用sys. 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+'%'

2011-05-11 15:55:43

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

你可以随时刷新，因为它是EXCEL，一切都可以很容易地操纵!

2013-10-31 14:37:01

有相当多好的sp_who3用户存储过程——我相信Adam Machanic做了一个非常好的存储过程，AFAIK。

亚当称之为“谁活跃”: http://whoisactive.com

2010-02-10 09:10:07

SQL Server:过滤sp_who2的输出

推荐文章

最新文章

标签