Below SP_PSWHO procedure is very useful for those who are supporting SQL SERVER databases with PeopleSoft, it gives you user, wait type, read blocks, blocking and much more.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- exec SP_PSWHO 'all'
-- exec SP_PSWHO
--DROP PROC SP_PSWHO
GO
CREATE PROC SP_PSWHO ( @all char (5) = 'NALL')
AS
SET NOCOUNT ON
CREATE TABLE #processes (
spid smallint NOT NULL ,
waittime int NOT NULL ,
waittype binary(2),
cpu int NOT NULL ,
physical_io bigint NOT NULL ,
login_time datetime NOT NULL ,
hostname char (128) NOT NULL ,
program_name char (128) NOT NULL ,
nt_domain char (128) NOT NULL ,
nt_username char (128) NOT NULL ,
loginame char (128) NOT NULL ,
context_info varchar(36),
sql_handle binary (20) ,
stmt_text text NULL
)
DECLARE
@spid smallint ,
@waittime int ,
@waittype binary(2),
@cpu int ,
@physical_io bigint ,
@login_time datetime,
@hostname char (128),
@program_name char (128),
@nt_domain char (128) ,
@nt_username char (128) ,
@loginame char (128),
@context_info VARCHAR(36),
@sql_handle binary (20) ,
@stmt_text VARCHAR(8000)
SELECT @all = upper(@all)
IF @all <> 'ALL'
begin
DECLARE PROCESSES CURSOR FOR
SELECT
spid,
waittime,
waittype,
cpu,
physical_io,
login_time,
hostname,
program_name,
nt_domain,
nt_username,
loginame,
convert(VARCHAR(36),context_info),
sql_handle,
cmd
FROM master..sysprocesses
WHERE dbid = db_id()
-- AND context_info <> 0X0
AND CONVERT(VARCHAR(36),context_info) NOT LIKE 'PSAPPS%'
AND spid <> @@spid
AND cmd <> 'AWAITING COMMAND'
end
else
begin
DECLARE PROCESSES CURSOR FOR
SELECT
spid,
waittime,
waittype,
cpu,
physical_io,
login_time,
hostname,
program_name,
nt_domain,
nt_username,
loginame,
convert(VARCHAR(36),context_info),
sql_handle,
cmd
FROM master..sysprocesses
WHERE dbid = db_id()
-- AND context_info <> 0X0
-- AND CONVERT(VARCHAR(36),context_info) NOT LIKE 'PSAPPS%'
AND spid <> @@spid
end
OPEN PROCESSES
FETCH PROCESSES
INTO @spid,
@waittime,
@waittype,
@cpu,
@physical_io,
@login_time,
@hostname,
@program_name,
@nt_domain,
@nt_username,
@loginame,
@context_info,
@sql_handle,
@stmt_text
WHILE @@FETCH_STATUS = 0
BEGIN
IF @sql_handle <> 0x0 AND IS_SRVROLEMEMBER ('sysadmin') = 1
SELECT @stmt_text = text from ::fn_get_sql(@sql_handle)
INSERT INTO #processes
VALUES( @spid,
@waittime,
@waittype,
@cpu,
@physical_io,
@login_time,
@hostname,
@program_name,
@nt_domain,
@nt_username,
@loginame,
@context_info,
@sql_handle,
@stmt_text
)
FETCH PROCESSES
INTO @spid,
@waittime,
@waittype,
@cpu,
@physical_io,
@login_time,
@hostname,
@program_name,
@nt_domain,
@nt_username,
@loginame,
@context_info,
@sql_handle,
@stmt_text
END
DEALLOCATE PROCESSES
SELECT --SUBSTRING(context_info,1,(charindex(',',context_info)-1)) AS [OPRID],
context_info AS [OPRID],
program_name AS Application,
Server = hostname,
SPID = spid,
login_time AS [Start Time],
[Wait(ms)] = (waittime/1000),
Waittype = case waittype
WHEN 0x001 THEN 'Lock: Sch-S'
WHEN 0x002 THEN 'Lock: Sch-M'
WHEN 0x003 THEN 'Lock: S'
WHEN 0x004 THEN 'Lock: U'
WHEN 0x005 THEN 'Lock: X'
WHEN 0x006 THEN 'Lock: IS'
WHEN 0x007 THEN 'Lock: IU'
WHEN 0x008 THEN 'Lock: IX'
WHEN 0x009 THEN 'Lock: SIU'
WHEN 0x00A THEN 'Lock: SIX'
WHEN 0x00B THEN 'Lock: UIX'
WHEN 0x00C THEN 'Lock: BU'
WHEN 0x00D THEN 'Lock: RangeS_S'
WHEN 0x00E THEN 'Lock: RangeS_U'
WHEN 0x00F THEN 'Lock: RangeI_N'
WHEN 0x010 THEN 'Lock: RangeI_S'
WHEN 0x011 THEN 'Lock: RangeI_U'
WHEN 0x012 THEN 'Lock: RangeI_X'
WHEN 0x013 THEN 'Lock: RangeX_S'
WHEN 0x014 THEN 'Lock: RangeX_U'
WHEN 0x015 THEN 'Lock: RangeX_X'
WHEN 0x20 THEN 'IO Completion – SLEEP'
WHEN 0x21 THEN 'IO Completion'
WHEN 0x22 THEN 'Async IO Completion'
WHEN 0x40 THEN 'Resource Semaphone'
WHEN 0x41 THEN 'DTC'
WHEN 0x42 THEN 'OLEDB Provider'
WHEN 0x43 THEN 'FAILPOINT'
WHEN 0x44 THEN 'Resource Queue'
WHEN 0x45 THEN 'Async DiskPool Lock'
WHEN 0x46 THEN 'UMS thread pooling'
WHEN 0x47 THEN 'Pipeline Index Stat'
WHEN 0x48 THEN 'Pipeline Log'
WHEN 0x49 THEN 'Pipeline VLM'
WHEN 0x81 THEN 'Waiting on Writelog'
WHEN 0x100 THEN 'PSSBIT'
WHEN 0x101 THEN 'PSS Child'
WHEN 0x200 THEN 'Exchange synch up'
WHEN 0x202 THEN 'DBTable for checkpoint'
WHEN 0x203 THEN 'Access to an EC'
WHEN 0x204 THEN 'Temporary Object Drop'
WHEN 0x205 THEN 'Xact Lock Info'
WHEN 0x206 THEN 'Log Manager'
WHEN 0x207 THEN 'C Memory Thread'
WHEN 0x208 THEN 'CX Packet List Parallel process'
WHEN 0x209 THEN 'Parallel Page Supplier'
WHEN 0x20A THEN 'Shutdown'
WHEN 0x20B THEN 'WAITFOR Command'
WHEN 0x20C THEN 'Async Cursors synch up'
WHEN 0x20D THEN 'General sync'
WHEN 0x400 THEN 'Latch NL'
WHEN 0x401 THEN 'Latch KP'
WHEN 0x402 THEN 'Latch SH'
WHEN 0x403 THEN 'Latch UP'
WHEN 0x404 THEN 'Latch EX'
WHEN 0x405 THEN 'Latch DT'
WHEN 0x410 THEN 'Pagelatch NL'
WHEN 0x411 THEN 'Pagelatch KP'
WHEN 0x412 THEN 'Pagelatch SH'
WHEN 0x413 THEN 'Pagelatch UP'
WHEN 0x414 THEN 'Pagelatch EX'
WHEN 0x415 THEN 'Pagelatch DT'
WHEN 0x420 THEN 'PageIOLatch NL'
WHEN 0x421 THEN 'PageIOLatch KP'
WHEN 0x422 THEN 'PageIOLatch SH'
WHEN 0x423 THEN 'PageIOLatch UP'
WHEN 0x424 THEN 'PageIOLatch EX'
WHEN 0x425 THEN 'PageIOLatch DT'
WHEN 0x800 THEN 'Waiting on Network IO Completion'
END,
CPU = cpu,
'IO' = physical_io,
'SQL' = stmt_text
FROM #processes P
ORDER BY 1, 2
RETURN @@ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
