DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_analyze  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure produces is used to invoke all the subordinate stored
3     ** procedures
4     */
5     create or replace procedure sp_sysmon_analyze
6         @interval int,
7         @Reco char(1),
8         @section char(80),
9         @applmon char(14),
10        @instid smallint = NULL /* optional SDC instance id */
11    as
12    
13        /* ----------- declare local variables ---------- */
14        declare @NumXacts int /* for per transactions calculations */
15        declare @NumElapsedMs int /* for "per Elapsed second" calculations */
16        declare @NumEngines tinyint /* number of engines online */
17        declare @status int /* hold sproc status codes */
18        declare @tmpelapsedMs int
19        declare @GetStatTime datetime /* time monitor counters sampled */
20        declare @sec_hdr char(80) /* string to delimit sections on printout */
21        declare @blankline char(1) /* to print blank line */
22        declare @rptline char(80) /* formatted stats line for print statement */
23        declare @tmp_elapsedMs_total int
24        declare @CacheID smallint /* Cache ID to map to buffer_N group */
25        declare @CacheName varchar(255) /* Cache Name from cache id lookup */
26        declare @i int
27        declare @msg varchar(255)
28    
29        select @tmp_elapsedMs_total = 0
30        select @sec_hdr = "==============================================================================="
31    
32        select @NumEngines = value
33        from #tempmonitors where
34            field_name = "cg_cmaxonline" and
35            group_name = "config"
36    
37        select @NumElapsedMs = @interval * 1000
38    
39    
40        /*
41        **      Name:           Number of Transactions
42        **      Descrip:        Measures the number of committed transactions, both
43        **                      explicit via BEGIN TRAN and COMMIT TRAN as well as
44        **                      implicit via single statement DML.  Used to report
45        **                      not just how much work has been done within the sample
46        **                      but, more importantly, as a measure of viewing other
47        **                      statistics with a common denominator (ie., I/Os per
48        **                      transaction).  This provides a common way of judging
49        **                      the effects of making changes within a controlled test
50        **                      environment.
51        **
52        **                      For example, what effect does adding memory have on I/O
53        **                      rates.
54        **      Calc:           access.xacts
55        **      Caveats:        This counter is also incremented once for each
56        **                      "sub-transaction" participating in a multi-DB
57        **                      transaction.
58        **      Tuning:         None, informational only to provide a frame of
59        **                      reference for evaluating other statistics.
60        */
61    
62        select @NumXacts = value
63        from #tempmonitors
64        where group_name = "access" and
65            field_name = "xacts"
66    
67        if @NumXacts = 0
68            select @NumXacts = 1 /* avoid divide by 0 for all per xact calcs */
69    
70        /* 
71        ** Since monitor counters are updated usually without any synchronization
72        ** these are not completely accurate in multi-engine scenarios where trace 
73        ** 4050 is used or if the platform is NT where we don't have engine local 
74        ** counters. Usually this is not a problem. But in cases where we deduce some 
75        ** values based on the relative values of two or more counters, this can cause 
76        ** sysmon to report meaningless values at times. (E.g. -ve values)
77        ** We should fixup any such counters here to avoid meaningless reporting.  
78        **
79        ** 1. Cache Misses = Cache searches - Cache hits
80        **	If hits > searches set hits = searches.
81        **	Although we have cache hit counters at pool level, those are not being 
82        **	used in reporting so we need not take care of them here.
83        */
84    
85        update #tempmonitors
86        set value = b.value
87        from #tempmonitors a, #tempmonitors b
88        where a.group_name = b.group_name
89            and a.group_name like 'buffer_%'
90            and b.group_name like 'buffer_%'
91            and a.field_name = 'bufsearch_calls'
92            and b.field_name = 'bufsearch_finds'
93            and a.value < b.value
94    
95        /* 
96        ** Execute the subprocedures.  If any procedure returns a status of
97        ** 1 we treat it as a fatal error and return.  We don't report any
98        ** messages here.  That is the job of the subordinate proc.
99        */
100   
101       if (@section = "NULL")
102       begin /* { */
103   
104           if (@@kernelmode = 'process')
105           begin
106               exec @status = sp_sysmon_kernel @NumEngines, @NumElapsedMs,
107                   @NumXacts, @Reco, @instid
108           end
109           else
110           begin
111   
112               select @instid = 0
113   
114               exec @status = sp_sysmon_kernel_threaded
115                   @NumEngines, @NumElapsedMs,
116                   @NumXacts, @Reco, @instid
117           end
118           if @status = 1 return 1
119   
120           exec @status = sp_sysmon_wpm @NumElapsedMs, @NumXacts, @Reco
121           if @status = 1 return 1
122   
123           exec @status = sp_sysmon_parallel @NumElapsedMs, @NumXacts, @Reco
124           if @status = 1 return 1
125   
126           exec @status = sp_sysmon_taskmgmt @NumEngines, @NumElapsedMs, @NumXacts,
127               @Reco
128           if @status = 1 return 1
129   
130           exec @status = sp_sysmon_appmgmt @NumEngines, @NumElapsedMs, @NumXacts,
131               @applmon
132           if @status = 1 return 1
133   
134           exec @status = sp_sysmon_esp @NumElapsedMs, @NumXacts
135           if @status = 1 return 1
136   
137           exec @status = sp_sysmon_hk @NumElapsedMs, @NumXacts, @Reco, @instid
138           if @status = 1 return 1
139   
140           exec @status = sp_sysmon_maccess @NumElapsedMs, @NumXacts, @Reco
141           if @status = 1 return 1
142   
143           exec @status = sp_sysmon_xactsum @NumElapsedMs, @NumXacts
144           if @status = 1 return 1
145   
146           exec @status = sp_sysmon_xactmgmt @NumElapsedMs, @NumXacts, @Reco
147           if @status = 1 return 1
148   
149           exec @status = sp_sysmon_index @NumElapsedMs, @NumXacts
150           if @status = 1 return 1
151   
152           exec @status = sp_sysmon_mdcache @NumElapsedMs, @NumXacts, @Reco
153           if @status = 1 return 1
154   
155           exec @status = sp_sysmon_locks @NumElapsedMs, @NumXacts, @Reco,
156               @NumEngines
157           if @status = 1 return 1
158   
159           exec @status = sp_sysmon_dcache @NumEngines, @NumElapsedMs, @NumXacts,
160               @Reco, @instid
161           if @status = 1 return 1
162   
163           exec @status = sp_sysmon_pcache @NumElapsedMs, @NumXacts, @Reco
164           if @status = 1 return 1
165   
166           exec @status = sp_sysmon_memory @NumElapsedMs, @NumXacts
167           if @status = 1 return 1
168   
169           exec @status = sp_sysmon_recovery @NumElapsedMs, @NumXacts
170           if @status = 1 return 1
171   
172           exec @status = sp_sysmon_diskio @NumEngines, @NumElapsedMs, @NumXacts,
173               @Reco
174           if @status = 1 return 1
175   
176           exec @status = sp_sysmon_netio @NumEngines, @NumElapsedMs, @NumXacts
177           if @status = 1 return 1
178   
179           exec @status = sp_sysmon_repagent
180           if @status = 1 return 1
181   
182           return 0
183       end /* } */
184       else
185       if (@section = "kernel")
186       begin /* { */
187           print @sec_hdr
188           if (@@kernelmode = 'process')
189           begin
190               exec @status = sp_sysmon_kernel @NumEngines,
191                   @NumElapsedMs, @NumXacts,
192                   @Reco, @instid
193           end
194           else
195           begin
196   
197               select @instid = 0
198   
199               exec @status = sp_sysmon_kernel_threaded @NumEngines,
200                   @NumElapsedMs, @NumXacts, @Reco, @instid
201           end
202   
203           if @status = 1
204           begin /* { */
205               return 1
206           end /* } */
207           else
208           begin /* { */
209               return 0
210           end /* } */
211   
212       end /* } */
213       else
214       if (@section = "wpm")
215       begin /* { */
216           print @sec_hdr
217           exec @status = sp_sysmon_wpm @NumElapsedMs, @NumXacts, @Reco
218           if @status = 1 /* fatal error - abort run */
219           begin /* { */
220               /* Subordinate stored procedures print their own
221               messages.*/
222               return 1
223           end /* } */
224           else
225           begin /* { */
226               return 0
227           end /* } */
228   
229       end /* } */
230       else
231       if (@section = "parallel")
232       begin /* { */
233           print @sec_hdr
234           exec @status = sp_sysmon_parallel @NumElapsedMs, @NumXacts, @Reco
235           if @status = 1 /* fatal error - abort run */
236           begin /* { */
237               /* Subordinate stored procedures print their own
238               messages.*/
239               return 1
240           end /* } */
241           else
242           begin /* { */
243               return 0
244           end /* } */
245   
246       end /* } */
247       else
248       if (@section = "taskmgmt")
249       begin /* { */
250           print @sec_hdr
251           exec @status = sp_sysmon_taskmgmt @NumEngines, @NumElapsedMs, @NumXacts, @Reco
252           if @status = 1 /* fatal error - abort run */
253           begin /* { */
254               /* Subordinate stored procedures print their own
255               messages.*/
256               return 1
257           end /* } */
258           else
259           begin /* { */
260               return 0
261           end /* } */
262   
263       end /* } */
264       else
265       if (@section = "appmgmt")
266       begin /* { */
267           print @sec_hdr
268           exec @status = sp_sysmon_appmgmt @NumEngines, @NumElapsedMs, @NumXacts, @applmon
269           if @status = 1 /* fatal error - abort run */
270           begin /* { */
271               /* Subordinate stored procedures print their own messages. */
272               return 1
273           end /* } */
274           else
275           begin /* { */
276               return 0
277           end /* } */
278       end /* } */
279       else
280       if (@section = "esp")
281       begin
282           exec @status = sp_sysmon_esp @NumElapsedMs, @NumXacts
283           if @status = 1 /* fatal error - abort run */
284           begin
285               /* Subordinate stored procedures print their own messages. */
286               return 1
287           end /* } */
288       end /* } */
289       else
290       if (@section = "housekeeper")
291       begin
292           exec @status = sp_sysmon_hk @NumElapsedMs, @NumXacts, @Reco, @instid
293           if @status = 1 /* fatal error - abort run */
294           begin
295               /* Subordinate stored procedures print their own messages. */
296               return 1
297           end /* } */
298       end /* } */
299       else
300       if (@section = "monaccess")
301       begin /* { */
302           print @sec_hdr
303           exec @status = sp_sysmon_maccess @NumElapsedMs, @NumXacts, @Reco
304           if @status = 1 /* fatal error - abort run */
305           begin /* { */
306               /* Subordinate stored procedures print their own messages. */
307               return 1
308           end /* } */
309       end /* } */
310       else
311       if (@section = "xactsum")
312       begin /* { */
313           print @sec_hdr
314           exec @status = sp_sysmon_xactsum @NumElapsedMs, @NumXacts
315           if @status = 1 /* fatal error - abort run */
316           begin /* { */
317               /* Subordinate stored procedures print their own
318               messages.*/
319               return 1
320           end /* } */
321           else
322           begin /* { */
323               return 0
324           end /* } */
325   
326       end /* } */
327       else
328       if (@section = "xactmgmt")
329       begin /* { */
330           print @sec_hdr
331           exec @status = sp_sysmon_xactmgmt @NumElapsedMs, @NumXacts, @Reco
332           if @status = 1 /* fatal error - abort run */
333           begin /* { */
334               /* Subordinate stored procedures print their own
335               messages.*/
336               return 1
337           end /* } */
338           else
339           begin /* { */
340               return 0
341           end /* } */
342   
343       end /* } */
344       else
345       if (@section = "indexmgmt")
346       begin /* { */
347           print @sec_hdr
348           exec @status = sp_sysmon_index @NumElapsedMs, @NumXacts
349           if @status = 1 /* fatal error - abort run */
350           begin /* { */
351               /* Subordinate stored procedures print their own
352               messages.*/
353               return 1
354           end /* } */
355           else
356           begin /* { */
357               return 0
358           end /* } */
359   
360       end /* } */
361       else
362       if (@section = "mdcache")
363       begin /* { */
364           print @sec_hdr
365           exec @status = sp_sysmon_mdcache @NumElapsedMs, @NumXacts, @Reco
366           if @status = 1 /* fatal error - abort run */
367           begin /* { */
368               /* Subordinate stored procedures print their own
369               messages.*/
370               return 1
371           end /* } */
372           else
373           begin /* { */
374               return 0
375           end /* } */
376   
377       end /* } */
378       else
379       if (@section = "locks")
380       begin /* { */
381           print @sec_hdr
382           exec @status = sp_sysmon_locks @NumElapsedMs,
383               @NumXacts, @Reco, @NumEngines
384           if @status = 1 /* fatal error - abort run */
385           begin /* { */
386               /* Subordinate stored procedures print their own
387               messages.*/
388               return 1
389           end /* } */
390           else
391           begin /* { */
392               return 0
393           end /* } */
394   
395       end /* } */
396       else
397       if (@section = "dcache")
398       begin /* { */
399           print @sec_hdr
400           exec @status = sp_sysmon_dcache @NumEngines, @NumElapsedMs, @NumXacts, @Reco, @instid
401           if @status = 1 /* fatal error - abort run */
402           begin /* { */
403               /* Subordinate stored procedures print their own
404               messages.*/
405               return 1
406           end /* } */
407           else
408           begin /* { */
409               return 0
410           end /* } */
411   
412       end /* } */
413       else
414       if (@section = "pcache")
415       begin /* { */
416           print @sec_hdr
417           exec @status = sp_sysmon_pcache @NumElapsedMs, @NumXacts, @Reco
418           if @status = 1 /* fatal error - abort run */
419           begin /* { */
420               /* Subordinate stored procedures print their own
421               messages.*/
422               return 1
423           end /* } */
424           else
425           begin /* { */
426               return 0
427           end /* } */
428   
429       end /* } */
430       else
431       if (@section = "memory")
432       begin /* { */
433           print @sec_hdr
434           exec @status = sp_sysmon_memory @NumElapsedMs, @NumXacts
435           if @status = 1 /* fatal error - abort run */
436           begin /* { */
437               /* Subordinate stored procedures print their own
438               messages.*/
439               return 1
440           end /* } */
441           else
442           begin /* { */
443               return 0
444           end /* } */
445   
446       end /* } */
447       else
448       if (@section = "recovery")
449       begin /* { */
450           print @sec_hdr
451           exec @status = sp_sysmon_recovery @NumElapsedMs, @NumXacts
452           if @status = 1 /* fatal error - abort run */
453           begin /* { */
454               /* Subordinate stored procedures print their own
455               messages.*/
456               return 1
457           end /* } */
458           else
459           begin /* { */
460               return 0
461           end /* } */
462   
463       end /* } */
464       else
465       if (@section = "diskio")
466       begin /* { */
467           print @sec_hdr
468           exec @status = sp_sysmon_diskio @NumEngines, @NumElapsedMs, @NumXacts, @Reco
469           if @status = 1 /* fatal error - abort run */
470           begin /* { */
471               /* Subordinate stored procedures print their own
472               messages.*/
473               return 1
474           end /* } */
475           else
476           begin /* { */
477               return 0
478           end /* } */
479   
480       end /* } */
481       else
482       if (@section = "netio")
483       begin /* { */
484           print @sec_hdr
485           exec @status = sp_sysmon_netio @NumEngines, @NumElapsedMs, @NumXacts
486           if @status = 1 /* fatal error - abort run */
487           begin /* { */
488               /* Subordinate stored procedures print their own
489               messages.*/
490               return 1
491           end /* } */
492           else
493           begin /* { */
494               return 0
495           end /* } */
496   
497       end /* } */
498   
499       else
500       if (@section = "repagent")
501       begin /* { */
502           print @sec_hdr
503           exec @status = sp_sysmon_repagent
504           if @status = 1 /* fatal error - abort run */
505           begin /* { */
506               /* Subordinate stored procedures print their own
507               messages.*/
508               return 1
509           end /* } */
510           else
511           begin /* { */
512               return 0
513           end /* } */
514   
515       end /* } */
516       else
517       begin
518           raiserror 18532 @msg, "sp_sysmon"
519           raiserror 18534 @msg, "sp_sysmon"
520       end
521   

