DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_xactsum  31 Aug 14Defects 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 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            OR (group_name = 'dolaccess' and field_name IN
81                ("dolinsert", "dolupdates", "doldelete_total",
82                    "mldml_dolinsert", "mldml_dolupdates",
83                    "mldml_doldelete_total"))
84    
85        if @NumXactOps = 0 /* Avoid Divide by Zero Errors */
86        begin
87            select @rptline = "  Total Rows Affected                 0.0           0.0           0       n/a"
88            print @rptline
89        end
90        else
91        begin
92            print "  Inserts"
93    
94            select @tmp_total = SUM(value)
95            from #tempmonitors
96            where (group_name = 'access' and
97                    field_name IN ("ncinsert", "cinsert",
98                        "bulk_fast_insert", "mldml_ncinsert",
99                        "mldml_cinsert"))
100               or (group_name = 'dolaccess' and
101                   field_name IN ("dolinsert", "mldml_dolinsert"))
102   
103           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
104           begin
105               select @rptline = "    Total Rows Inserted               0.0           0.0           0       n/a"
106               print @rptline
107           end
108           else
109           begin
110   
111               print "    Fully Logged"
112               select @tmp_int = value
113               from #tempmonitors
114               where group_name = 'access' and
115                   field_name = "ncinsert"
116   
117               select @rptline = "      APL Heap Table" + space(9) +
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) + @psign
124               print @rptline
125   
126               select @tmp_int = value
127               from #tempmonitors
128               where group_name = 'access' and
129                   field_name = "cinsert"
130   
131               select @rptline = "      APL Clustered Table" + space(4) +
132                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
133                   space(2) +
134                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
135                   space(2) +
136                   str(@tmp_int, 10) + space(5) +
137                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
138               print @rptline
139   
140               select @tmp_int = value
141               from #tempmonitors
142               where group_name = 'dolaccess' and
143                   field_name = "dolinsert"
144   
145               select @rptline = "      Data Only Lock Table" + space(3) +
146                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
147                   space(2) +
148                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
149                   space(2) +
150                   str(@tmp_int, 10) + space(5) +
151                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
152               print @rptline
153   
154               select @tmp_int = value
155               from #tempmonitors
156               where group_name = 'access' and
157                   field_name = "bulk_fast_insert"
158   
159               select @rptline = "      Fast Bulk Insert" + space(7) +
160                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
161                   space(2) +
162                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
163                   space(2) +
164                   str(@tmp_int, 10) + space(5) +
165                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
166               print @rptline
167   
168               print "    Minimally Logged"
169               select @tmp_int = value
170               from #tempmonitors
171               where group_name = 'access' and
172                   field_name = "mldml_ncinsert"
173   
174               select @rptline = "      APL Heap Table" + space(9) +
175                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
176                   space(2) +
177                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
178                   space(2) +
179                   str(@tmp_int, 10) + space(5) +
180                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
181               print @rptline
182   
183               select @tmp_int = value
184               from #tempmonitors
185               where group_name = 'access' and
186                   field_name = "mldml_cinsert"
187   
188               select @rptline = "      APL Clustered Table" + space(4) +
189                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
190                   space(2) +
191                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
192                   space(2) +
193                   str(@tmp_int, 10) + space(5) +
194                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
195               print @rptline
196   
197               select @tmp_int = value
198               from #tempmonitors
199               where group_name = 'dolaccess' and
200                   field_name = "mldml_dolinsert"
201   
202               select @rptline = "      Data Only Lock Table" + space(3) +
203                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
204                   space(2) +
205                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
206                   space(2) +
207                   str(@tmp_int, 10) + space(5) +
208                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
209               print @rptline
210   
211           end /* else */
212   
213           print @sum1line
214           select @rptline = "  Total Rows Inserted" + space(8) +
215               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
216               space(2) +
217               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
218               space(2) +
219               str(@tmp_total, 10) + space(5) +
220               str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign
221           print @rptline
222           print @blankline
223   
224           print "  Updates"
225   
226           select @tmp_total = SUM(value)
227           from #tempmonitors
228           where (group_name = 'access' and field_name IN
229                   ("deferred_update", "direct_inplace_update",
230                       "direct_notinplace_update", "direct_expensive_update",
231                       "mldml_deferred_update", "mldml_direct_inplace_update",
232                       "mldml_direct_notinplace_update",
233                       "mldml_direct_expensive_update"))
234               OR (group_name = 'dolaccess' and field_name IN ("dolupdates",
235                       "mldml_dolupdates"))
236   
237           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
238           begin
239               select @rptline = "    Total Rows Updated                0.0           0.0           0       n/a"
240               print @rptline
241           end
242           else
243           begin
244   
245               print "    Fully Logged"
246               select @tmp_int = value
247               from #tempmonitors
248               where group_name = 'access' and
249                   field_name = "deferred_update"
250   
251               select @rptline = "      APL Deferred" + space(11) +
252                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
253                   space(2) +
254                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
255                   space(2) +
256                   str(@tmp_int, 10) + space(5) +
257                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
258               print @rptline
259   
260               select @tmp_int = value
261               from #tempmonitors
262               where group_name = 'access' and
263                   field_name = "direct_inplace_update"
264   
265               select @rptline = "      APL Direct In-place" + space(4) +
266                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
267                   space(2) +
268                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
269                   space(2) +
270                   str(@tmp_int, 10) + space(5) +
271                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
272               print @rptline
273   
274               select @tmp_int = value
275               from #tempmonitors
276               where group_name = 'access' and
277                   field_name = "direct_notinplace_update"
278   
279               select @rptline = "      APL Direct Cheap" + space(7) +
280                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
281                   space(2) +
282                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
283                   space(2) +
284                   str(@tmp_int, 10) + space(5) +
285                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
286               print @rptline
287   
288               select @tmp_int = value
289               from #tempmonitors
290               where group_name = 'access' and
291                   field_name = "direct_expensive_update"
292   
293               select @rptline = "      APL Direct Expensive" + space(3) +
294                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
295                   space(2) +
296                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
297                   space(2) +
298                   str(@tmp_int, 10) + space(5) +
299                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
300               print @rptline
301   
302               select @tmp_int2 = value
303               from #tempmonitors
304               where group_name = 'dolaccess' and
305                   field_name = "dolupdate_deferred"
306   
307               select @rptline = "      DOL Deferred" + space(11) +
308                   str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
309                   space(2) +
310                   str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
311                   space(2) +
312                   str(@tmp_int2, 10) + space(5) +
313                   str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign
314               print @rptline
315   
316               select @tmp_int = value - @tmp_int2
317               from #tempmonitors
318               where group_name = 'dolaccess' and
319                   field_name = "dolupdates"
320   
321               select @rptline = "      DOL Direct" + space(13) +
322                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
323                   space(2) +
324                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
325                   space(2) +
326                   str(@tmp_int, 10) + space(5) +
327                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
328               print @rptline
329   
330               print "    Minimally Logged"
331               select @tmp_int = value
332               from #tempmonitors
333               where group_name = 'access' and
334                   field_name = "mldml_direct_inplace_update"
335   
336               select @rptline = "      APL Direct In-place" + space(4) +
337                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
338                   space(2) +
339                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
340                   space(2) +
341                   str(@tmp_int, 10) + space(5) +
342                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
343               print @rptline
344   
345               select @tmp_int = value
346               from #tempmonitors
347               where group_name = 'access' and
348                   field_name = "mldml_direct_notinplace_update"
349   
350               select @rptline = "      APL Direct Cheap" + space(7) +
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(5) +
356                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
357               print @rptline
358   
359               select @tmp_int = value
360               from #tempmonitors
361               where group_name = 'access' and
362                   field_name = "mldml_direct_expensive_update"
363   
364               select @rptline = "      APL Direct Expensive" + space(3) +
365                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
366                   space(2) +
367                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
368                   space(2) +
369                   str(@tmp_int, 10) + space(5) +
370                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
371               print @rptline
372   
373   
374               select @tmp_int = value
375               from #tempmonitors
376               where group_name = 'dolaccess' and
377                   field_name = "mldml_dolupdates"
378   
379               select @rptline = "      DOL Direct" + space(13) +
380                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
381                   space(2) +
382                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
383                   space(2) +
384                   str(@tmp_int, 10) + space(5) +
385                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
386               print @rptline
387   
388           end
389   
390           print @sum1line
391           select @rptline = "  Total Rows Updated" + space(9) +
392               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
393               space(2) +
394               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
395               space(2) +
396               str(@tmp_total, 10) + space(5) +
397               str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign
398           print @rptline
399           print @blankline
400   
401   
402           print "  Data Only Locked Updates"
403   
404           select @tmp_total = SUM(value)
405           from #tempmonitors
406           where group_name = 'dolaccess' and field_name IN
407               ("dolupdate_replace", "mldml_dolupdate_shrink",
408                   "dolupdate_expand_incfs", "mldml_dolupdate_expand_shift",
409                   "dolupdate_expand_after_gc",
410                   "mldml_dolupdate_forward_firstlvl",
411                   "dolupdate_forward_secondlvl",
412                   "mldml_dolupdate_migrate_rowhome",
413                   "mldml_dolupdate_replace", "mldml_dolupdate_shrink",
414                   "mldml_dolupdate_expand_incfs", "mldml_dolupdate_expand_shift",
415                   "mldml_dolupdate_expand_after_gc",
416                   "mldml_dolupdate_forward_firstlvl",
417                   "mldml_dolupdate_forward_secondlvl",
418                   "mldml_dolupdate_migrate_rowhome")
419   
420           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
421           begin
422               select @rptline = "    Total Rows Updated                0.0           0.0           0       n/a"
423               print @rptline
424           end
425           else
426           begin
427   
428               print "    Fully Logged"
429               select @tmp_int = value
430               from #tempmonitors
431               where group_name = 'dolaccess' and
432                   field_name = "dolupdate_replace"
433   
434               select @rptline = "      DOL Replace" + space(12) +
435                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
436                   space(2) +
437                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
438                   space(2) +
439                   str(@tmp_int, 10) + space(5) +
440                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
441               print @rptline
442   
443               select @tmp_int = value
444               from #tempmonitors
445               where group_name = 'dolaccess' and
446                   field_name = "dolupdate_shrink"
447   
448               select @rptline = "      DOL Shrink" + space(13) +
449                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
450                   space(2) +
451                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
452                   space(2) +
453                   str(@tmp_int, 10) + space(5) +
454                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
455               print @rptline
456   
457               select @tmp_int = value
458               from #tempmonitors
459               where group_name = 'dolaccess' and
460                   field_name = "dolupdate_expand_incfs"
461   
462               select @rptline = "      DOL Cheap Expand" + space(7) +
463                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
464                   space(2) +
465                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
466                   space(2) +
467                   str(@tmp_int, 10) + space(5) +
468                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
469               print @rptline
470   
471               select @tmp_int = SUM(value)
472               from #tempmonitors
473               where group_name = 'dolaccess' and
474                   field_name in ("dolupdate_expand_shift",
475                       "dolupdate_expand_after_gc")
476   
477               select @rptline = "      DOL Expensive Expand" + space(3) +
478                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
479                   space(2) +
480                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
481                   space(2) +
482                   str(@tmp_int, 10) + space(5) +
483                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
484               print @rptline
485   
486               select @tmp_int = SUM(value)
487               from #tempmonitors
488               where group_name = 'dolaccess' and
489                   field_name in ("dolupdate_forward_firstlvl",
490                       "dolupdate_forward_secondlvl")
491   
492               select @rptline = "      DOL Expand & Forward" + space(3) +
493                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
494                   space(2) +
495                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
496                   space(2) +
497                   str(@tmp_int, 10) + space(5) +
498                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
499               print @rptline
500   
501               select @tmp_int = value
502               from #tempmonitors
503               where group_name = 'dolaccess' and
504                   field_name = "dolupdate_migrate_rowhome"
505   
506               select @rptline = "      DOL Fwd Row Returned" + space(3) +
507                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
508                   space(2) +
509                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
510                   space(2) +
511                   str(@tmp_int, 10) + space(5) +
512                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
513               print @rptline
514   
515               print "    Minimally Logged"
516               select @tmp_int = value
517               from #tempmonitors
518               where group_name = 'dolaccess' and
519                   field_name = "mldml_dolupdate_replace"
520   
521               select @rptline = "      DOL Replace" + space(12) +
522                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
523                   space(2) +
524                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
525                   space(2) +
526                   str(@tmp_int, 10) + space(5) +
527                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
528               print @rptline
529   
530               select @tmp_int = value
531               from #tempmonitors
532               where group_name = 'dolaccess' and
533                   field_name = "mldml_dolupdate_shrink"
534   
535               select @rptline = "      DOL Shrink" + space(13) +
536                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
537                   space(2) +
538                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
539                   space(2) +
540                   str(@tmp_int, 10) + space(5) +
541                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
542               print @rptline
543   
544               select @tmp_int = value
545               from #tempmonitors
546               where group_name = 'dolaccess' and
547                   field_name = "mldml_dolupdate_expand_incfs"
548   
549               select @rptline = "      DOL Cheap Expand" + space(7) +
550                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
551                   space(2) +
552                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
553                   space(2) +
554                   str(@tmp_int, 10) + space(5) +
555                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
556               print @rptline
557   
558               select @tmp_int = SUM(value)
559               from #tempmonitors
560               where group_name = 'dolaccess' and
561                   field_name in ("dmldml_olupdate_expand_shift",
562                       "mldml_dolupdate_expand_after_gc")
563   
564               select @rptline = "      DOL Expensive Expand" + space(3) +
565                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
566                   space(2) +
567                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
568                   space(2) +
569                   str(@tmp_int, 10) + space(5) +
570                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
571               print @rptline
572   
573               select @tmp_int = SUM(value)
574               from #tempmonitors
575               where group_name = 'dolaccess' and
576                   field_name in ("mldml_dolupdate_forward_firstlvl",
577                       "mldml_dolupdate_forward_secondlvl")
578   
579               select @rptline = "      DOL Expand & Forward" + space(3) +
580                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
581                   space(2) +
582                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
583                   space(2) +
584                   str(@tmp_int, 10) + space(5) +
585                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
586               print @rptline
587   
588               select @tmp_int = value
589               from #tempmonitors
590               where group_name = 'dolaccess' and
591                   field_name = "mldml_dolupdate_migrate_rowhome"
592   
593               select @rptline = "      DOL Fwd Row Returned" + space(3) +
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           end
602   
603           print @sum1line
604           select @rptline = "  Total DOL Rows Updated" + space(5) +
605               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
606               space(2) +
607               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
608               space(2) +
609               str(@tmp_total, 10) + space(5) +
610               str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign
611           print @rptline
612           print @blankline
613   
614           print "  Deletes"
615   
616           select @tmp_total = SUM(value)
617           from #tempmonitors
618           where (group_name = 'access' and
619                   field_name IN ("delete", "mldml_delete"))
620               or (group_name = 'dolaccess' and
621                   field_name IN ("doldelete_total",
622                       "mldml_doldelete_total"))
623   
624           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
625           begin
626               select @rptline = "    Total Rows Deleted                0.0           0.0           0       n/a"
627               print @rptline
628           end
629           else
630           begin
631   
632               print "    Fully Logged"
633               select @tmp_int = value
634               from #tempmonitors
635               where group_name = 'access' and
636                   field_name = "delete_deferred"
637   
638               select @rptline = "      APL Deferred" + space(11) +
639                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
640                   space(2) +
641                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
642                   space(2) +
643                   str(@tmp_int, 10) + space(5) +
644                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
645               print @rptline
646   
647               /* Calc Direct Deletes as access.delete - access.delete_deferred */
648   
649               select @tmp_int2 = value - @tmp_int
650               from #tempmonitors
651               where group_name = 'access' and
652                   field_name = "delete"
653   
654               select @rptline = "      APL Direct" + space(13) +
655                   str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
656                   space(2) +
657                   str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
658                   space(2) +
659                   str(@tmp_int2, 10) + space(5) +
660                   str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign
661               print @rptline
662   
663               select @tmp_int = value
664               from #tempmonitors
665               where group_name = 'dolaccess' and
666                   field_name = "doldelete_total"
667   
668               select @rptline = "      DOL" + space(20) +
669                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
670                   space(2) +
671                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
672                   space(2) +
673                   str(@tmp_int, 10) + space(5) +
674                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
675               print @rptline
676   
677               print "    Minimally Logged"
678   
679               select @tmp_int = value
680               from #tempmonitors
681               where group_name = 'access' and
682                   field_name = "mldml_delete"
683   
684               select @rptline = "      APL Direct" + space(13) +
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               select @tmp_int = value
694               from #tempmonitors
695               where group_name = 'dolaccess' and
696                   field_name = "mldml_doldelete_total"
697   
698               select @rptline = "      DOL" + space(20) +
699                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
700                   space(2) +
701                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
702                   space(2) +
703                   str(@tmp_int, 10) + space(5) +
704                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
705               print @rptline
706   
707               print @sum1line
708               select @rptline = "  Total Rows Deleted" + space(9) +
709                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
710                   space(2) +
711                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
712                   space(2) +
713                   str(@tmp_total, 10) + space(5) +
714                   str(100.0 * @tmp_total / @NumXactOps, 5, 1) + @psign
715               print @rptline
716           end
717   
718           print @blankline
719           print @sum3line
720           select @rptline = "  Total Rows Affected " + space(7) +
721               str(@NumXactOps / (@NumElapsedMs / 1000.0), 12, 1) +
722               space(2) +
723               str(@NumXactOps / convert(real, @NumXacts), 12, 1) +
724               space(2) +
725               str(@NumXactOps, 10)
726           print @rptline
727           print @sum3line
728       end /* else @NumXactOps != 0 */
729   
730       print @blankline
731       return 0
732   


