自訂sp_who2加強版

　　最近遇到有台DB主機的CPU使用率常常滿載，觀察DB的活動監視器，發現有過多Connection似乎沒有colse，這是不好的現象，那得先找出相關的是哪些Connection囉，SQL 2000本身只提供sp_who與sp_who2這兩個遇存程序來觀察，但都沒提供相關Connection的陳述式的資訊，總不可能再一個一個透過DBCC INPUTBUFFER來查吧，先上網找找有沒有人寫好的加強版，有是有啦，不過不是很合自己用，那就自己來自訂一個吧

--只適用SQL 2000，若要用在2005以後版本請看最後一行
USE [master]
GO
IF object_id('sp_who3') IS NOT NULL
BEGIN
DROP PROC sp_who3
END
GO

CREATE PROC [dbo].[sp_who3]
@loginame sysname = NULL
AS
--參數用法有3種，如下：
--exec sp_who3
--exec sp_who3 '14'
--　指定spid，只會列出該資料
--exec sp_who3 'active'
--　指定 ACTIVE。ACTIVE 會從處理序報表中，排除那些等待使用者下一個指令的處理序。
BEGIN

SET NOCOUNT ON

--去除參數頭尾空白
SET @loginame = NULLIF(RTRIM(LTRIM(UPPER(@loginame))), '')

--取得sysprocesses相關資訊後，插入暫存#sp_who3
SELECT sp.spid as 'spid',
sp.status as 'status',
sp.loginame as 'loginame',
NULLIF(RTRIM(LTRIM(sp.hostname)), '') as 'hostname',
sp.blocked as 'blocked',
sd.name as 'dbname',
sp.cmd as 'cmd',
sp.cpu as 'cpu',
sp.physical_io as 'physical_io',
sp.memusage as 'memusage',
sp.open_tran as 'open_tran',
sp.last_batch as 'last_batch',
sp.login_time as 'login_time',
NULLIF(RTRIM(LTRIM(sp.program_name)), '') as 'program_name',
cast('NULL' as nvarchar(255)) as 'eventinfo'
into #sp_who3
FROM master..sysprocesses sp
LEFT OUTER JOIN master..sysdatabases sd ON sd.dbid = sp.dbid

--建立暫存table，儲存dbcc inputbuffer資訊用
CREATE TABLE #inputbuffer (
EventType nvarchar(30),
Parameters Int,
EventInfo nvarchar(255)
)

-- 如果參數指定為ACTIVE則排除那些等待使用者下一個指令的處理序
-- (仿照dbo.sp_who2裡的作法)
IF (@loginame = 'ACTIVE')
DELETE #sp_who3
where LOWER(status) = 'sleeping'
and UPPER(cmd) IN ('AWAITING COMMAND',
'MIRROR HANDLER',
'LAZY WRITER',
'CHECKPOINT SLEEP',
'RA MANAGER')
and blocked = 0

-- 以下是取得額外dbcc inputbuffer的資訊用的
-- 像是T-SQL的內容，這補充dbo.sp_who2所不足的地方
-- cursor Start
DECLARE @spid varchar(30)

DECLARE cur_dbccinputbuffer CURSOR FOR
SELECT CONVERT(VARCHAR(30), spid) FROM #sp_who3

OPEN cur_dbccinputbuffer

FETCH NEXT FROM cur_dbccinputbuffer INTO @spid

WHILE @@fetch_status = 0
BEGIN

TRUNCATE TABLE #inputbuffer

INSERT INTO #inputbuffer
exec('dbcc inputbuffer(' + @spid + ')')

UPDATE #sp_who3
SET eventinfo = (SELECT EventInfo FROM #inputbuffer)
WHERE spid = @spid

FETCH NEXT FROM cur_dbccinputbuffer INTO @spid

END

CLOSE cur_dbccinputbuffer
DEALLOCATE cur_dbccinputbuffer

-- cursor End

IF @loginame IS NULL
BEGIN
SELECT *
FROM #sp_who3
ORDER BY spid
END
ELSE IF @loginame = 'ACTIVE'
BEGIN
SELECT *
FROM #sp_who3
ORDER BY spid
END
ELSE IF isnumeric(@loginame) = 1
BEGIN
SELECT *
FROM #sp_who3
WHERE spid = cast(@loginame as int)
END
RETURN 0
END

若要用在SQL 2005上，請將nvarchar(255)取代為nvarchar(max)即可