DEFECTS
 VRUN 4 Variable is read and not initialized @msg 518
 MLCH 3 Char type with length>30 char(80) 8
 MLCH 3 Char type with length>30 char(80) 20
 MLCH 3 Char type with length>30 char(80) 22
 MNER 3 No Error Check should check @@error after update 85
 MNER 3 No Error Check should check return value of exec 106
 MNER 3 No Error Check should check return value of exec 190
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 265
 MUCO 3 Useless Code Useless Brackets 280
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 311
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 345
 MUCO 3 Useless Code Useless Brackets 362
 MUCO 3 Useless Code Useless Brackets 379
 MUCO 3 Useless Code Useless Brackets 397
 MUCO 3 Useless Code Useless Brackets 414
 MUCO 3 Useless Code Useless Brackets 431
 MUCO 3 Useless Code Useless Brackets 448
 MUCO 3 Useless Code Useless Brackets 465
 MUCO 3 Useless Code Useless Brackets 482
 MUCO 3 Useless Code Useless Brackets 500
 QAFM 3 Var Assignment from potentially many rows 32
 QAFM 3 Var Assignment from potentially many rows 62
 QJWT 3 Join or Sarg Without Index on temp table 88
 QNAJ 3 Not using ANSI Inner Join 87
 VNRD 3 Variable is not read @tmp_elapsedMs_total 29
 VUNU 3 Variable is not used @tmpelapsedMs 18
 VUNU 3 Variable is not used @GetStatTime 19
 VUNU 3 Variable is not used @blankline 21
 VUNU 3 Variable is not used @rptline 22
 VUNU 3 Variable is not used @CacheID 24
 VUNU 3 Variable is not used @CacheName 25
 VUNU 3 Variable is not used @i 26
 MTR1 2 Metrics: Comments Ratio Comments: 34% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 67dec - 59exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 287 5

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_sysmon_memory  
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_kernel_threaded  
   reads table master..monEngine (1)  
   read_writes table tempdb..#tmpEngUtilization (1) 
   reads table master..monSysLoad (1)  
   reads table tempdb..#tempThreadStats (1) 
   reads table tempdb..#tempmonitors (1) 
   reads table tempdb..#tempWorkQueue (1) 
   reads table master..monThreadPool (1)  
   reads table tempdb..#tempIOCStats (1) 
   read_writes table tempdb..#tmpLoad (1) 
   read_writes table tempdb..#tmpThreadPool (1) 
   reads table master..monThread (1)  
   reads table tempdb..#muxthreadsinfo (1) 
