Indepth SPID information

,

This stored procedure will consolidate useful information about an active SPID from sys.syspr

NOTE: Since sys.dm_exec_requests is very dynamic you may not always get results for a particular SPID but this SP will usually show results for a SPID that has been running for sometime and perhaps waiting on some resource to be allocated. You may find this SP quite handy when you are troubleshooting a "stuck" or long running query.

Usage: EXEC OPS_SP_DetailSPID @SPID = < INSERT SPID >

USE MASTER
GO

IF OBJECT_ID ('DBO.OPS_SP_DetailSPID') IS NOT NULL
DROP PROC DBO.OPS_SP_DetailSPID 
GO

CREATE PROC DBO.OPS_SP_DetailSPID @spid int 
AS

SELECT
SP.SPID, SP.status Status, SP.cmd Command, A.percent_complete PercentComplete, 
SP.Blocked BlockedBy, DB_NAME(SP.dbid) AS DatabaseName, SP.hostname Hostname,
SP.physical_io Physical_IO, SP.cpu CPU, SP.waittype WaitType, (SP.waittime/1000) WaitTimeSec, 
SP.lastwaittype LastWaitType, SP.waitresource WaitResource,
SP.login_time LoginTime, A.total_elapsed_time/1000 BatchElapsedTimeSec,
(SELECT SUBSTRING(C.text,A.statement_start_offset/2,(CASE WHEN A.statement_end_offset = -1 
THEN LEN(CONVERT(nvarchar(max), C.text)) * 2 ELSE A.statement_end_offset END -A.statement_start_offset)/2)) SQLBatchText,
C.text SQLStatementText,
B.query_plan as QueryPlan
FROM sys.sysprocesses AS SP
INNER JOIN sys.dm_exec_requests as A
ON SP.SPID = A.session_id
CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) as B
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) as C
WHERE SP.spid = @spid

Rate

Share

Share

Rate