DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_xactsum  14 déc. 14Defects Propagation Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** the type of activites performed by transactions running during
4     ** the sampling period, including the number of transactions and 
5     ** a summary of insert, update and delete activity.
6     **
7     */
8     create or replace procedure sp_sysmon_xactsum
9         @NumElapsedMs int, /* for "per Elapsed second" calculations */
10        @NumXacts int /* for per transactions calculations */
11    as
12    
13        /* --------- declare local variables --------- */
14        declare @NumXactOps int /* total # of rows affected by 
15        ** inserts/updates/deletes */
16        declare @tmp_int int /* temp var for integer storage */
17        declare @tmp_int2 int /* temp var for integer storage */
18        declare @tmp_total int /* temp var for summing 'total #s' data */
19        declare @sum1line char(80) /* string to delimit total lines without 
20        ** percent calc on printout */
21        declare @sum2line char(67) /* string to delimit total lines with percent 
22        ** calc on printout */
23        declare @sum3line char(67) /* string to delimit total lines with percent 
24        ** calc on printout */
25        declare @blankline char(1) /* to print blank line */
26        declare @psign char(3) /* hold a percent sign (%) for print out */
27        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
28        declare @rptline char(80) /* formatted stats line for print statement */
29        declare @section char(80) /* string to delimit sections on printout */
30        /* --------- Setup Environment --------- */
31        set nocount on /* disable row counts being sent to client */
32    
33        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
34        select @sum2line = "  -------------------------  ------------  ------------  ----------"
35        select @sum3line = "  =========================  ============  ============  =========="
36        select @blankline = " "
37        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
38        select @na_str = "n/a"
39        select @section = "==============================================================================="
40    
41        /* ================ Transaction Profile Section ================= */
42        print @section
43        print @blankline
44        print "Transaction Profile"
45        print "-------------------"
46        print @blankline
47    
48        /* -------- transaction summary ------------*/
49        print "  Transaction Summary             per sec      per xact       count  %% of total"
50        print @sum1line
51    
52        select @rptline = "    Committed Xacts" + space(10) +
53            str(@NumXacts / (@NumElapsedMs / 1000.0), 12, 1) +
54            space(11) + @na_str + space(2) +
55            str(@NumXacts, 10) + space(5) + @na_str
56        print @rptline
57        print @blankline
58    
59        /*
60        ** transaction detail
61        */
62    
63        print "  Transaction Detail              per sec      per xact       count  %% of total"
64        print @sum1line
65    
66        /* 
67        **  get total number of Transaction Operations for percentage 
68        **  calculations on total lines 
69        */
70        select @NumXactOps = SUM(value)
71        from #tempmonitors
72        where (group_name = 'access' and field_name IN
73                ("ncinsert", "cinsert", "deferred_update",
74                    "direct_inplace_update", "direct_notinplace_update",
75                    "direct_expensive_update", "delete", "bulk_fast_insert",
76                    "mldml_ncinsert", "mldml_cinsert",
77                    "mldml_direct_inplace_update",
78                    "mldml_direct_notinplace_update",
79                    "mldml_direct_expensive_update", "mldml_delete",
80                    "bulk_fast_log_insert"))
81            OR (group_name = 'dolaccess' and field_name IN
82                ("dolinsert", "dolupdates", "doldelete_total",
83                    "mldml_dolinsert", "mldml_dolupdates",
84                    "mldml_doldelete_total"))
85    
86        if @NumXactOps = 0 /* Avoid Divide by Zero Errors */
87        begin
88            select @rptline = "  Total Rows Affected                 0.0           0.0           0       n/a"
89            print @rptline
90        end
91        else
92        begin
93            print "  Inserts"
94    
95            select @tmp_total = SUM(value)
96            from #tempmonitors
97            where (group_name = 'access' and
98                    field_name IN ("ncinsert", "cinsert",
99                        "bulk_fast_insert", "mldml_ncinsert",
100                       "mldml_cinsert",
101                       "bulk_fast_log_insert"))
102               or (group_name = 'dolaccess' and
103                   field_name IN ("dolinsert", "mldml_dolinsert"))
104   
105           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
106           begin
107               select @rptline = "    Total Rows Inserted               0.0           0.0           0       n/a"
108               print @rptline
109           end
110           else
111           begin
112   
113               print "    Fully Logged"
114               select @tmp_int = value
115               from #tempmonitors
116               where group_name = 'access' and
117                   field_name = "ncinsert"
118   
119               select @rptline = "      APL Heap Table" + space(9) +
120                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
121                   space(2) +
122                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
123                   space(2) +
124                   str(@tmp_int, 10) + space(5) +
125                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
126               print @rptline
127   
128               select @tmp_int = value
129               from #tempmonitors
130               where group_name = 'access' and
131                   field_name = "cinsert"
132   
133               select @rptline = "      APL Clustered Table" + space(4) +
134                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
135                   space(2) +
136                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
137                   space(2) +
138                   str(@tmp_int, 10) + space(5) +
139                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
140               print @rptline
141   
142               select @tmp_int = value
143               from #tempmonitors
144               where group_name = 'dolaccess' and
145                   field_name = "dolinsert"
146   
147               select @rptline = "      Data Only Lock Table" + space(3) +
148                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
149                   space(2) +
150                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
151                   space(2) +
152                   str(@tmp_int, 10) + space(5) +
153                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
154               print @rptline
155   
156               select @tmp_int = value
157               from #tempmonitors
158               where group_name = 'access' and
159                   field_name = "bulk_fast_insert"
160   
161               select @rptline = "      Fast Bulk Insert" + space(7) +
162                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
163                   space(2) +
164                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
165                   space(2) +
166                   str(@tmp_int, 10) + space(5) +
167                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
168               print @rptline
169   
170               select @tmp_int = value
171               from #tempmonitors
172               where group_name = 'access' and
173                   field_name = "bulk_fast_log_insert"
174   
175               select @rptline = "      Fast Log Bulk Insert" + space(3) +
176                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
177                   space(2) +
178                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
179                   space(2) +
180                   str(@tmp_int, 10) + space(5) +
181                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
182               print @rptline
183   
184               print "    Minimally Logged"
185               select @tmp_int = value
186               from #tempmonitors
187               where group_name = 'access' and
188                   field_name = "mldml_ncinsert"
189   
190               select @rptline = "      APL Heap Table" + space(9) +
191                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
192                   space(2) +
193                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
194                   space(2) +
195                   str(@tmp_int, 10) + space(5) +
196                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
197               print @rptline
198   
199               select @tmp_int = value
200               from #tempmonitors
201               where group_name = 'access' and
202                   field_name = "mldml_cinsert"
203   
204               select @rptline = "      APL Clustered Table" + space(4) +
205                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
206                   space(2) +
207                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
208                   space(2) +
209                   str(@tmp_int, 10) + space(5) +
210                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
211               print @rptline
212   
213               select @tmp_int = value
214               from #tempmonitors
215               where group_name = 'dolaccess' and
216                   field_name = "mldml_dolinsert"
217   
218               select @rptline = "      Data Only Lock Table" + space(3) +
219                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
220                   space(2) +
221                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
222                   space(2) +
223                   str(@tmp_int, 10) + space(5) +
224                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
225               print @rptline
226   
227           end /* else */
228   
229           print @sum1line
230           select @rptline = "  Total Rows Inserted" + space(8) +
231               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
232               space(2) +
233               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
234               space(2) +
235               str(@tmp_total, 10) + space(5) +
236               str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign
237           print @rptline
238           print @blankline
239   
240           print "  Updates"
241   
242           select @tmp_total = SUM(value)
243           from #tempmonitors
244           where (group_name = 'access' and field_name IN
245                   ("deferred_update", "direct_inplace_update",
246                       "direct_notinplace_update", "direct_expensive_update",
247                       "mldml_deferred_update", "mldml_direct_inplace_update",
248                       "mldml_direct_notinplace_update",
249                       "mldml_direct_expensive_update"))
250               OR (group_name = 'dolaccess' and field_name IN ("dolupdates",
251                       "mldml_dolupdates"))
252   
253           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
254           begin
255               select @rptline = "    Total Rows Updated                0.0           0.0           0       n/a"
256               print @rptline
257           end
258           else
259           begin
260   
261               print "    Fully Logged"
262               select @tmp_int = value
263               from #tempmonitors
264               where group_name = 'access' and
265                   field_name = "deferred_update"
266   
267               select @rptline = "      APL Deferred" + space(11) +
268                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
269                   space(2) +
270                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
271                   space(2) +
272                   str(@tmp_int, 10) + space(5) +
273                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
274               print @rptline
275   
276               select @tmp_int = value
277               from #tempmonitors
278               where group_name = 'access' and
279                   field_name = "direct_inplace_update"
280   
281               select @rptline = "      APL Direct In-place" + space(4) +
282                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
283                   space(2) +
284                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
285                   space(2) +
286                   str(@tmp_int, 10) + space(5) +
287                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
288               print @rptline
289   
290               select @tmp_int = value
291               from #tempmonitors
292               where group_name = 'access' and
293                   field_name = "direct_notinplace_update"
294   
295               select @rptline = "      APL Direct Cheap" + space(7) +
296                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
297                   space(2) +
298                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
299                   space(2) +
300                   str(@tmp_int, 10) + space(5) +
301                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
302               print @rptline
303   
304               select @tmp_int = value
305               from #tempmonitors
306               where group_name = 'access' and
307                   field_name = "direct_expensive_update"
308   
309               select @rptline = "      APL Direct Expensive" + space(3) +
310                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
311                   space(2) +
312                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
313                   space(2) +
314                   str(@tmp_int, 10) + space(5) +
315                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
316               print @rptline
317   
318               select @tmp_int2 = value
319               from #tempmonitors
320               where group_name = 'dolaccess' and
321                   field_name = "dolupdate_deferred"
322   
323               select @rptline = "      DOL Deferred" + space(11) +
324                   str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
325                   space(2) +
326                   str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
327                   space(2) +
328                   str(@tmp_int2, 10) + space(5) +
329                   str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign
330               print @rptline
331   
332               select @tmp_int = value - @tmp_int2
333               from #tempmonitors
334               where group_name = 'dolaccess' and
335                   field_name = "dolupdates"
336   
337               select @rptline = "      DOL Direct" + space(13) +
338                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
339                   space(2) +
340                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
341                   space(2) +
342                   str(@tmp_int, 10) + space(5) +
343                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
344               print @rptline
345   
346               print "    Minimally Logged"
347               select @tmp_int = value
348               from #tempmonitors
349               where group_name = 'access' and
350                   field_name = "mldml_direct_inplace_update"
351   
352               select @rptline = "      APL Direct In-place" + space(4) +
353                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
354                   space(2) +
355                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
356                   space(2) +
357                   str(@tmp_int, 10) + space(5) +
358                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
359               print @rptline
360   
361               select @tmp_int = value
362               from #tempmonitors
363               where group_name = 'access' and
364                   field_name = "mldml_direct_notinplace_update"
365   
366               select @rptline = "      APL Direct Cheap" + space(7) +
367                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
368                   space(2) +
369                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
370                   space(2) +
371                   str(@tmp_int, 10) + space(5) +
372                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
373               print @rptline
374   
375               select @tmp_int = value
376               from #tempmonitors
377               where group_name = 'access' and
378                   field_name = "mldml_direct_expensive_update"
379   
380               select @rptline = "      APL Direct Expensive" + space(3) +
381                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
382                   space(2) +
383                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
384                   space(2) +
385                   str(@tmp_int, 10) + space(5) +
386                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
387               print @rptline
388   
389   
390               select @tmp_int = value
391               from #tempmonitors
392               where group_name = 'dolaccess' and
393                   field_name = "mldml_dolupdates"
394   
395               select @rptline = "      DOL Direct" + space(13) +
396                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
397                   space(2) +
398                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
399                   space(2) +
400                   str(@tmp_int, 10) + space(5) +
401                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
402               print @rptline
403   
404           end
405   
406           print @sum1line
407           select @rptline = "  Total Rows Updated" + space(9) +
408               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
409               space(2) +
410               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
411               space(2) +
412               str(@tmp_total, 10) + space(5) +
413               str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign
414           print @rptline
415           print @blankline
416   
417   
418           print "  Data Only Locked Updates"
419   
420           select @tmp_total = SUM(value)
421           from #tempmonitors
422           where group_name = 'dolaccess' and field_name IN
423               ("dolupdate_replace", "mldml_dolupdate_shrink",
424                   "dolupdate_expand_incfs", "mldml_dolupdate_expand_shift",
425                   "dolupdate_expand_after_gc",
426                   "mldml_dolupdate_forward_firstlvl",
427                   "dolupdate_forward_secondlvl",
428                   "mldml_dolupdate_migrate_rowhome",
429                   "mldml_dolupdate_replace", "mldml_dolupdate_shrink",
430                   "mldml_dolupdate_expand_incfs", "mldml_dolupdate_expand_shift",
431                   "mldml_dolupdate_expand_after_gc",
432                   "mldml_dolupdate_forward_firstlvl",
433                   "mldml_dolupdate_forward_secondlvl",
434                   "mldml_dolupdate_migrate_rowhome")
435   
436           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
437           begin
438               select @rptline = "    Total Rows Updated                0.0           0.0           0       n/a"
439               print @rptline
440           end
441           else
442           begin
443   
444               print "    Fully Logged"
445               select @tmp_int = value
446               from #tempmonitors
447               where group_name = 'dolaccess' and
448                   field_name = "dolupdate_replace"
449   
450               select @rptline = "      DOL Replace" + space(12) +
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(5) +
456                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
457               print @rptline
458   
459               select @tmp_int = value
460               from #tempmonitors
461               where group_name = 'dolaccess' and
462                   field_name = "dolupdate_shrink"
463   
464               select @rptline = "      DOL Shrink" + space(13) +
465                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
466                   space(2) +
467                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
468                   space(2) +
469                   str(@tmp_int, 10) + space(5) +
470                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
471               print @rptline
472   
473               select @tmp_int = value
474               from #tempmonitors
475               where group_name = 'dolaccess' and
476                   field_name = "dolupdate_expand_incfs"
477   
478               select @rptline = "      DOL Cheap Expand" + space(7) +
479                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
480                   space(2) +
481                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
482                   space(2) +
483                   str(@tmp_int, 10) + space(5) +
484                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
485               print @rptline
486   
487               select @tmp_int = SUM(value)
488               from #tempmonitors
489               where group_name = 'dolaccess' and
490                   field_name in ("dolupdate_expand_shift",
491                       "dolupdate_expand_after_gc")
492   
493               select @rptline = "      DOL Expensive Expand" + space(3) +
494                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
495                   space(2) +
496                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
497                   space(2) +
498                   str(@tmp_int, 10) + space(5) +
499                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
500               print @rptline
501   
502               select @tmp_int = SUM(value)
503               from #tempmonitors
504               where group_name = 'dolaccess' and
505                   field_name in ("dolupdate_forward_firstlvl",
506                       "dolupdate_forward_secondlvl")
507   
508               select @rptline = "      DOL Expand & Forward" + space(3) +
509                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
510                   space(2) +
511                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
512                   space(2) +
513                   str(@tmp_int, 10) + space(5) +
514                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
515               print @rptline
516   
517               select @tmp_int = value
518               from #tempmonitors
519               where group_name = 'dolaccess' and
520                   field_name = "dolupdate_migrate_rowhome"
521   
522               select @rptline = "      DOL Fwd Row Returned" + space(3) +
523                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
524                   space(2) +
525                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
526                   space(2) +
527                   str(@tmp_int, 10) + space(5) +
528                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
529               print @rptline
530   
531               print "    Minimally Logged"
532               select @tmp_int = value
533               from #tempmonitors
534               where group_name = 'dolaccess' and
535                   field_name = "mldml_dolupdate_replace"
536   
537               select @rptline = "      DOL Replace" + space(12) +
538                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
539                   space(2) +
540                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
541                   space(2) +
542                   str(@tmp_int, 10) + space(5) +
543                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
544               print @rptline
545   
546               select @tmp_int = value
547               from #tempmonitors
548               where group_name = 'dolaccess' and
549                   field_name = "mldml_dolupdate_shrink"
550   
551               select @rptline = "      DOL Shrink" + space(13) +
552                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
553                   space(2) +
554                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
555                   space(2) +
556                   str(@tmp_int, 10) + space(5) +
557                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
558               print @rptline
559   
560               select @tmp_int = value
561               from #tempmonitors
562               where group_name = 'dolaccess' and
563                   field_name = "mldml_dolupdate_expand_incfs"
564   
565               select @rptline = "      DOL Cheap Expand" + space(7) +
566                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
567                   space(2) +
568                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
569                   space(2) +
570                   str(@tmp_int, 10) + space(5) +
571                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
572               print @rptline
573   
574               select @tmp_int = SUM(value)
575               from #tempmonitors
576               where group_name = 'dolaccess' and
577                   field_name in ("dmldml_olupdate_expand_shift",
578                       "mldml_dolupdate_expand_after_gc")
579   
580               select @rptline = "      DOL Expensive Expand" + space(3) +
581                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
582                   space(2) +
583                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
584                   space(2) +
585                   str(@tmp_int, 10) + space(5) +
586                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
587               print @rptline
588   
589               select @tmp_int = SUM(value)
590               from #tempmonitors
591               where group_name = 'dolaccess' and
592                   field_name in ("mldml_dolupdate_forward_firstlvl",
593                       "mldml_dolupdate_forward_secondlvl")
594   
595               select @rptline = "      DOL Expand & Forward" + space(3) +
596                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
597                   space(2) +
598                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
599                   space(2) +
600                   str(@tmp_int, 10) + space(5) +
601                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
602               print @rptline
603   
604               select @tmp_int = value
605               from #tempmonitors
606               where group_name = 'dolaccess' and
607                   field_name = "mldml_dolupdate_migrate_rowhome"
608   
609               select @rptline = "      DOL Fwd Row Returned" + space(3) +
610                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
611                   space(2) +
612                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
613                   space(2) +
614                   str(@tmp_int, 10) + space(5) +
615                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
616               print @rptline
617           end
618   
619           print @sum1line
620           select @rptline = "  Total DOL Rows Updated" + space(5) +
621               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
622               space(2) +
623               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
624               space(2) +
625               str(@tmp_total, 10) + space(5) +
626               str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign
627           print @rptline
628           print @blankline
629   
630           print "  Deletes"
631   
632           select @tmp_total = SUM(value)
633           from #tempmonitors
634           where (group_name = 'access' and
635                   field_name IN ("delete", "mldml_delete"))
636               or (group_name = 'dolaccess' and
637                   field_name IN ("doldelete_total",
638                       "mldml_doldelete_total"))
639   
640           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
641           begin
642               select @rptline = "    Total Rows Deleted                0.0           0.0           0       n/a"
643               print @rptline
644           end
645           else
646           begin
647   
648               print "    Fully Logged"
649               select @tmp_int = value
650               from #tempmonitors
651               where group_name = 'access' and
652                   field_name = "delete_deferred"
653   
654               select @rptline = "      APL Deferred" + space(11) +
655                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
656                   space(2) +
657                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
658                   space(2) +
659                   str(@tmp_int, 10) + space(5) +
660                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
661               print @rptline
662   
663               /* Calc Direct Deletes as access.delete - access.delete_deferred */
664   
665               select @tmp_int2 = value - @tmp_int
666               from #tempmonitors
667               where group_name = 'access' and
668                   field_name = "delete"
669   
670               select @rptline = "      APL Direct" + space(13) +
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(5) +
676                   str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign
677               print @rptline
678   
679               select @tmp_int = value
680               from #tempmonitors
681               where group_name = 'dolaccess' and
682                   field_name = "doldelete_total"
683   
684               select @rptline = "      DOL" + space(20) +
685                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
686                   space(2) +
687                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
688                   space(2) +
689                   str(@tmp_int, 10) + space(5) +
690                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
691               print @rptline
692   
693               print "    Minimally Logged"
694   
695               select @tmp_int = value
696               from #tempmonitors
697               where group_name = 'access' and
698                   field_name = "mldml_delete"
699   
700               select @rptline = "      APL Direct" + space(13) +
701                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
702                   space(2) +
703                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
704                   space(2) +
705                   str(@tmp_int, 10) + space(5) +
706                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
707               print @rptline
708   
709               select @tmp_int = value
710               from #tempmonitors
711               where group_name = 'dolaccess' and
712                   field_name = "mldml_doldelete_total"
713   
714               select @rptline = "      DOL" + space(20) +
715                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
716                   space(2) +
717                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
718                   space(2) +
719                   str(@tmp_int, 10) + space(5) +
720                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
721               print @rptline
722   
723               print @sum1line
724               select @rptline = "  Total Rows Deleted" + space(9) +
725                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
726                   space(2) +
727                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
728                   space(2) +
729                   str(@tmp_total, 10) + space(5) +
730                   str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign
731               print @rptline
732           end
733   
734           print @blankline
735           print @sum3line
736           select @rptline = "  Total Rows Affected " + space(7) +
737               str(@NumXactOps / (@NumElapsedMs / 1000.0), 12, 1) +
738               space(2) +
739               str(@NumXactOps / convert(real, @NumXacts), 12, 1) +
740               space(2) +
741               str(@NumXactOps, 10)
742           print @rptline
743           print @sum3line
744       end /* else @NumXactOps != 0 */
745   
746       print @blankline
747       return 0
748   