calls proc sybsystemprocs..sp_sysmon_xactsum  
   reads table tempdb..#tempmonitors (1) 
read_writes table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_parallel  
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_pcache  
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_dcache  
   calls proc sybsystemprocs..sp_sysmon_dcache_sum  
      reads table tempdb..#tempmonitors (1) 
      reads table master..sysconfigures (1)  
   calls proc sybsystemprocs..sp_sysmon_dcache_dtl  
      read_writes table tempdb..#foo (1) 
      reads table tempdb..#cachemap (1) 
      reads table tempdb..#pool_detail_per_cache (1) 
      reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_mdcache  
   calls proc sybsystemprocs..sp_monitorconfig  
      calls proc sybsystemprocs..sp_getmessage  
         calls proc sybsystemprocs..sp_validlang  
            reads table master..syslanguages (1)  
         reads table sybsystemprocs..sysusermessages  
         reads table master..syslanguages (1)  
         reads table master..sysmessages (1)  
      calls proc sybsystemprocs..sp_exec_SQL  
         writes table sybsystemprocs..sp_exec_SQL_rset_001 
      calls proc sybsystemprocs..sp_validateconfigname  
         writes table sybsystemprocs..sp_validateconfigname_rset_001 
         reads table master..sysconfigures (1)  
         calls proc sybsystemprocs..sp_getmessage  
         reads table master..syscurconfigs (1)  
      read_writes table tempdb..#resource_result_tbl (1) 
      reads table master..sysconfigures (1)  
      calls proc sybsystemprocs..sp_aux_checkroleperm  
         reads table master..syscurconfigs (1)  
         reads table master..sysconfigures (1)  
      writes table sybsystemprocs..sp_monitorconfig_rset_001 
      writes table sybsystemprocs..sp_monitorconfig_rset_002 
      reads table master..syscurconfigs (1)  
      read_writes table tempdb..#resource_monitor_tbl (1) 
   reads table tempdb..#tempmonitors (1) 
   read_writes table tempdb..#tmp_res_monitor (1) 
