Technical Article

usp_SQL_In_sp_who2

,

Like most DBAs I frequently use sp_who2 to monitor activity at any given moment. Many times however I would identify a SPID that had a high CPU utilization or came from an unkown host and I would wish that I could see the SQL running under that SPID without having to fire up SQL Profiler.

I finally did some research and found that the DBCC INPUTBUFFER(SPID) would return what I was looking for. The input buffer doesn't always contain SQL, but for the SPIDs I have questions about it usually does. I coded a stored procedure, usp_SQL_In_sp_who2, to include the contents of the Input buffer and the length of the input buffer which I find useful in identifying at-a-glance monster queries.

Brandon Forest
SQL Server DBA

USE [master]
GO

/****** Object: StoredProcedure [dbo].[usp_SQL_In_sp_who2] Script Date: 03/27/2009 10:00:22 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SQL_In_sp_who2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SQL_In_sp_who2]
GO

/****** Object: StoredProcedure [dbo].[usp_SQL_In_sp_who2] Script Date: 03/27/2009 10:00:22 ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[usp_SQL_In_sp_who2]
As
/**************************************************************************************************
*    Procedure: usp_SQL_In_sp_who2
*    Parameter: none
*    Purpose : Ammends resultset from sp_who2 with inputbuffer contents (usually a SQL statement).
*    Author     : Brandon Forest
*    Created : 11/21/2008
*    Last Mod : 11/25/2008; 2/26/2009
*  Mod By     : Brandon Forest
***************************************************************************************************/If Exists(Select Name From Sys.Objects Where Name = '#tmp1')
    BEGIN Drop Table #tmp1 END

Declare @stg_sp_who2 TABLE
(
    [stgKey] [int] IDENTITY(1,1) NOT NULL,
    [SQLStatement] [varchar](4000) NULL,
    [SPID] [int] NULL,
    [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] NULL,
    [RequestID] [int] NULL,
    [RecDate] [datetime] NULL
)

Create table #tmp1 (EventType nvarchar(100), Parms nvarchar(10), EventInfo nvarchar(4000), id int)    

INSERT Into @stg_sp_who2 
([SPID], [Status], [Login], [HostName], [BlkBy], [DBName], [Command], [CPUTime], [DiskIO], [LastBatch], [ProgramName], [SPID2], [RequestID])
EXEC sp_who2 
Update @stg_sp_who2
Set RecDate = GETDATE()

--Start Cursor
Declare @spid varchar(10)
Declare @Statement varchar(8000)
Declare @sql varchar(1000)
DECLARE SpidCursor Cursor 
    FOR Select spid from @stg_sp_who2
OPEN SpidCursor
FETCH NEXT FROM SpidCursor INTO @spid
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @sql = 'dbcc inputbuffer (' + @spid + ')' 
    Insert #tmp1
    (EventType, Parms, EventInfo) 
    exec (@sql) 
    Update #tmp1 
    Set id = @spid 
    Update @stg_sp_who2
    Set [SQLStatement] = (Select EventInfo From #tmp1 Where ID = @spid) Where [SPID] = @spid
    Truncate Table #tmp1
 FETCH NEXT FROM SpidCursor INTO @spid
END
Close SpidCursor
Deallocate SpidCursor

Select 
    Len(SQLStatement) As LenSQLStatement
    , SQLStatement
    , SPID
    , Status
    , Login
    , HostName
    , BlkBy
    , DBName
    , Command
    , CPUTime
    , DiskIO
    , LastBatch
    , ProgramName
    , RecDate
From @stg_sp_who2
Order By DBName;

GO

Rate

4 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (5)

You rated this post out of 5. Change rating