DEFECTS
 MLCH 3 Char type with length>30 char(80) 19
 MLCH 3 Char type with length>30 char(67) 21
 MLCH 3 Char type with length>30 char(67) 23
 MLCH 3 Char type with length>30 char(80) 28
 MLCH 3 Char type with length>30 char(80) 29
 QAFM 3 Var Assignment from potentially many rows 114
 QAFM 3 Var Assignment from potentially many rows 128
 QAFM 3 Var Assignment from potentially many rows 142
 QAFM 3 Var Assignment from potentially many rows 156
 QAFM 3 Var Assignment from potentially many rows 170
 QAFM 3 Var Assignment from potentially many rows 185
 QAFM 3 Var Assignment from potentially many rows 199
 QAFM 3 Var Assignment from potentially many rows 213
 QAFM 3 Var Assignment from potentially many rows 262
 QAFM 3 Var Assignment from potentially many rows 276
 QAFM 3 Var Assignment from potentially many rows 290
 QAFM 3 Var Assignment from potentially many rows 304
 QAFM 3 Var Assignment from potentially many rows 318
 QAFM 3 Var Assignment from potentially many rows 332
 QAFM 3 Var Assignment from potentially many rows 347
 QAFM 3 Var Assignment from potentially many rows 361
 QAFM 3 Var Assignment from potentially many rows 375
 QAFM 3 Var Assignment from potentially many rows 390
 QAFM 3 Var Assignment from potentially many rows 445
 QAFM 3 Var Assignment from potentially many rows 459
 QAFM 3 Var Assignment from potentially many rows 473
 QAFM 3 Var Assignment from potentially many rows 517
 QAFM 3 Var Assignment from potentially many rows 532
 QAFM 3 Var Assignment from potentially many rows 546
 QAFM 3 Var Assignment from potentially many rows 560
 QAFM 3 Var Assignment from potentially many rows 604
 QAFM 3 Var Assignment from potentially many rows 649
 QAFM 3 Var Assignment from potentially many rows 665
 QAFM 3 Var Assignment from potentially many rows 679
 QAFM 3 Var Assignment from potentially many rows 695
 QAFM 3 Var Assignment from potentially many rows 709
 QTLO 3 Top-Level OR 72
 QTLO 3 Top-Level OR 97
 QTLO 3 Top-Level OR 244
 QTLO 3 Top-Level OR 634
 VNRD 3 Variable is not read @sum2line 34
 MTR1 2 Metrics: Comments Ratio Comments: 6% 8
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 55 = 54dec - 1exi + 2 8
 MTR3 2 Metrics: Query Complexity Complexity: 325 8

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

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