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

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 112
 MTYP 4 Assignment type mismatch @spid: smallint = int 313
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 140
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 110
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 116
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 117
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 123
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 174
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 225
 TNOI 4 Table with no index master..monProcessProcedures master..monProcessProcedures
 TNOI 4 Table with no index master..syslocks master..syslocks
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 VRUN 4 Variable is read and not initialized @blocked 219
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause proc_cur 249
 MAW1 3 Warning message on %name% master..syslocks.id: Warning message on syslocks 112
 MAW1 3 Warning message on %name% tempdb..#syslocks.id: Warning message on #syslocks_crby_sybsystemprocs__sp_monitor_procstack 129
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..syslocks  
 MGTP 3 Grant to public master..sysprocesses  
 MNER 3 No Error Check should check @@error after select into 83
 MNER 3 No Error Check should check @@error after select into 112
 MNER 3 No Error Check should check @@error after update 121
 MNER 3 No Error Check should check @@error after select into 129
 MNER 3 No Error Check should check return value of exec 140
 MNER 3 No Error Check should check return value of exec 203
 MNER 3 No Error Check should check @@error after update 221
 MNER 3 No Error Check should check return value of exec 312
 MUCO 3 Useless Code Useless Brackets in create proc 22
 MUCO 3 Useless Code Useless Begin-End Pair 27
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 279
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 319
 MUCO 3 Useless Code Useless Brackets 324
 MUCO 3 Useless Code Useless Brackets 338
 MUCO 3 Useless Code Useless Brackets 344
 MUCO 3 Useless Code Useless Brackets 352
 MZMB 3 Zombie: use of non-existent object sybsystemprocs.dbo.sp_monitor_verify_setup 0
 QAFM 3 Var Assignment from potentially many rows 106
 QAFM 3 Var Assignment from potentially many rows 170
 QAPT 3 Access to Proxy Table master..monProcessProcedures 94
 QCTC 3 Conditional Table Creation 83
 QCTC 3 Conditional Table Creation 112
 QCTC 3 Conditional Table Creation 129
 VUNU 3 Variable is not used @batchid 38
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 249
 MDYE 2 Dynamic Exec Marker exec @retval 59
 MDYE 2 Dynamic Exec Marker exec @sp_showtext 294
 MSUC 2 Correlated Subquery Marker 122
 MTR1 2 Metrics: Comments Ratio Comments: 41% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 24 = 27dec - 5exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 132 22

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#syslocks (1) 
read_writes table tempdb..#monProcessProcedures (1) 
reads table master..syslocks (1)  
calls proc sybsystemprocs..sp_showplan  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
      reads table master..syscurconfigs (1)  
      reads table master..sysconfigures (1)  
reads table master..sysprocesses (1)  
calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
reads table master..spt_values (1)  
writes table tempdb..#syslocks_2 (1) 
reads table master..monProcessProcedures (1)  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)