exec sp_procxmode 'sp_sysmon_xactsum', 'AnyMode'
go

Grant Execute on sp_sysmon_xactsum to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_xactsum  
 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 112
 QAFM 3 Var Assignment from potentially many rows 126
 QAFM 3 Var Assignment from potentially many rows 140
 QAFM 3 Var Assignment from potentially many rows 154
 QAFM 3 Var Assignment from potentially many rows 169
 QAFM 3 Var Assignment from potentially many rows 183
 QAFM 3 Var Assignment from potentially many rows 197
 QAFM 3 Var Assignment from potentially many rows 246
 QAFM 3 Var Assignment from potentially many rows 260
 QAFM 3 Var Assignment from potentially many rows 274
 QAFM 3 Var Assignment from potentially many rows 288
 QAFM 3 Var Assignment from potentially many rows 302
 QAFM 3 Var Assignment from potentially many rows 316
 QAFM 3 Var Assignment from potentially many rows 331
 QAFM 3 Var Assignment from potentially many rows 345
 QAFM 3 Var Assignment from potentially many rows 359
 QAFM 3 Var Assignment from potentially many rows 374
 QAFM 3 Var Assignment from potentially many rows 429
 QAFM 3 Var Assignment from potentially many rows 443
 QAFM 3 Var Assignment from potentially many rows 457
 QAFM 3 Var Assignment from potentially many rows 501
 QAFM 3 Var Assignment from potentially many rows 516
 QAFM 3 Var Assignment from potentially many rows 530
 QAFM 3 Var Assignment from potentially many rows 544
 QAFM 3 Var Assignment from potentially many rows 588
 QAFM 3 Var Assignment from potentially many rows 633
 QAFM 3 Var Assignment from potentially many rows 649
 QAFM 3 Var Assignment from potentially many rows 663
 QAFM 3 Var Assignment from potentially many rows 679
 QAFM 3 Var Assignment from potentially many rows 693
 QTLO 3 Top-Level OR 72
 QTLO 3 Top-Level OR 96
 QTLO 3 Top-Level OR 228
 QTLO 3 Top-Level OR 618
 VNRD 3 Variable is not read @sum2line 34
 MTR1 2 Metrics: Comments Ratio Comments: 6% 8
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 53 = 52dec - 1exi + 2 8
 MTR3 2 Metrics: Query Complexity Complexity: 319 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