calls proc sybsystemprocs..sp_sysmon_hk  
   reads table tempdb..#tempmonitors (1) 
   reads table master..sysengines (1)  
calls proc sybsystemprocs..sp_sysmon_netio  
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_kernel  
   reads table master..syscurconfigs (1)  
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_taskmgmt  
   read_writes table tempdb..#tmpThreadPool (1) 
   reads table tempdb..#muxthreadsinfo (1) 
   reads table master..monThreadPool (1)  
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_wpm  
   reads table tempdb..#tempmonitors (1) 
   reads table tempdb..#tempconfigures (1) 
calls proc sybsystemprocs..sp_sysmon_recovery  
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_esp  
   reads table tempdb..#tempconfigures (1) 
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_index  
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_xactmgmt  
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_maccess  
   reads table tempdb..#tempconfigures (1) 
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_appmgmt  
   read_writes table tempdb..#tempappl (1) 
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_locks  
   reads table tempdb..#tempmonitors (1) 
   read_writes table tempdb..#foo (1) 
calls proc sybsystemprocs..sp_sysmon_repagent  
   reads table master..sysdatabases (1)  
   read_writes table tempdb..#tempdatabases (1) 
   reads table tempdb..#tempmonitors (1) 
calls proc sybsystemprocs..sp_sysmon_diskio  
   reads table tempdb..#devicemap (1) 
   reads table tempdb..#tempmonitors (1) 

CALLERS
called by proc sybsystemprocs..sp_sysmon