Poll SPID Performance

Want to know exactly where one SPID spends its time? This script polls sysprocesses for a given session every second, accumulating samples of each wait type it encounters along with cumulative CPU and physical I/O - and stops when the session goes to sleep. The result is a per-wait-type breakdown plus total execution stats for that request's lifetime.

Set @SpidToMonitor at the top before running.

SET NOCOUNT ON;
GO
DECLARE @SpidToMonitor int = 137

DECLARE @WaitType varchar(128)
DECLARE @WaitTime int
DECLARE @Samples int = 0
DECLARE @Status varchar(128)
DECLARE @PreviousWaitType varchar(128) = ''
DECLARE @PreviousWaitTime int = 0
DECLARE @StartingCPU bigint
DECLARE @StartingPhysicalIO bigint
DECLARE @CPU bigint
DECLARE @PhysicalIO bigint

DECLARE @WaitStats table
(
	WaitType varchar(128),
	WaitTime int,
	Samples int
)

DECLARE @ExecStats table
(
	CPU bigint,
	PhysicalIO bigint,
	WaitTime bigint
)

SELECT
	@StartingCPU = cpu,
	@StartingPhysicalIO = physical_io
FROM
	sys.sysprocesses
WHERE
	spid = @SpidToMonitor

INSERT INTO @ExecStats(CPU, PhysicalIO) SELECT 0, 0

WHILE(1 = 1)
BEGIN--WHILE
	SELECT
		@WaitType = lastwaittype,
		@WaitTime = waittime,
		@CPU = cpu - @StartingCPU,
		@PhysicalIO = physical_io - @StartingPhysicalIO,
		@Status = RTRIM(LTRIM([status]))
	FROM
		sys.sysprocesses
	WHERE
		spid = @SpidToMonitor

	UPDATE @ExecStats SET CPU = @CPU, PhysicalIO = @PhysicalIO

	SET @Samples = IsNull(@Samples, 0) + 1

	IF(@WaitType != @PreviousWaitType OR @WaitTime < @PreviousWaitTime)
	BEGIN--IF
		IF(@PreviousWaitTime > 0)
		BEGIN--IF
			UPDATE @WaitStats SET Samples = Samples + @Samples, WaitTime = WaitTime + @PreviousWaitTime WHERE WaitType = @PreviousWaitType
			IF(@@ROWCOUNT = 0)
			BEGIN--IF
				INSERT INTO @WaitStats(Samples, WaitType, WaitTime) Values(@Samples, @PreviousWaitType, @PreviousWaitTime)
			END--IF
		END--IF

		SET @Samples = 0
	END--IF

	IF(@Status = 'sleeping')
	BEGIN--IF
		IF(@WaitTime > 0)
		BEGIN--IF
			UPDATE @WaitStats SET Samples = Samples + @Samples, WaitTime = WaitTime + @WaitTime WHERE WaitType = @WaitType
			IF(@@ROWCOUNT = 0)
			BEGIN--IF
				INSERT INTO @WaitStats(Samples, WaitType, WaitTime) Values(@Samples, @WaitType, @WaitTime)
			END--IF
		END--IF
	END--IF

	SET @PreviousWaitType = @WaitType
	SET @PreviousWaitTime = @WaitTime

	IF @Status = 'sleeping'
	BEGIN--IF
		BREAK
	END--IF

	WAITFOR DELAY '00:00:00:01'
END--WHILE

UPDATE @ExecStats SET WaitTime = (SELECT SUM(WaitTime) FROM @WaitStats)

SELECT * FROM @WaitStats
SELECT * FROM @ExecStats