June 26, 2008 at 10:18 am
Does anybody know from where DBCC INPUTBUFFER(pid) gets its result? I mean it should be somewhere in a table ... (I'm trying to combine in a view using sysprocesses and the result of this command...)
Virgil Rucsandescu
July 7, 2008 at 1:37 am
I don't know of any nice clean way to join to it, but I've done the sort of thing I think you're after in fairly ugly ways with cursors. For example, here is something I run which takes snapshots ten seconds apart to see what processes are using most I/O:
ALTER PROC sp_SQLIOUsage(@to_table bit = 0, @showall bit = 0) AS
select *
into #temp1
FROM master..sysprocesses
WAITFOR DELAY '00:00:10'
select *
into #temp2
FROM master..sysprocesses
select DISTINCT #temp1.spid, #temp1.program_name, #temp1.cmd,
CAST(#temp1.hostname AS varchar(16)) AS hostname, #temp1.physical_io as IO1, #temp2.physical_io as IO2,
#temp2.physical_io - #temp1.physical_io AS IO_used
INTO #iores
from #temp1
join #temp2 on #temp2.spid = #temp1.spid and #temp2.kpid = #temp1.kpid
WHERE (@showall = 1 OR #temp2.physical_io - #temp1.physical_io > 100)
order by #temp2.physical_io - #temp1.physical_io desc
-- Run
create table #inputbuffer(spid int, EventType varchar(32), Parameters varchar(32), EventInfo varchar(256))
DECLARE proccursor CURSOR
READ_ONLY
FOR SELECT spid FROM #iores WHERE IO_used > 50
DECLARE @spid int
OPEN proccursor
FETCH NEXT FROM proccursor INTO @spid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
INSERT INTO #inputbuffer(EventType, Parameters, EventInfo)
EXEC sp_executesql N'dbcc inputbuffer(@spid)', N'@spid int', @spid
UPDATE #inputbuffer
SET spid = @spid
WHERE spid IS NULL
END
FETCH NEXT FROM proccursor INTO @spid
END
CLOSE proccursor
DEALLOCATE proccursor
IF @to_table = 1
BEGIN
INSERT INTO iores(spid, program_name, cmd, hostname, io1, io2, IO_used, eventinfo)
SELECT #iores.spid, program_name, cmd, CAST(hostname AS varchar(16)), io1, io2, IO_used, CAST(ISNULL(EventInfo, '-') AS varchar(256)) AS eventinfo
FROM #iores
LEFT JOIN #inputbuffer ON #inputbuffer.spid = #iores.spid
WHERE #iores.spid <> @@spid AND IO_used > 100
ORDER BY IO_used DESC
END ELSE BEGIN
SELECT #iores.spid, program_name, cmd, hostname, io1, io2, IO_used, ISNULL(EventInfo, '-') AS eventinfo
FROM #iores
LEFT JOIN #inputbuffer ON #inputbuffer.spid = #iores.spid
WHERE #iores.spid <> @@spid AND IO_used > 100
ORDER BY IO_used DESC
END
DROP TABLE #temp1, #temp2, #iores, #inputbuffer
GO
Note the bit starting DECLARE CURSOR - in this case the query delivers the SPIDs of all the processes with more than 50 I/Os and runs DBCC INPUTBUFFER into a temp table, which you can then join.
Tony
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply