--#region #sp_whoiocomp
RETURN
IF OBJECT_ID('tempdb..#sp_whoiocomp') IS NOT NULL DROP TABLE #sp_whoiocomp
CREATE TABLE #sp_whoiocomp (
[spid] SMALLINT NOT NULL,
[blocking] XML NULL,
[blocked] XML NULL,
[Threads] VARCHAR(30) NULL,
[status] VARCHAR(MAX) NULL,
[lastwaittype] VARCHAR(MAX) NULL,
[database] SYSNAME NULL,
[hostname] VARCHAR(128) NULL,
[loginame] VARCHAR(128) NULL,
[program_name] VARCHAR(128) NULL,
[cmd] VARCHAR(MAX) NULL,
[cpu] BIGINT NULL,
[physical_io] BIGINT NULL,
[io_delta] BIGINT NULL,
[cpu_delta] BIGINT NULL,
[memusage] VARCHAR(30) NULL,
[waittime] BIGINT NULL,
[wait_delta] BIGINT NULL,
[grant_mem_mb] NUMERIC(12, 2) NULL,
[req_mem_mb] NUMERIC(12, 2) NULL,
[used_mem_mb] NUMERIC(12, 2) NULL,
[open_tran] VARCHAR(30) NULL,
[dbid] SMALLINT NULL,
[waitresource] XML NULL,
[blockingresource] XML NULL,
[last_batch] DATETIME NULL,
[last_batch_sec] INT NULL,
[context_info] VARBINARY(128) NULL,
[Inputbuffer] XML NULL,
[SQLBatch] XML NULL,
[SQLStmt] XML NULL)
GO
--#endregion
--#region #sp_whoio
IF OBJECT_ID('tempdb..#sp_whoio') IS NOT NULL DROP TABLE #sp_whoio
CREATE TABLE #sp_whoio (
[spid] SMALLINT NOT NULL,
[blocking] XML NULL,
[blocked] XML NULL,
[Threads] VARCHAR(30) NULL,
[status] VARCHAR(MAX) NULL,
[lastwaittype] VARCHAR(MAX) NULL,
[database] SYSNAME NULL,
[hostname] VARCHAR(128) NULL,
[loginame] VARCHAR(128) NULL,
[program_name] VARCHAR(128) NULL,
[cmd] VARCHAR(MAX) NULL,
[cpu] BIGINT NULL,
[physical_io] BIGINT NULL,
[io_delta] BIGINT NULL,
[cpu_delta] BIGINT NULL,
[memusage] VARCHAR(30) NULL,
[waittime] BIGINT NULL,
[wait_delta] BIGINT NULL,
[grant_mem_mb] NUMERIC(12, 2) NULL,
[req_mem_mb] NUMERIC(12, 2) NULL,
[used_mem_mb] NUMERIC(12, 2) NULL,
[open_tran] VARCHAR(30) NULL,
[dbid] SMALLINT NULL,
[waitresource] XML NULL,
[blockingresource] XML NULL,
[last_batch] DATETIME NULL,
[last_batch_sec] INT NULL,
[context_info] VARBINARY(128) NULL,
[Inputbuffer] XML NULL,
[SQLBatch] XML NULL,
[SQLStmt] XML NULL)
--#endregion
GO
EXEC dbo.sp_whoio
@delay = 1,
@ignoresleeping = 1,
@showonlyblocked = 0,
@DbccInputbuffer = 1,
@IgnoreSystem = 1,
@GetResourceDescription = 1,
@Get_os_wait_stats = 0
GO
--SELECT *
--FROM #sp_whoio
--WHERE spid IN (65,75)
--#region INSERT #sp_whoiocomp
INSERT #sp_whoiocomp ([spid], [blocking], [blocked], [Threads], [status], [lastwaittype], [database], [hostname], [loginame], [program_name], [cmd], [cpu], [physical_io], [io_delta], [cpu_delta], [memusage], [waittime], [wait_delta], [grant_mem_mb], [req_mem_mb], [used_mem_mb], [open_tran], [dbid], [waitresource], [blockingresource], [last_batch], [last_batch_sec], [Inputbuffer], [SQLBatch], [SQLStmt])
SELECT [spid], [blocking], [blocked], [Threads], [status], [lastwaittype], [database], [hostname], [loginame], [program_name], [cmd], [cpu], [physical_io], [io_delta], [cpu_delta], [memusage], [waittime], [wait_delta], [grant_mem_mb], [req_mem_mb], [used_mem_mb], [open_tran], [dbid], [waitresource], [blockingresource], [last_batch], [last_batch_sec], [Inputbuffer], [SQLBatch], [SQLStmt]
FROM #sp_whoio
WHERE SPID IN (62,63)
GO
--#endregion
SELECT [spid], [grant_mem_mb], [req_mem_mb], [used_mem_mb], [Inputbuffer] FROM #sp_whoiocomp