| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_monitor_procstack | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_monitor_procstack 4 ** 5 ** Main entry point procedure to identify the procedure calling 6 ** sequence for what is probably [currently] a blocked spid. Also 7 ** reports on the nature of blocking; i.e. who is blocking whom on 8 ** what page/row/table lock resource. If possible, generate the SQL 9 ** fragment executing for sprocs and cached statements. If possible, 10 ** generate the query plan for the currently executing statement in 11 ** the top-most stored procedure in the stack. 12 ** 13 ** This is also the interface to get the server-side stacktrace for 14 ** the process of interest. 15 ** 16 ** Parameters: 17 ** @spid - Process SPID to trace sproc calling sequence 18 ** @context - # of lines of context to generate of SQL executing. 19 ** @type - Type of object to trace back. 20 { 21 */ 22 create procedure sp_monitor_procstack( 23 @spid int 24 , @context int = NULL -- of lines of sproc text. 25 , @type varchar(10) = 'procedure' 26 ) as 27 begin 28 declare @sqlstr varchar(256) 29 , @maxnest int -- Nesting level of top proc 30 , @dbname varchar(30) 31 , @ownername varchar(30) 32 , @objname varchar(255) 33 , @procname varchar(92) 34 , @sp_showtext varchar(90) 35 , @linenum int 36 , @stmtnum int -- of stmt in top-most sproc 37 , @contextid int 38 , @batchid int 39 , @blocked int -- spid of blocking task. 40 , @retval int 41 , @error int -- @@error value 42 , @proc_objid int -- Objid of sproc. 43 , @blocking_spid int 44 , @ctxt_block int -- context block 45 , @nesting int 46 , @do_freeze int 47 , @trace_3604_ON int -- boolean, if 3604 is ON 48 , @topmost tinyint -- whether we are running the 49 -- top-most sproc in the stack 50 51 select @sqlstr = object_name(@@procid, db_id('sybsystemprocs')) 52 exec @retval = sp_monitor_check_permission @sqlstr 53 if (@retval != 0) 54 return @retval 55 56 -- For 'procstack' execution, all we want is that the monitoring tables 57 -- be installed. We don't even need 'enable monitoring' turned ON. 58 -- So only do setup verification; configuration verification is not 59 -- needed. 60 -- 61 select @procname = "sybsystemprocs.dbo.sp_monitor_verify_setup" 62 exec @retval = @procname 'procstack' 63 if (@retval != 0) 64 return @retval 65 66 -- [Comments left behind intentionally.] 67 -- Remember whether 'enable monitoring' is ON. Only then should we 68 -- attempt to freeze MDA tables; otherwise we will get a noise error. 69 -- 70 -- exec sp_monitor_getcfgval "enable monitoring", @do_freeze output 71 -- 72 -- But in a situation like this one, where we are accessing only 73 -- one MDA table, without a join, and the table is a state table, 74 -- there is no additional value to be gained by a freeze. So skip it. 75 -- 76 select @do_freeze = 0 77 78 if (@type IS NULL OR @type IN ("procedure", "procedure with arguments")) 79 begin 80 if (@do_freeze = 1) 81 select @retval = mdaconfig('freeze', 'begin') 82 83 -- ######################################################### 84 -- Report the procedure stack information for this spid. 85 -- 86 select SPID 87 , 'Nesting' = convert(tinyint, m.ContextID) 88 , m.DBName 89 , m.OwnerName 90 , m.ObjectName 91 , m.ObjectID 92 , m.LineNumber 93 , m.ContextID 94 , Blocked = 0 95 , Statement = 0 96 into #monProcessProcedures 97 from master.dbo.monProcessProcedures m 98 where SPID = @spid 99 100 if (@do_freeze = 1) 101 select @retval = mdaconfig('freeze', 'end') 102 103 -- Cache the locks held by this task and the ones it is 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 114 115 /* Adaptive Server has expanded all '*' elements in the following statement */ select lo.id, lo.dbid, lo.page, lo.type, lo.spid, lo.class, lo.fid, lo.context, lo.row, lo.loid, lo.partitionid, lo.nodeid, typestr = convert(varchar(30) null, "") 116 , blocked = @blocking_spid 117 into #syslocks 118 from master.dbo.syslocks lo 119 where (lo.spid = @spid 120 or lo.spid = @blocking_spid) 121 122 -- select * from #syslocks 123 124 update #syslocks 125 set typestr = (select v.name from master..spt_values v 126 where v.type = "L" and v.number = l.type) 127 from #syslocks l 128 129 -- Only select rows that are either blocked due to a 130 -- request, or the cause of the block. 131 -- 132 select spid, dbid, id, page, row, typestr, blocked 133 into #syslocks_2 134 from #syslocks l 135 where (typestr LIKE '%request%' or typestr LIKE '%blk%') 136 137 if (@@rowcount != 0) 138 begin 139 print "SPID %1! is involved in a blocking situation as follows:" 140 , @spid 141 142 -- Show if any blocks were found. 143 exec sp_autoformat #syslocks_2 144 end 145 else 146 begin 147 print " " 148 print "No blocks were found for SPID %1!", @spid 149 end 150 151 -- ######################################################### 152 -- From here on we execute code only if the task being 153 -- backtraced is already executing a sproc. Otherwise, it's 154 -- executing an ad-hoc SQL, in which case there will be 155 -- no rows in this table. So bail, out. 156 -- Get Nesting level of top-most executing sproc. 157 -- 158 select @maxnest = max(Nesting) 159 from #monProcessProcedures b 160 where b.SPID = @spid -- redundant, but still ... 161 162 if (@maxnest IS NULL) 163 begin 164 return 0 165 end 166 167 -- Check if the procedure that is being run is a light-weight 168 -- procedure. If so, they won't have any data in sysprocedures 169 -- so there is no further need to drill down. Simply use 170 -- a DBCC to generate the statement cache information and 171 -- exit. 172 -- 173 select @procname = ObjectName 174 , @proc_objid = ObjectID 175 , @dbname = DBName 176 from #monProcessProcedures 177 where Nesting = @maxnest 178 179 if (@procname LIKE '*%') 180 begin 181 print ">>> SPID %1! is running a cached statement '%2!' (object ID=%3!) in db '%4!' <<<" 182 , @spid, @procname, @proc_objid, @dbname 183 184 -- Figure out the various naming patterns for diff 185 -- forms of Light Weight Procedures. 186 -- 187 -- . "[*]ss%ss[*]" is for statement cache LWPs. 188 -- . "[*][0-9]*_%" is for dynamic SQL LWPs. 189 -- 190 -- Recognize each of them, and appropriately call 191 -- the interface that is available to generate the 192 -- SQL text. 193 -- 194 if (@procname LIKE '*ss%ss*') 195 begin 196 set switch ON print_output_to_client 197 with override, no_info 198 199 -- 1 prints the showplan, if it's around. 200 DBCC prsqlcache(@proc_objid, 1) 201 202 set switch OFF print_output_to_client with no_info 203 end 204 else if (@procname LIKE '*[0-9]%_[0-9]%') 205 begin 206 exec sp_getmessage 17679, @sqlstr output 207 print @sqlstr, @procname 208 end 209 210 return 0 211 end 212 213 -- ######################################################### 214 -- IF this spid also appears as blocked in sysprocesses, 215 -- and has a valid line #, generate the sproc information 216 -- using sp_showtext. 217 -- 218 219 -- Update the line # field with the value you might find 220 -- from sysprocesses for this 'hung' task. 221 -- 222 if (@linenum IS NOT NULL) OR (@blocked IS NOT NULL) 223 begin 224 update #monProcessProcedures 225 set LineNumber = isnull(@linenum, 0) 226 , Blocked = isnull(@blocked, 0) 227 , Statement = isnull(@stmtnum, 0) 228 where Nesting = @maxnest 229 end 230 231 print "Procedure stack trace for SPID %1!:", @spid 232 print " " 233 234 exec @retval = sp_autoformat 235 @selectlist = "Nesting, DBName, OwnerName, ObjectName,ObjectID, LineNumber,Blocked" 236 , @fulltabname = "#monProcessProcedures" 237 , @orderby = "order by Nesting desc" 238 if (@retval != 0) 239 return @retval 240 241 -- ######################################################### 242 -- Iterate through each procedure in the tree and try to 243 -- print the code fragment that was just executing. 244 -- Obviously for all but the top-most sproc, the statement 245 -- will be EXEC. But the context block around 246 -- that line of SQL will give the user some idea of what 247 -- code path was executing when the block/situation occured. 248 -- This will be helpful if there are multiple calls to procB 249 -- from procA under some if()-conditional logic. 250 -- 251 declare proc_cur cursor for 252 select a.DBName, a.OwnerName, a.ObjectName, a.LineNumber 253 , a.Nesting 254 , a.ContextID 255 from #monProcessProcedures a 256 where a.SPID = @spid 257 order by a.Nesting desc 258 259 open proc_cur 260 261 select @topmost = 1 262 while (1 = 1) 263 begin -- { 264 fetch proc_cur into @dbname 265 , @ownername 266 , @objname 267 , @linenum 268 , @nesting 269 , @contextid 270 if (@@sqlstatus != 0) 271 break 272 273 if ((@linenum IS NOT NULL) and (@objname IS NOT NULL)) 274 begin -- { 275 276 -- Fabricate the full name for the 277 -- executing sproc. 278 select @procname = @ownername 279 + '.' 280 + @objname 281 282 if (@procname IS NOT NULL) 283 begin 284 print " " 285 print ">>> SPID %1! [Nest: %2!] Procedure '%3!.%4!' at line number %5!: <<<" 286 , @spid, @nesting 287 , @dbname, @procname, @linenum 288 print " " 289 290 select @ctxt_block = 291 case when (@context IS NOT NULL) 292 then @context 293 else 5 294 end 295 296 select @sp_showtext = @dbname + ".dbo.sp_showtext" 297 exec @sp_showtext @objname = @procname 298 , @startline = @linenum 299 , @numlines_or_ctxt = @ctxt_block 300 , @printopts = "context,linenumbers" 301 302 /* 303 ** FUTURE: This works, but we need more 304 ** complete data from the monitoring 305 ** table to directly call the sproc. 306 ** Until that is available, run this 307 ** in a slower mode; works. 308 */ 309 -- Generate the plan of the currently 310 -- executing statement in the top-most 311 -- sproc. 312 -- 313 if (@topmost = 1) 314 begin 315 exec @retval = sp_showplan 316 @spid 317 , NULL 318 , NULL 319 , NULL 320 select @error = @@error 321 select @topmost = 0 322 if (@retval != 0) 323 begin 324 print "sp_showplan return code: %1! (@error=%2!)" 325 , @retval 326 , @error 327 if (@error != 0) 328 goto end_cursor 329 end 330 end 331 end 332 end -- } 333 end -- } 334 335 end_cursor: 336 close proc_cur 337 338 deallocate cursor proc_cur 339 340 end 341 else if (@type = "server") 342 begin 343 select @trace_3604_ON = 344 (switchprop("print_output_to_client") & 1) 345 346 -- If it's not set locally, turn it ON now. 347 if (@trace_3604_ON != 1) 348 begin 349 set switch ON print_output_to_client 350 with override, no_info 351 end 352 353 dbcc stacktrace(@spid) 354 355 if (@trace_3604_ON != 1) 356 begin 357 set switch OFF print_output_to_client with no_info 358 end 359 360 return @@error 361 362 end 363 364 return @retval 365 end -- } 366
exec sp_procxmode 'sp_monitor_procstack', 'AnyMode' go Grant Execute on sp_monitor_procstack to public go
| DEPENDENCIES |
| PROCS AND TABLES USED writes table tempdb..#syslocks_2 (1) reads table master..monProcessProcedures (1)  calls proc sybsystemprocs..sp_showplan read_writes table tempdb..#syslocks (1) reads table master..syslocks (1) reads table master..spt_values (1) calls proc sybsystemprocs..sp_monitor_check_permission calls proc sybsystemprocs..sp_autoformat  read_writes table tempdb..#colinfo_af (1) reads table tempdb..syscolumns (1)  reads table tempdb..systypes (1) calls proc sybsystemprocs..sp_autoformat reads table master..systypes (1) calls proc sybsystemprocs..sp_namecrack reads table master..syscolumns (1) calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) reads table master..syslanguages (1) reads table master..sysprocesses (1) read_writes table tempdb..#monProcessProcedures (1) |
