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 
