DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_xactmgmt  31 Aug 14Defects Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** log activity including activity in the user log caches.
4     */
5     create procedure sp_sysmon_xactmgmt
6     
7         @NumElapsedMs int, /* for "per Elapsed second" calculations */
8         @NumXacts int, /* for per transactions calculations */
9         @Reco char(1) /* Flag for recommendations */
10    as
11    
12        /* --------- declare local variables --------- */
13        declare @tmp_int int /* temp var for integer storage */
14        declare @tmp_int2 int /* temp var for integer storage */
15        declare @tmp_total int /* temp var for summing 'total #s' data */
16        declare @sum1line char(80) /* string to delimit total lines without 
17        ** percent calc on printout */
18        declare @sum2line char(67) /* string to delimit total lines with 
19        ** percent calc on printout */
20        declare @blankline char(1) /* to print blank line */
21        declare @psign char(3) /* hold a percent sign (%) for print out */
22        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
23        declare @rptline char(80) /* formatted stats line for print statement */
24        declare @section char(80) /* string to delimit sections on printout */
25        declare @discarded_subcmd int /* # of ML-DML subcmds successfully discarded */
26        declare @logged_subcmd int /* # of ML-DML subcmds which could not be
27        ** discarded and therefore had to be logged */
28        declare @fldml_plc_flush_full int /* PLC flushes due to full PLC accounted for
29        ** Fully Logged DML commands. */
30        declare @mldml_plc_flush_full int /* PLC flushes due to full PLC accounted for
31        ** Minimally Logged DML commands. */
32        declare @fldml_plc_flush_slr int /* PLC flushes due to SLR logging accounted
33        ** for Fully Logged DML commands. */
34        declare @mldml_plc_flush_slr int /* PLC flushes due to SLR logging accounted
35        ** for Minimally Logged DML commands. */
36    
37    
38        /* ------------- Variables for Tuning Recommendations ------------*/
39        declare @recotxt char(80) /* Header for tuning recommendation */
40        declare @recoline char(80) /* to underline recotxt */
41        declare @reco_hdr_prn bit /* to indicate if the recotxt is already printed */
42        declare @reco_by_slr real
43        declare @reco_by_full_ulc real
44    
45        /* --------- Setup Environment --------- */
46        set nocount on /* disable row counts being sent to client */
47    
48        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
49        select @sum2line = "  -------------------------  ------------  ------------  ----------"
50        select @blankline = " "
51        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
52        select @na_str = "n/a"
53        select @section = "==============================================================================="
54    
55    
56        print @section
57        print @blankline
58    
59        print "Transaction Management"
60        print "----------------------"
61        print @blankline
62        print "  ULC Flushes to Xact Log         per sec      per xact       count  %% of total"
63        print @sum1line
64    
65        select @tmp_total = SUM(value)
66        from #tempmonitors
67        where group_name = 'xls' and
68            (field_name like "plc_flush_%" or
69                field_name like "mldml_plc_flush_%") and
70            field_name != "plc_flush_discard" and
71            field_name != "mldml_plc_discard"
72    
73        if @tmp_total = 0 /* Avoid Divide by Zero Errors */
74        begin
75            select @rptline = "  Total ULC Flushes                   0.0           0.0           0       n/a"
76            print @rptline
77        end
78        else
79        begin
80            print "  Any Logging Mode DMLs"
81    
82            select @tmp_int = value
83            from #tempmonitors
84            where group_name = 'xls' and
85                field_name = "plc_flush_endxact"
86    
87            select @rptline = "    by End Transaction" + space(7) +
88                str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
89                space(2) +
90                str(@tmp_int / convert(real, @NumXacts), 12, 1) +
91                space(2) +
92                str(@tmp_int, 10) + space(5) +
93                str(100.0 * @tmp_int / @tmp_total, 5, 1) +
94                @psign
95            print @rptline
96    
97            select @tmp_int = value
98            from #tempmonitors
99            where group_name = 'xls' and
100               field_name = "plc_flush_xdeschange"
101   
102           select @rptline = "    by Change of Database" + space(4) +
103               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
104               space(2) +
105               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
106               space(2) +
107               str(@tmp_int, 10) + space(5) +
108               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
109               @psign
110           print @rptline
111   
112           select @tmp_int = value
113           from #tempmonitors
114           where group_name = 'xls' and
115               field_name = "plc_flush_unpin"
116   
117           select @rptline = "    by Unpin" + space(17) +
118               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
119               space(2) +
120               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
121               space(2) +
122               str(@tmp_int, 10) + space(5) +
123               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
124               @psign
125           print @rptline
126   
127           select @tmp_int = SUM(value)
128           from #tempmonitors
129           where group_name = 'xls' and field_name IN
130               ("plc_flush_pmscan")
131   
132           select @rptline = "    by Log markers" + space(11) +
133               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
134               space(2) +
135               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
136               space(2) +
137               str(@tmp_int, 10) + space(5) +
138               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
139               @psign
140           print @rptline
141           print @blankline
142   
143           print "  Fully Logged DMLs"
144   
145           select @tmp_int = value
146           from #tempmonitors
147           where group_name = 'xls' and
148               field_name = "plc_flush_full"
149   
150           select @fldml_plc_flush_full = @tmp_int
151   
152           select @rptline = "    by Full ULC" + space(14) +
153               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
154               space(2) +
155               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
156               space(2) +
157               str(@tmp_int, 10) + space(5) +
158               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
159               @psign
160           print @rptline
161   
162           select @tmp_int = value
163           from #tempmonitors
164           where group_name = 'xls' and
165               field_name = "plc_flush_slr_xact"
166   
167           select @fldml_plc_flush_slr = @tmp_int
168   
169           select @rptline = "    by Single Log Record" + space(5) +
170               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
171               space(2) +
172               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
173               space(2) +
174               str(@tmp_int, 10) + space(5) +
175               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
176               @psign
177           print @rptline
178   
179           print @blankline
180           print "  Minimally Logged DMLs"
181   
182           select @tmp_int = value
183           from #tempmonitors
184           where group_name = 'xls' and
185               field_name = "mldml_plc_flush_full"
186   
187           select @mldml_plc_flush_full = @tmp_int
188   
189           select @rptline = "    by Full ULC" + space(14) +
190               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
191               space(2) +
192               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
193               space(2) +
194               str(@tmp_int, 10) + space(5) +
195               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
196               @psign
197           print @rptline
198   
199           select @reco_by_full_ulc = convert(int, 100.0 *
200               ((1.0 * (@fldml_plc_flush_full + @mldml_plc_flush_full))
201               / @tmp_total))
202   
203           select @tmp_int = value
204           from #tempmonitors
205           where group_name = 'xls' and
206               field_name = "mldml_plc_flush_slr_xact"
207   
208           select @mldml_plc_flush_slr = @tmp_int
209   
210           select @rptline = "    by Single Log Record" + space(5) +
211               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
212               space(2) +
213               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
214               space(2) +
215               str(@tmp_int, 10) + space(5) +
216               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
217               @psign
218           print @rptline
219   
220           select @reco_by_slr = convert(int, 100.0 *
221               ((1.0 * (@fldml_plc_flush_slr + @mldml_plc_flush_slr))
222               / @tmp_total))
223   
224           select @tmp_int = value
225           from #tempmonitors
226           where group_name = 'xls' and
227               field_name = "mldml_plc_flush_beginsubcmd"
228   
229           select @rptline = "    by Start of Sub-Command" + space(2) +
230               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
231               space(2) +
232               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
233               space(2) +
234               str(@tmp_int, 10) + space(5) +
235               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
236               @psign
237           print @rptline
238   
239           select @tmp_int = value
240           from #tempmonitors
241           where group_name = 'xls' and
242               field_name = "mldml_plc_flush_endsubcmd"
243   
244           select @logged_subcmd = @tmp_int
245   
246           select @rptline = "    by End of Sub-Command" + space(4) +
247               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
248               space(2) +
249               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
250               space(2) +
251               str(@tmp_int, 10) + space(5) +
252               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
253               @psign
254           print @rptline
255           print @sum2line
256           select @rptline = "  Total ULC Flushes" + space(10) +
257               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
258               space(2) +
259               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
260               space(2) +
261               str(@tmp_total, 10)
262           print @rptline
263       end
264   
265       print @blankline
266       print "  ULC Flushes Skipped             per sec      per xact       count  %% of total"
267       print @sum1line
268   
269       select @tmp_total = SUM(value)
270       from #tempmonitors
271       where group_name = 'xls' and
272           field_name in ("plc_flush_discard", "mldml_plc_discard")
273   
274       if @tmp_total = 0 /* Avoid Divide by Zero Errors */
275       begin
276           select @rptline = "  Total ULC Skips                     0.0           0.0           0       n/a"
277           print @rptline
278       end
279       else
280       begin
281           print "  Fully Logged DMLs"
282           select @tmp_int = value
283           from #tempmonitors
284           where group_name = 'xls' and
285               field_name = "plc_flush_discard"
286   
287           select @rptline = "    by ULC Discards" + space(10) +
288               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
289               space(2) +
290               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
291               space(2) +
292               str(@tmp_int, 10) + space(5) +
293               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
294               @psign
295           print @rptline
296   
297           print "  Minimally Logged DMLs"
298           select @tmp_int = value
299           from #tempmonitors
300           where group_name = 'xls' and
301               field_name = "mldml_plc_discard"
302   
303           select @discarded_subcmd = value
304           from #tempmonitors
305           where group_name = 'xls' and
306               field_name = "mldml_subcmd_discard"
307   
308           select @rptline = "    by ULC Discards" + space(10) +
309               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
310               space(2) +
311               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
312               space(2) +
313               str(@tmp_int, 10) + space(5) +
314               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
315               @psign
316           print @rptline
317   
318           print @sum2line
319           select @rptline = "  Total ULC Flushes Skips" + space(4) +
320               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
321               space(2) +
322               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
323               space(2) +
324               str(@tmp_total, 10)
325           print @rptline
326   
327       end
328   
329       print @blankline
330       print "  ULC Log Records                 per sec      per xact       count  %% of total"
331       print @sum1line
332   
333       select @tmp_total = SUM(value)
334       from #tempmonitors
335       where group_name = 'xls' and
336           field_name in ("plc_logrecs", "mldml_plc_logrecs")
337   
338       if @tmp_total = 0 /* Avoid Divide by Zero Errors */
339       begin
340           select @rptline = "  Total ULC Log Records               0.0           0.0           0       n/a"
341           print @rptline
342       end
343       else
344       begin
345           select @tmp_int = value
346           from #tempmonitors
347           where group_name = 'xls' and
348               field_name = "plc_logrecs"
349   
350           select @rptline = "  Fully Logged DMLs" + space(10) +
351               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
352               space(2) +
353               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
354               space(2) +
355               str(@tmp_int, 10) + space(7) +
356               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
357               @psign
358           print @rptline
359   
360           select @tmp_int = value
361           from #tempmonitors
362           where group_name = 'xls' and
363               field_name = "mldml_plc_logrecs"
364   
365           select @rptline = "  Minimally Logged DMLs" + space(6) +
366               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
367               space(2) +
368               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
369               space(2) +
370               str(@tmp_int, 10) + space(7) +
371               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
372               @psign
373           print @rptline
374   
375           print @sum2line
376           select @rptline = "  Total ULC Log Records" + space(6) +
377               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
378               space(2) +
379               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
380               space(2) +
381               str(@tmp_total, 10)
382           print @rptline
383       end
384   
385       print @blankline
386       print "  Max ULC Size During Sample"
387       print "  --------------------------"
388   
389       select @tmp_int = value
390       from #tempmonitors
391       where group_name = 'xls' and
392           field_name = "plc_maxused"
393   
394       select @rptline = "  Fully Logged DMLs" + space(19) +
395           @na_str + space(11) +
396           @na_str + space(2) +
397           str(@tmp_int, 10) + space(7) +
398           @na_str
399       print @rptline
400   
401       select @tmp_int = value
402       from #tempmonitors
403       where group_name = 'xls' and
404           field_name = "mldml_plc_maxused"
405   
406       select @rptline = "  Minimally Logged DMLs" + space(15) +
407           @na_str + space(11) +
408           @na_str + space(2) +
409           str(@tmp_int, 10) + space(7) +
410           @na_str
411       print @rptline
412       print @blankline
413   
414       print "  ML-DMLs Sub-Command Scans       per sec      per xact       count  %% of total"
415       print @sum1line
416   
417       select @tmp_total = SUM(value)
418       from #tempmonitors
419       where group_name = 'xls' and
420           field_name in ("mldml_subcmd_plc_scan",
421               "mldml_subcmd_syslogs_scan")
422   
423       if @tmp_total = 0 /* Avoid Divide by Zero Errors */
424       begin
425           select @rptline = "  Total Sub-Command Scans             0.0           0.0           0       n/a"
426           print @rptline
427       end
428       else
429       begin
430           select @tmp_int = value
431           from #tempmonitors
432           where group_name = 'xls' and
433               field_name = "mldml_subcmd_plc_scan"
434   
435           select @rptline = "  ULC Scans" + space(18) +
436               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
437               space(2) +
438               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
439               space(2) +
440               str(@tmp_int, 10) + space(7) +
441               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
442               @psign
443           print @rptline
444   
445           select @tmp_int = value
446           from #tempmonitors
447           where group_name = 'xls' and
448               field_name = "mldml_subcmd_syslogs_scan"
449   
450           select @rptline = "  Syslogs Scans" + space(14) +
451               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
452               space(2) +
453               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
454               space(2) +
455               str(@tmp_int, 10) + space(7) +
456               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
457               @psign
458           print @rptline
459   
460           print @sum2line
461           select @rptline = "  Total Sub-Command Scans" + space(4) +
462               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
463               space(2) +
464               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
465               space(2) +
466               str(@tmp_total, 10)
467           print @rptline
468       end
469   
470       print @blankline
471       print "  ML-DMLs ULC Efficiency          per sec      per xact       count  %% of total"
472       print @sum1line
473   
474       select @tmp_total = @discarded_subcmd + @logged_subcmd
475       if @tmp_total = 0 /* Avoid Divide by Zero Errors */
476       begin
477           select @rptline = "  Total ML-DML Sub-Commands           0.0           0.0           0       n/a"
478           print @rptline
479       end
480       else
481       begin
482           select @tmp_int = @discarded_subcmd
483   
484           select @rptline = "  Discarded Sub-Commands" + space(5) +
485               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
486               space(2) +
487               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
488               space(2) +
489               str(@tmp_int, 10) + space(7) +
490               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
491               @psign
492           print @rptline
493   
494           select @tmp_int = @logged_subcmd
495   
496           select @rptline = "  Logged Sub-Commands" + space(8) +
497               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
498               space(2) +
499               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
500               space(2) +
501               str(@tmp_int, 10) + space(7) +
502               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
503               @psign
504           print @rptline
505   
506           print @sum2line
507           select @rptline = "  Total ML-DML Sub-Commands" + space(2) +
508               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
509               space(2) +
510               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
511               space(2) +
512               str(@tmp_total, 10)
513           print @rptline
514       end
515   
516       print @blankline
517       print "  ULC Semaphore Requests"
518   
519       select @tmp_total = value
520       from #tempmonitors
521       where group_name = 'xls' and
522           field_name = "plc_lock_calls"
523   
524       if @tmp_total = 0 /* Avoid Divide by Zero Errors */
525       begin
526           select @rptline = "    Total ULC Semaphore Req           0.0           0.0           0       n/a"
527           print @rptline
528       end
529       else
530       begin
531           select @tmp_int2 = value
532           from #tempmonitors
533           where group_name = 'xls' and
534               field_name = "plc_lock_waits"
535   
536           /* calculate "immediately granted" spinlocks */
537           select @tmp_int = @tmp_total - @tmp_int2
538   
539   
540           select @rptline = "    Granted" + space(18) +
541               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
542               space(2) +
543               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
544               space(2) +
545               str(@tmp_int, 10) + space(5) +
546               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
547               @psign
548           print @rptline
549   
550           select @rptline = "    Waited" + space(19) +
551               str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
552               space(2) +
553               str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
554               space(2) +
555               str(@tmp_int2, 10) + space(5) +
556               str(100.0 * @tmp_int2 / @tmp_total, 5, 1) +
557               @psign
558           print @rptline
559   
560           print @sum2line
561           select @rptline = "  Total ULC Semaphore Req" + space(4) +
562               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
563               space(2) +
564               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
565               space(2) +
566               str(@tmp_total, 10)
567   
568   
569           print @rptline
570       end /* else @tmp_total != 0 */
571   
572       print @blankline
573   
574       print "  Log Semaphore Requests"
575   
576       select @tmp_total = SUM(value)
577       from #tempmonitors
578       where group_name = 'xls' and
579           field_name IN ("log_lock_granted", "log_lock_waited", "log_objectlock_needwait")
580   
581       if @tmp_total = 0 /* Avoid Divide by Zero Errors */
582       begin
583           select @rptline = "    Total Log Semaphore Req           0.0           0.0           0       n/a"
584           print @rptline
585       end
586       else
587       begin
588           select @tmp_int = value
589           from #tempmonitors
590           where group_name = 'xls' and
591               field_name = "log_lock_granted"
592   
593           select @rptline = "    Granted" + space(18) +
594               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
595               space(2) +
596               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
597               space(2) +
598               str(@tmp_int, 10) + space(5) +
599               str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
600           print @rptline
601   
602           select @tmp_int = value
603           from #tempmonitors
604           where group_name = 'xls' and
605               field_name = "log_lock_waited"
606   
607           select @rptline = "    Local Waited" + space(13) +
608               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
609               space(2) +
610               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
611               space(2) +
612               str(@tmp_int, 10) + space(5) +
613               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
614               @psign
615           print @rptline
616   
617           select @tmp_int = value
618           from #tempmonitors
619           where group_name = 'xls' and
620               field_name = "log_objectlock_needwait"
621   
622           select @rptline = "    Global Waited" + space(12) +
623               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
624               space(2) +
625               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
626               space(2) +
627               str(@tmp_int, 10) + space(5) +
628               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
629               @psign
630           print @rptline
631   
632           print @sum2line
633           select @rptline = "  Total Log Semaphore Req" + space(4) +
634               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
635               space(2) +
636               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
637               space(2) +
638               str(@tmp_total, 10)
639           print @rptline
640       end
641   
642       print @blankline
643   
644   
645   
646       select @tmp_int = SUM(value)
647       from #tempmonitors
648       where group_name like "buffer_%" and
649           field_name = "last_log_page_writes"
650   
651       select @tmp_int = SUM(value)
652       from #tempmonitors
653       where group_name like "buffer_%" and
654           field_name = "log_page_writes"
655   
656       select @rptline = "  Transaction Log Writes" + space(5) +
657           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
658           space(2) +
659           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
660           space(2) +
661           str(@tmp_int, 10) + space(7) +
662           @na_str
663       print @rptline
664   
665       select @tmp_int2 = value
666       from #tempmonitors
667       where group_name = "access" and
668           field_name = "log_page_allocations"
669   
670       select @rptline = "  Transaction Log Alloc" + space(6) +
671           str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
672           space(2) +
673           str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
674           space(2) +
675           str(@tmp_int2, 10) + space(7) +
676           @na_str
677       print @rptline
678   
679       if @tmp_int2 != 0 /* avoid divide by zero errors */
680       begin
681           select @rptline = "  Avg # Writes per Log Page" + space(11) +
682               @na_str + space(11) +
683               @na_str + space(2) +
684               str(convert(real, @tmp_int) / @tmp_int2, 10, 5) + space(7) +
685               @na_str
686           print @rptline
687       end
688   
689       print @blankline
690   
691       if @Reco = 'Y'
692       begin
693           select @recotxt = "  Tuning Recommendations for Transaction Management"
694           select @recoline = "  -------------------------------------------------"
695           select @reco_hdr_prn = 0
696   
697           /*
698           ** If the % of flushes on account of ULC being full is > 20%
699           ** consider increasing the 'user log cache size'
700           */
701           if @reco_by_full_ulc > 20
702           begin
703               if (@reco_hdr_prn = 0)
704               begin
705                   print @recotxt
706                   print @recoline
707                   select @reco_hdr_prn = 1
708               end
709   
710               print "  - Consider increasing the 'user log cache size'"
711               print "    configuration parameter."
712               print @blankline
713               select @reco_hdr_prn = 1
714           end
715   
716           /*
717           ** If the % of flushes on account of SLR is > 20%
718           ** and the % of flushes on account of ULC being full
719           ** is < 20% consider decreasing the user log cache size
720           */
721           if @reco_by_slr > 20 and @reco_by_full_ulc < 20
722           begin
723               if (@reco_hdr_prn = 0)
724               begin
725                   print @recotxt
726                   print @recoline
727                   select @reco_hdr_prn = 1
728               end
729   
730               print "  - Consider decreasing the 'user log cache size'"
731               print "    configuration parameter if it is greater than the"
732               print "    logical database page size."
733               print @blankline
734               select @reco_hdr_prn = 1
735           end
736       end
737   
738       return 0
739   


