Examines the execution context of a specified task, even within a deeply nested stored procedure execution.
sp_monitor 'procstack', 'spid' [, "context" [, '<type>'] ]
spid – the server ID of the task analyzed.
context – the number of SQL lines of context around the line of text executed for each nested stored procedure. The default value is 5 lines of context for each procedure.
type – reserved for future use.
Example 1 Generates the procedural stack for the current spid executing sp_monitor.
1> sp_monitor procstack, '17' 2> goNo blocks were found for SPID 17 Procedure stack trace for SPID 17: Nesting DBName OwnerName ObjectName ObjectID LineNumber Blocked ------- -------------- --------- -------------------- ---------- ---------- ------- 2 sybsystemprocs NULL sp_monitor_procstack 182288678 109 0 1 sybsystemprocs NULL sp_monitor 2064723377 364 0(2 rows affected) >>> SPID 17 [Nest: 2] Procedure 'sybsystemprocs..sp_monitor_procstack' at line number 109: <<< CREATE PROCEDURE sp_monitor_procstack Parameter_name Type Length Prec Scale Param_order Mode -------------- ------- ------ ---- ----- ----------- ---- @spid int 4 NULL NULL 1 in @context int 4 NULL NULL 2 in @type varchar 10 NULL NULL 3 in(1 row affected) 104 -- requesting, along with the locks held by the task that is 105 -- blocking this spid, if any. 106 -- 107 set switch on 1202 with override, no_info 108 109 >>> select @blocking_spid = p.blocked 110 , @linenum = linenum 111 , @stmtnum = stmtnum 112 from master.dbo.sysprocesses p 113 where p.spid = @spid 114QUERY PLAN FOR STATEMENT 52 (at line 131). STEP 1 The type of query is SELECT. 1 operator(s) under root |ROOT:EMIT Operator | |SCAN Operator | FROM CACHE >>> SPID 17 [Nest: 1] Procedure 'sybsystemprocs..sp_monitor' at line number 364: <<< CREATE PROCEDURE sp_monitor Parameter_name Type Length Prec Scale Param_order Mode -------------------- ------- ------ ---- ----- ----------- ---- @entity varchar 30 NULL NULL 1 in @dbname varchar 30 NULL NULL 2 in @OrderBy_OR_Procname varchar 30 NULL NULL 3 in @option varchar 30 NULL NULL 4 in(1 row affected) 359 return 1 360 end 361 end 362 363 -- Produce the procedural/stack trace for this spid. 364 >>> exec @rtnstatus = @monprocname @spid, @context, @option 365 end 366 367 else if @u_entity = "ARCHIVE" 368 begin 369 -- Call the archival sproc for given monitoring type and archive.(return status = 0)
sp_monitor procstack, '14' go SPID is involved in a blocking situation as follows: spid dbid id page row typestr blocked ---- ---- -- --- --- ----------- ------- 20 31514 3 1304 1 Ex_row-blk 14 31514 3 1304 1 Sh_row_request 20 Procedure stack trace for SPID 14: .....
