Friday, January 09, 2015

sp_who2 with sql text

SQL Server has an undocumented stored procedure sp_who2 that provides information about the connections to the database.

Recently I have been using sp_who2 to track down connections that are "suspended" which in my case means that our MS-Access application is not processing all of the requested data in a timely manner. This can interfere with other users, even though there is no actual blocking going on. Another issue that we're seeing is that users will log-in to their terminal server environment, launch the application & then leave it open for days at a time. Even though the connections are sleeping, they still require some resources on the SQL server.

The task at-hand is 1) determine which connections (SPID's) are suspended & what SQL was running, and 2) determine which connections have been sleeping for more than a day. The first item will reveal the SQL so we can find an alternate solution; the second will allow us to identify the sleepers so we can close them out.

I started by creating a temp table & inserting the sp output, and then running some code to look up the SQL text. . .

DECLARE
    -- set this to the name of your database
    @database sysname = 'my_database',
    @not_sleeping int,
    @not_today int,
    @spid int,
    @sqltext varbinary(128),
    @sql_command nvarchar(4000),
    @today nvarchar(50)

-- LastBatch only provides month & day
SELECT @today = LEFT(CONVERT(nvarchar,GETDATE(),101),5)

-- create temp table to receive sp output, and execute
IF OBJECT_ID(N'tempdb..#sp_who2', N'U') IS NOT NULL
DROP TABLE #sp_who2

SET NOCOUNT ON

CREATE TABLE #sp_who2
    (
    SPID int,
    Status nvarchar(1000) NULL,
    Login sysname NULL,
    HostName sysname NULL,
    BlkBy sysname NULL,
    DBName sysname NULL,
    Command nvarchar(1000) NULL,
    CPUTime int NULL,
    DiskIO int NULL,
    LastBatch nvarchar(1000) NULL,
    ProgramName nvarchar(1000) NULL,
    SPID2 int,
    REQUESTID int NULL
    )

INSERT #sp_who2
EXEC sp_who2

-- look up the sql text for each SPID
ALTER TABLE #sp_who2 ADD sql_command nvarchar(4000)
-----------------------------------------------------------
WHILE EXISTS (SELECT 1 FROM #sp_who2 WHERE sql_command IS NULL)
BEGIN
    SELECT @spid = spid FROM #sp_who2 WHERE sql_command IS NULL

    SELECT @sqltext = sql_handle
    FROM sys.sysprocesses
    WHERE spid = @spid

    SELECT @sql_command = TEXT
    FROM sys.dm_exec_sql_text(@sqltext)

    UPDATE #sp_who2
    SET sql_command = @sql_command
    WHERE spid = @spid
END
-----------------------------------------------------------
SELECT @not_sleeping = COUNT(*)
FROM #sp_who2
WHERE DBName = @database
 AND status <> 'sleeping'

SELECT @not_today = COUNT(*)
FROM #sp_who2
WHERE DBName = @database
 AND    LEFT(LastBatch,5) <> @today

-- display a list of all SPID's of interest
IF @not_sleeping <> 0 OR @not_today <> 0
BEGIN
    SELECT
        SPID,
        Status,
        Login,
        HostName,
        BlkBy,
        DBName,
        Command,
        CPUTime,
        DiskIO,
        LastBatch,
        ProgramName,
        SPID2,
        REQUESTID,
        sql_command
    FROM
        #sp_who2
    WHERE
        DBName = @database
     AND    (
        Status <> 'sleeping'
        OR
        LEFT(LastBatch,5) <> @today
        )
END
---------------------------------------

DROP TABLE #sp_who2