exec sp_procxmode 'sp_sysmon_xactmgmt', 'AnyMode'
go

Grant Execute on sp_sysmon_xactmgmt to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_xactmgmt  
 MLCH 3 Char type with length>30 char(80) 16
 MLCH 3 Char type with length>30 char(67) 18
 MLCH 3 Char type with length>30 char(80) 23
 MLCH 3 Char type with length>30 char(80) 24
 MLCH 3 Char type with length>30 char(80) 39
 MLCH 3 Char type with length>30 char(80) 40
 MUCO 3 Useless Code Useless Brackets 703
 MUCO 3 Useless Code Useless Brackets 723
 QAFM 3 Var Assignment from potentially many rows 82
 QAFM 3 Var Assignment from potentially many rows 97
 QAFM 3 Var Assignment from potentially many rows 112
 QAFM 3 Var Assignment from potentially many rows 145
 QAFM 3 Var Assignment from potentially many rows 162
 QAFM 3 Var Assignment from potentially many rows 182
 QAFM 3 Var Assignment from potentially many rows 203
 QAFM 3 Var Assignment from potentially many rows 224
 QAFM 3 Var Assignment from potentially many rows 239
 QAFM 3 Var Assignment from potentially many rows 282
 QAFM 3 Var Assignment from potentially many rows 298
 QAFM 3 Var Assignment from potentially many rows 303
 QAFM 3 Var Assignment from potentially many rows 345
 QAFM 3 Var Assignment from potentially many rows 360
 QAFM 3 Var Assignment from potentially many rows 389
 QAFM 3 Var Assignment from potentially many rows 401
 QAFM 3 Var Assignment from potentially many rows 430
 QAFM 3 Var Assignment from potentially many rows 445
 QAFM 3 Var Assignment from potentially many rows 519
 QAFM 3 Var Assignment from potentially many rows 531
 QAFM 3 Var Assignment from potentially many rows 588
 QAFM 3 Var Assignment from potentially many rows 602
 QAFM 3 Var Assignment from potentially many rows 617
 QAFM 3 Var Assignment from potentially many rows 665
 VNRD 3 Variable is not read @reco_hdr_prn 734
 MTR1 2 Metrics: Comments Ratio Comments: 9% 5
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 50 = 49dec - 1exi + 2 5
 MTR3 2 Metrics: Query Complexity Complexity: 343 5

DEPENDENCIES
PROCS AND TABLES USED
reads table tempdb..#tempmonitors (1) 

CALLERS
called by proc sybsystemprocs..sp_sysmon_analyze  
   called by proc sybsystemprocs..sp_sysmon