ordenar - SQL Server: Salida de filtro de sp_who2

sql select sp_who2

Question

En SQL Server, ¿hay alguna manera fácil de filtrar el resultado de sp_who2? Supongamos que quiero mostrar filas para una determinada base de datos, por ejemplo.

Answer 1

De forma similar a la respuesta de KyleMit, es posible seleccionar directamente las tablas utilizadas por SP_WHO2, aunque creo que solo se necesita la tabla dbo.sysprocesses.

Si alguien abre este SP, puede entender lo que hace. Esta es mi mejor opción para tener un resultado similar a 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)
  ;

Sobre esta selección, puede seleccionar los campos que necesita y tener el orden que desea.

Answer 2

Esta es la solución para usted: http://blogs.technet.com/b/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx

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

Answer 3

Extensión de la primera y mejor respuesta ... He creado un procedimiento almacenado en la base de datos maestra a la que puede pasar parámetros ... como el nombre de la base de datos:

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 

Podría extenderlo para agregar un orden por parámetro o incluso un paramatmer kill para que mate todas las conexiones a un dato en particular

Answer 4

Hay bastantes buenos procedimientos almacenados por el usuario sp_who3, estoy seguro de que Adam Machanic hizo uno muy bueno, AFAIK.

Adam lo llama Who Is Active: http://sqlblog.com/blogs/adam_machanic/archive/tags/Scripts/default.aspx

Answer 5

Leve mejora a la respuesta de Astander. Me gusta poner mis criterios en la parte superior y hacer que sea más fácil reutilizarlo día a día:

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

Podrías probar algo como

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

Y filtra lo que requieres.

Answer 7

Puede guardar los resultados en una tabla temporal , pero sería aún mejor ir directamente a la fuente en master.dbo.sysprocesses

Lo he construido para producir casi exactamente lo mismo que sp_who2 genera, excepto que puede agregar fácilmente cláusulas ORDER BY y WHERE para obtener un resultado significativo.

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 

Answer 8

Sí, capturando la salida de sp_who2 en una tabla y luego seleccionando de la tabla, pero esa sería una mala forma de hacerlo. En primer lugar, porque sp_who2, a pesar de su popularidad, es un procedimiento no documentado y no debe confiar en los procedimientos no documentados. En segundo lugar, porque todo lo que sp_who2 puede hacer, y mucho más, puede obtenerse de sys.dm_exec_requests y de otros DMV, y se puede filtrar, ordenar, unir y mostrar todas las demás ventajas que vienen con conjuntos de filas queryables.

Answer 9

Una forma es crear una tabla temporal:

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 10

Una forma realmente fácil de hacerlo es crear un enlace ODBC en EXCEL y ejecutar SP_WHO2 desde allí.

Puede actualizar cuando quiera y porque es EXCEL, ¡todo puede manipularse fácilmente!

Answer 11

basado en http://sqlserver2005.databases.aspfaq.com/how-do-i-mimic-sp-who2.html

He creado la siguiente secuencia de comandos
que resuelve la búsqueda de conexiones activas a cualquier datbase usando DMV, esto funciona en sql 2005, 2008 y 2008R2

El siguiente script utiliza 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+''%''