DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_locks  31 Aug 14Defects Dependencies

1     
2     /* This stored procedure produces a report containing a summary of
3     ** of locking characteristics including lock requests and waits.
4     */
5     
6     create procedure sp_sysmon_locks
7     
8         @NumElapsedMs int, /* for "per Elapsed second" calculations */
9         @NumXacts int, /* for per transactions calculations */
10        @Reco char(1),
11        @NumEngines tinyint = 1 /* number of engines online */
12    as
13    
14        /* --------- declare local variables --------- */
15    
16        declare @TotalLocks int /* Count Total Number of Locks */
17        declare @tmp_int int /* temp var for integer storage */
18        declare @tmp_int2 int /* temp var for integer storage */
19        declare @tmp_access int /* temp var for integer storage */
20        declare @tmp_xls int /* temp var for integer storage */
21        declare @tmp_total int /* temp var for summing 'total #s' data */
22        declare @sum1line char(80) /* string to delimit total lines without 
23        ** percent calc on printout */
24        declare @sum2line char(67) /* string to delimit total lines with 
25        ** percent calc on printout */
26        declare @blankline char(1) /* to print blank line */
27        declare @psign char(3) /* hold a percent sign (%) for print out */
28        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
29        declare @rptline char(80) /* formatted stats line for print statement */
30        declare @section char(80) /* string to delimit sections on printout */
31    
32        /* ------------- Variables for Suggested Recomendations ------------*/
33        declare @recotxt char(80)
34        declare @recoline char(80)
35        declare @maxengfreelocks int /* Value for max engine freelocks */
36        declare @max_online_engine int /* Max online engine count */
37        declare @freelock_tr_blksize char(5) /* Freelock transfer block size */
38        declare @deadlocks int /* Deadlock count */
39        declare @deadlock_search int /* Deadlock Search count */
40        declare @tempmaxengfreelocks char(5) /* Temp maxengfreelocks for convert */
41        declare @curfreelocks int
42        declare @tot_movefreelock_toengine int
43        declare @tot_movefreelock_toserver int
44        declare @newval char(5)
45        declare @tmp_num_lock_op int
46        declare @reco_hdr_prn bit
47        declare @reco_lastpg_wait_percent real
48        declare @reco_avg_chain_length_pagerow real
49        declare @spinlock_contention float
50        declare @reco_tabspin_contention float
51        declare @reco_addrspin_contention float
52        declare @reco_fgspin_contention float
53        declare @totalrequested int
54        declare @totalretained int
55        declare @totaldiskreads int
56        declare @totaldeadlocks int
57        declare @totalwaits int
58        declare @totaltransfers int
59        declare @totallockmgrcalls int
60        declare @NumElapsedSec real
61        declare @totalreqs int
62        declare @totallocalmaster int
63        declare @totalgrants int
64        declare @totalbastreqs int
65    
66    
67        /* --------- Setup Environment --------- */
68        set nocount on /* disable row counts being sent to client */
69    
70        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
71        select @sum2line = "  -------------------------  ------------  ------------  ----------"
72        select @blankline = " "
73        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
74        select @na_str = "n/a"
75        select @recotxt = "  Tuning Recommendations for Lock Management"
76        select @recoline = "  ------------------------------------------"
77        select @section = "==============================================================================="
78    
79        print @section
80        print @blankline
81    
82        print "Lock Management"
83        print "---------------"
84        print @blankline
85        print "  Lock Summary                    per sec      per xact       count  %% of total"
86        print @sum1line
87    
88        select @NumElapsedSec = @NumElapsedMs / 1000.0
89    
90        select @TotalLocks = SUM(value)
91        from #tempmonitors
92        where group_name = "lock" and
93            (field_name like "granted%" or field_name like "waited%")
94    
95        select @totalretained = SUM(value)
96        from #tempmonitors
97        where group_name = "lock" and
98            field_name like "clm_logical_retained"
99    
100   
101       select @rptline = "  Total Lock Requests" + space(8) +
102           str(@TotalLocks / (@NumElapsedMs / 1000.0), 12, 1) +
103           space(2) +
104           str(@TotalLocks / convert(real, @NumXacts), 12, 1) +
105           space(2) +
106           str(@TotalLocks, 10) + space(7) +
107           @na_str
108       print @rptline
109   
110       if @TotalLocks != 0 /* Avoid Divide by Zero Errors after printout */
111       begin
112           select @tmp_int = SUM(value)
113           from #tempmonitors
114           where group_name = "lock" and
115               field_name like "%waited%"
116   
117           select @rptline = "  Avg Lock Contention" + space(8) +
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 / @TotalLocks, 5, 1) +
124               @psign
125           print @rptline
126           select @rptline = "  Cluster Locks Retained" + space(5) +
127               str(@totalretained / (@NumElapsedMs / 1000.0), 12, 1) +
128               space(2) +
129               str(@totalretained / convert(real, @NumXacts), 12, 1) +
130               space(2) +
131               str(@totalretained, 10) + space(5) +
132               str(100.0 * @totalretained / @TotalLocks, 5, 1) +
133               @psign
134           print @rptline
135   
136           select @tmp_int = SUM(value)
137           from #tempmonitors
138           where group_name = "lock" and
139               field_name = "deadlocks"
140   
141           select @rptline = "  Deadlock Percentage" + space(8) +
142               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
143               space(2) +
144               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
145               space(2) +
146               str(@tmp_int, 10) + space(5) +
147               str(100.0 * @tmp_int / @TotalLocks, 5, 1) +
148               @psign
149           print @rptline
150   
151           print @blankline
152   
153           /* 
154           ** Get spinlock related counters for hashtable spinlock contentions
155           */
156           select P.field_name as name,
157               P.value as grabs,
158               W.value as waits,
159               S.value as spins
160           into #foo
161           from #tempmonitors P, #tempmonitors W, #tempmonitors S
162           where
163               P.group_name = "spinlock_p"
164               and W.group_name = "spinlock_w"
165               and S.group_name = "spinlock_s"
166               and P.field_id = W.field_id
167               and P.field_id = S.field_id
168               and P.field_name in ('fglockspins', 'tablockspins', 'addrlockspins')
169   
170           print "  Lock Detail                     per sec      per xact       count  %% of total"
171           print @sum1line
172           print @blankline
173   
174           print "  Table Lock Hashtable"
175           select @tmp_int = value
176           from #tempmonitors
177           where group_name = "lock" and
178               field_name = "hashtab_lookups_table_lock"
179   
180           select @rptline = "    Lookups" + space(18) +
181               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
182               space(2) +
183               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
184               space(2) +
185               str(@tmp_int, 10) + space(7) +
186               @na_str
187           print @rptline
188   
189           /* Avoid divide by 0 error */
190           if (@tmp_int > 0)
191           begin
192               select @tmp_int2 = value
193               from #tempmonitors
194               where group_name = "lock" and
195                   field_name = "hashtab_overflow_table_lock"
196   
197               select @rptline = "    Avg Chain Length" + space(18) +
198                   @na_str + space(11) +
199                   @na_str + space(2) +
200                   str(convert(real, @tmp_int2) / @tmp_int, 10, 5) +
201                   space(7) + @na_str
202               print @rptline
203           end
204   
205           select @spinlock_contention = isnull(100.0 * (sum(waits) / sum(grabs)), 0)
206           from #foo
207           where name = 'tablockspins'
208               and grabs > 0
209   
210           select @rptline = "    Spinlock Contention" + space(15) +
211               @na_str + space(11) +
212               @na_str + space(9) +
213               @na_str + space(5) +
214               str(@spinlock_contention, 5, 1)
215               + @psign
216               , @reco_tabspin_contention = @spinlock_contention
217           print @rptline
218           print @blankline
219   
220           print "  Exclusive Table"
221   
222           select @tmp_total = SUM(value)
223           from #tempmonitors
224           where group_name = "lock" and
225               (field_name like "granted%_EX_TAB" or
226                   field_name like "waited%_EX_TAB")
227   
228           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
229           begin
230               select @rptline = "    Total EX-Table Requests           0.0           0.0           0       n/a"
231               print @rptline
232           end
233           else
234           begin
235               select @tmp_int = sum(value)
236               from #tempmonitors
237               where group_name = "lock" and
238                   field_name like "granted%_EX_TAB"
239   
240               select @rptline = "    Granted" + space(18) +
241                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
242                   space(2) +
243                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
244                   space(2) +
245                   str(@tmp_int, 10) + space(5) +
246                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
247                   @psign
248               print @rptline
249   
250               select @tmp_int = sum(value)
251               from #tempmonitors
252               where group_name = "lock" and
253                   field_name like "waited%_EX_TAB"
254   
255               select @rptline = "    Waited" + space(19) +
256                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
257                   space(2) +
258                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
259                   space(2) +
260                   str(@tmp_int, 10) + space(5) +
261                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
262                   @psign
263               print @rptline
264               print @sum1line
265               select @rptline = "  Total EX-Table Requests" + space(4) +
266                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
267                   space(2) +
268                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
269                   space(2) +
270                   str(@tmp_total, 10) + space(5) +
271                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
272                   @psign
273               print @rptline
274           end
275           print @blankline
276   
277           print "  Shared Table"
278   
279           select @tmp_total = SUM(value)
280           from #tempmonitors
281           where group_name = "lock" and
282               (field_name like "granted%_SH_TAB" or field_name like "waited%_SH_TAB")
283   
284           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
285           begin
286               select @rptline = "    Total SH-Table Requests           0.0           0.0           0       n/a"
287               print @rptline
288           end
289           else
290           begin
291               select @tmp_int = sum(value)
292               from #tempmonitors
293               where group_name = "lock" and
294                   field_name like "granted%_SH_TAB"
295   
296               select @rptline = "    Granted" + space(18) +
297                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
298                   space(2) +
299                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
300                   space(2) +
301                   str(@tmp_int, 10) + space(5) +
302                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
303                   @psign
304               print @rptline
305   
306               select @tmp_int = sum(value)
307               from #tempmonitors
308               where group_name = "lock" and
309                   field_name like "waited%_SH_TAB"
310   
311               select @rptline = "    Waited" + space(19) +
312                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
313                   space(2) +
314                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
315                   space(2) +
316                   str(@tmp_int, 10) + space(5) +
317                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
318                   @psign
319               print @rptline
320   
321               print @sum1line
322               select @rptline = "  Total SH-Table Requests" + space(4) +
323                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
324                   space(2) +
325                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
326                   space(2) +
327                   str(@tmp_total, 10) + space(5) +
328                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
329                   @psign
330               print @rptline
331           end
332           print @blankline
333   
334           print "  Exclusive Intent"
335   
336           select @tmp_total = SUM(value)
337           from #tempmonitors
338           where group_name = "lock" and
339               (field_name like "granted%_EX_INT" or
340                   field_name like "waited%_EX_INT")
341   
342           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
343           begin
344               select @rptline = "    Total EX-Intent Requests          0.0           0.0           0       n/a"
345               print @rptline
346           end
347           else
348           begin
349               select @tmp_int = sum(value)
350               from #tempmonitors
351               where group_name = "lock" and
352                   field_name like "granted%_EX_INT"
353   
354               select @rptline = "    Granted" + space(18) +
355                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
356                   space(2) +
357                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
358                   space(2) +
359                   str(@tmp_int, 10) + space(5) +
360                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
361                   @psign
362               print @rptline
363   
364               select @tmp_int = sum(value)
365               from #tempmonitors
366               where group_name = "lock" and
367                   field_name like "waited%_EX_INT"
368   
369               select @rptline = "    Waited" + space(19) +
370                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
371                   space(2) +
372                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
373                   space(2) +
374                   str(@tmp_int, 10) + space(5) +
375                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
376                   @psign
377               print @rptline
378   
379               print @sum1line
380               select @rptline = "  Total EX-Intent Requests" + space(3) +
381                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
382                   space(2) +
383                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
384                   space(2) +
385                   str(@tmp_total, 10) + space(5) +
386                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
387                   @psign
388               print @rptline
389           end
390           print @blankline
391   
392           print "  Shared Intent"
393   
394           select @tmp_total = SUM(value)
395           from #tempmonitors
396           where group_name = "lock" and
397               (field_name like "granted%_SH_INT" or
398                   field_name like "waited%_SH_INT")
399   
400           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
401           begin
402               select @rptline = "    Total SH-Intent Requests          0.0           0.0           0       n/a"
403               print @rptline
404           end
405           else
406           begin
407               select @tmp_int = sum(value)
408               from #tempmonitors
409               where group_name = "lock" and
410                   field_name like "granted%_SH_INT"
411   
412               select @rptline = "    Granted" + space(18) +
413                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
414                   space(2) +
415                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
416                   space(2) +
417                   str(@tmp_int, 10) + space(5) +
418                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
419                   @psign
420               print @rptline
421   
422               select @tmp_int = sum(value)
423               from #tempmonitors
424               where group_name = "lock" and
425                   field_name like "waited%_SH_INT"
426   
427               select @rptline = "    Waited" + space(19) +
428                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
429                   space(2) +
430                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
431                   space(2) +
432                   str(@tmp_int, 10) + space(5) +
433                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
434                   @psign
435               print @rptline
436               print @sum1line
437   
438               select @rptline = "  Total SH-Intent Requests" + space(3) +
439                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
440                   space(2) +
441                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
442                   space(2) +
443                   str(@tmp_total, 10) + space(5) +
444                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
445                   @psign
446               print @rptline
447           end
448           print @blankline
449   
450           print "  Page & Row Lock HashTable"
451           select @tmp_int = value
452           from #tempmonitors
453           where group_name = "lock" and
454               field_name = "hashtab_lookups_pagerow_lock"
455   
456           select @rptline = "    Lookups" + space(18) +
457               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
458               space(2) +
459               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
460               space(2) +
461               str(@tmp_int, 10) + space(7) + @na_str
462           print @rptline
463   
464           /* Avoid divide by 0 error */
465           if (@tmp_int > 0)
466           begin
467               select @tmp_int2 = value
468               from #tempmonitors
469               where group_name = "lock" and
470                   field_name = "hashtab_overflow_pagerow_lock"
471   
472               select @rptline = "    Avg Chain Length" + space(18) +
473                   @na_str + space(11) +
474                   @na_str + space(2) +
475                   str(convert(real, @tmp_int2) / @tmp_int, 10, 5) +
476                   space(7) + @na_str
477                   , @reco_avg_chain_length_pagerow = convert(real, @tmp_int2) / @tmp_int
478               print @rptline
479           end
480           else
481           begin
482               select @reco_avg_chain_length_pagerow = 0
483           end
484   
485           select @spinlock_contention = isnull(100.0 * (sum(waits) / sum(grabs)), 0)
486           from #foo
487           where name = 'fglockspins'
488               and grabs > 0
489   
490           select @rptline = "    Spinlock Contention" + space(15) +
491               @na_str + space(11) +
492               @na_str + space(9) +
493               @na_str + space(5) +
494               str(@spinlock_contention, 5, 1)
495               + @psign
496               , @reco_fgspin_contention = @spinlock_contention
497           print @rptline
498           print @blankline
499   
500           print "  Exclusive Page"
501   
502           select @tmp_total = SUM(value)
503           from #tempmonitors
504           where group_name = "lock" and
505               (field_name like "granted%_EX_PAGE" or
506                   field_name like "waited%_EX_PAGE")
507   
508           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
509           begin
510               select @rptline = "    Total EX-Page Requests            0.0           0.0           0       n/a"
511               print @rptline
512           end
513           else
514           begin
515   
516               select @tmp_int = sum(value)
517               from #tempmonitors
518               where group_name = "lock" and
519                   field_name like "granted%_EX_PAGE"
520   
521               select @rptline = "    Granted" + space(18) +
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) +
528                   @psign
529               print @rptline
530   
531               select @tmp_int = sum(value)
532               from #tempmonitors
533               where group_name = "lock" and
534                   field_name like "waited%_EX_PAGE"
535   
536               select @rptline = "    Waited" + space(19) +
537                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
538                   space(2) +
539                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
540                   space(2) +
541                   str(@tmp_int, 10) + space(5) +
542                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
543               print @rptline
544   
545               print @sum1line
546               select @rptline = "  Total EX-Page Requests" + space(5) +
547                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
548                   space(2) +
549                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
550                   space(2) +
551                   str(@tmp_total, 10) + space(5) +
552                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
553               print @rptline
554           end
555           print @blankline
556   
557           print "  Update Page"
558   
559           select @tmp_total = SUM(value)
560           from #tempmonitors
561           where group_name = "lock" and
562               (field_name like "granted%_UP_PAGE" or
563                   field_name like "waited%_UP_PAGE")
564   
565           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
566           begin
567               select @rptline = "    Total UP-Page Requests            0.0           0.0           0       n/a"
568               print @rptline
569           end
570           else
571           begin
572   
573               select @tmp_int = sum(value)
574               from #tempmonitors
575               where group_name = "lock" and
576                   field_name like "granted%_UP_PAGE"
577   
578               select @rptline = "    Granted" + space(18) +
579                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
580                   space(2) +
581                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
582                   space(2) +
583                   str(@tmp_int, 10) + space(5) +
584                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
585               print @rptline
586   
587               select @tmp_int = sum(value)
588               from #tempmonitors
589               where group_name = "lock" and
590                   field_name like "waited%_UP_PAGE"
591   
592               select @rptline = "    Waited" + space(19) +
593                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
594                   space(2) +
595                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
596                   space(2) +
597                   str(@tmp_int, 10) + space(5) +
598                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
599               print @rptline
600   
601               print @sum1line
602               select @rptline = "  Total UP-Page Requests" + space(5) +
603                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
604                   space(2) +
605                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
606                   space(2) +
607                   str(@tmp_total, 10) + space(5) +
608                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
609               print @rptline
610           end
611           print @blankline
612   
613           print "  Shared Page"
614   
615           select @tmp_total = SUM(value)
616           from #tempmonitors
617           where group_name = "lock" and
618               (field_name like "granted%_SH_PAGE" or
619                   field_name like "waited%_SH_PAGE")
620   
621           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
622           begin
623               select @rptline = "    Total SH-Page Requests            0.0           0.0           0       n/a"
624               print @rptline
625           end
626           else
627           begin
628   
629               select @tmp_int = sum(value)
630               from #tempmonitors
631               where group_name = "lock" and
632                   field_name like "granted%_SH_PAGE"
633   
634               select @rptline = "    Granted" + space(18) +
635                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
636                   space(2) +
637                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
638                   space(2) +
639                   str(@tmp_int, 10) + space(5) +
640                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
641               print @rptline
642   
643               select @tmp_int = sum(value)
644               from #tempmonitors
645               where group_name = "lock" and
646                   field_name like "waited%_SH_PAGE"
647   
648               select @rptline = "    Waited" + space(19) +
649                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
650                   space(2) +
651                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
652                   space(2) +
653                   str(@tmp_int, 10) + space(5) +
654                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
655               print @rptline
656   
657               print @sum1line
658               select @rptline = "  Total SH-Page Requests" + space(5) +
659                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
660                   space(2) +
661                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
662                   space(2) +
663                   str(@tmp_total, 10) + space(5) +
664   
665                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
666               print @rptline
667               print @blankline
668           end
669           print @blankline
670   
671           print "  Exclusive Row"
672   
673           select @tmp_total = SUM(value)
674           from #tempmonitors
675           where group_name = "lock" and
676               (field_name like "granted%_EX_ROW" or
677                   field_name like "waited%_EX_ROW")
678   
679           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
680           begin
681               select @rptline = "    Total EX-Row Requests             0.0           0.0           0       n/a"
682               print @rptline
683           end
684           else
685           begin
686   
687               select @tmp_int = sum(value)
688               from #tempmonitors
689               where group_name = "lock" and
690                   field_name like "granted%_EX_ROW"
691   
692               select @rptline = "    Granted" + space(18) +
693                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
694                   space(2) +
695                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
696                   space(2) +
697                   str(@tmp_int, 10) + space(5) +
698                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
699                   @psign
700               print @rptline
701   
702               select @tmp_int = sum(value)
703               from #tempmonitors
704               where group_name = "lock" and
705                   field_name like "waited%_EX_ROW"
706   
707               select @rptline = "    Waited" + space(19) +
708                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
709                   space(2) +
710                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
711                   space(2) +
712                   str(@tmp_int, 10) + space(5) +
713                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
714               print @rptline
715   
716               print @sum1line
717               select @rptline = "  Total EX-Row Requests" + space(6) +
718                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
719                   space(2) +
720                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
721                   space(2) +
722                   str(@tmp_total, 10) + space(5) +
723                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
724               print @rptline
725           end
726           print @blankline
727   
728           print "  Update Row"
729   
730           select @tmp_total = SUM(value)
731           from #tempmonitors
732           where group_name = "lock" and
733               (field_name like "granted%_UP_ROW" or
734                   field_name like "waited%_UP_ROW")
735   
736           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
737           begin
738               select @rptline = "    Total UP-Row Requests             0.0           0.0           0       n/a"
739               print @rptline
740           end
741           else
742           begin
743   
744               select @tmp_int = sum(value)
745               from #tempmonitors
746               where group_name = "lock" and
747                   field_name like "granted%_UP_ROW"
748   
749               select @rptline = "    Granted" + space(18) +
750                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
751                   space(2) +
752                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
753                   space(2) +
754                   str(@tmp_int, 10) + space(5) +
755                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
756               print @rptline
757   
758               select @tmp_int = sum(value)
759               from #tempmonitors
760               where group_name = "lock" and
761                   field_name like "waited%_UP_ROW"
762   
763               select @rptline = "    Waited" + space(19) +
764                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
765                   space(2) +
766                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
767                   space(2) +
768                   str(@tmp_int, 10) + space(5) +
769                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
770               print @rptline
771   
772               print @sum1line
773               select @rptline = "  Total UP-Row Requests" + space(6) +
774                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
775                   space(2) +
776                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
777                   space(2) +
778                   str(@tmp_total, 10) + space(5) +
779                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
780               print @rptline
781           end
782           print @blankline
783   
784           print "  Shared Row"
785   
786           select @tmp_total = SUM(value)
787           from #tempmonitors
788           where group_name = "lock" and
789               (field_name like "granted%_SH_ROW" or
790                   field_name like "waited%_SH_ROW")
791   
792           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
793           begin
794               select @rptline = "    Total SH-Row Requests             0.0           0.0           0       n/a"
795               print @rptline
796           end
797           else
798           begin
799   
800               select @tmp_int = sum(value)
801               from #tempmonitors
802               where group_name = "lock" and
803                   field_name like "granted%_SH_ROW"
804   
805               select @rptline = "    Granted" + space(18) +
806                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
807                   space(2) +
808                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
809                   space(2) +
810                   str(@tmp_int, 10) + space(5) +
811                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
812               print @rptline
813   
814               select @tmp_int = sum(value)
815               from #tempmonitors
816               where group_name = "lock" and
817                   field_name like "waited%_SH_ROW"
818   
819               select @rptline = "    Waited" + space(19) +
820                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
821                   space(2) +
822                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
823                   space(2) +
824                   str(@tmp_int, 10) + space(5) +
825                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
826               print @rptline
827   
828               print @sum1line
829               select @rptline = "  Total SH-Row Requests" + space(6) +
830                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
831                   space(2) +
832                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
833                   space(2) +
834                   str(@tmp_total, 10) + space(5) +
835                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
836               print @rptline
837               print @blankline
838           end
839           print @blankline
840   
841           print "  Next-Key"
842   
843           select @tmp_total = SUM(value)
844           from #tempmonitors
845           where group_name = "lock" and
846               (field_name like "granted%_SH_NKL" or
847                   field_name like "waited%_SH_NKL")
848   
849           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
850           begin
851               select @rptline = "    Total Next-Key Requests           0.0           0.0           0       n/a"
852               print @rptline
853           end
854           else
855           begin
856   
857               select @tmp_int = sum(value)
858               from #tempmonitors
859               where group_name = "lock" and
860                   field_name like "granted%_SH_NKL"
861   
862               select @rptline = "    Granted" + space(18) +
863                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
864                   space(2) +
865                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
866                   space(2) +
867                   str(@tmp_int, 10) + space(5) +
868                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
869               print @rptline
870   
871               select @tmp_int = sum(value)
872               from #tempmonitors
873               where group_name = "lock" and
874                   field_name like "waited%_SH_NKL"
875   
876               select @rptline = "    Waited" + space(19) +
877                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
878                   space(2) +
879                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
880                   space(2) +
881                   str(@tmp_int, 10) + space(5) +
882                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
883               print @rptline
884   
885               print @sum1line
886               select @rptline = "  Total Next-Key Requests" + space(4) +
887                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
888                   space(2) +
889                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
890                   space(2) +
891                   str(@tmp_total, 10) + space(5) +
892                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
893               print @rptline
894               print @blankline
895           end
896           print @blankline
897   
898           print "  Address Lock Hashtable"
899           select @tmp_int = value
900           from #tempmonitors
901           where group_name = "lock" and
902               field_name = "hashtab_lookups_address_lock"
903   
904           select @rptline = "    Lookups" + space(18) +
905               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
906               space(2) +
907               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
908               space(2) +
909               str(@tmp_int, 10) + space(7) +
910               @na_str
911           print @rptline
912   
913           /* Avoid divide by 0 error */
914           if (@tmp_int > 0)
915           begin
916               select @tmp_int2 = value
917               from #tempmonitors
918               where group_name = "lock" and
919                   field_name = "hashtab_overflow_address_lock"
920   
921               select @rptline = "    Avg Chain Length" + space(18) +
922                   @na_str + space(11) +
923                   @na_str + space(2) +
924                   str(convert(real, @tmp_int2) / @tmp_int, 10, 5) +
925                   space(7) + @na_str
926               print @rptline
927           end
928   
929           select @spinlock_contention = isnull(100.0 * (sum(waits) / sum(grabs)), 0)
930           from #foo
931           where name = 'addrlockspins'
932               and grabs > 0
933   
934           select @rptline = "    Spinlock Contention" + space(15) +
935               @na_str + space(11) +
936               @na_str + space(9) +
937               @na_str + space(5) +
938               str(@spinlock_contention, 5, 1)
939               + @psign
940               , @reco_addrspin_contention = @spinlock_contention
941           print @rptline
942           print @blankline
943   
944           print "  Exclusive Address"
945   
946           select @tmp_total = SUM(value)
947           from #tempmonitors
948           where group_name = "lock" and
949               (field_name like "granted_%EX_ADDR" or
950                   field_name like "waited_%EX_ADDR")
951   
952           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
953           begin
954               select @rptline = "    Total EX-Address Requests         0.0           0.0           0       n/a"
955               print @rptline
956           end
957           else
958           begin
959   
960               select @tmp_int = sum(value)
961               from #tempmonitors
962               where group_name = "lock" and
963                   field_name like "granted_%EX_ADDR"
964   
965               select @rptline = "    Granted" + space(18) +
966                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
967                   space(2) +
968                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
969                   space(2) +
970                   str(@tmp_int, 10) + space(5) +
971                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
972               print @rptline
973   
974               select @tmp_int = sum(value)
975               from #tempmonitors
976               where group_name = "lock" and
977                   field_name like "waited_%EX_ADDR"
978   
979               select @rptline = "    Waited" + space(19) +
980                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
981                   space(2) +
982                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
983                   space(2) +
984                   str(@tmp_int, 10) + space(5) +
985                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
986               print @rptline
987   
988               print @sum1line
989               select @rptline = "  Total EX-Address Requests  " +
990                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
991                   space(2) +
992                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
993                   space(2) +
994                   str(@tmp_total, 10) + space(5) +
995                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
996               print @rptline
997   
998           end
999           print @blankline
1000  
1001          print "  Shared Address"
1002  
1003          select @tmp_total = SUM(value)
1004          from #tempmonitors
1005          where group_name = "lock" and
1006              (field_name like "granted_%SH_ADDR" or
1007                  field_name like "waited_%SH_ADDR")
1008  
1009          if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1010          begin
1011              select @rptline = "    Total SH-Address Requests         0.0           0.0           0       n/a"
1012              print @rptline
1013          end
1014          else
1015          begin
1016  
1017              select @tmp_int = sum(value)
1018              from #tempmonitors
1019              where group_name = "lock" and
1020                  field_name like "granted_%SH_ADDR"
1021  
1022              select @rptline = "    Granted" + space(18) +
1023                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1024                  space(2) +
1025                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1026                  space(2) +
1027                  str(@tmp_int, 10) + space(5) +
1028                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1029              print @rptline
1030  
1031              select @tmp_int = sum(value)
1032              from #tempmonitors
1033              where group_name = "lock" and
1034                  field_name like "waited_%SH_ADDR"
1035  
1036              select @rptline = "    Waited" + space(19) +
1037                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1038                  space(2) +
1039                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1040                  space(2) +
1041                  str(@tmp_int, 10) + space(5) +
1042                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1043              print @rptline
1044  
1045              print @sum1line
1046              select @rptline = "  Total SH-Address Requests  " +
1047                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1048                  space(2) +
1049                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1050                  space(2) +
1051                  str(@tmp_total, 10) + space(5) +
1052                  str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
1053              print @rptline
1054  
1055              print @blankline
1056          end
1057          print @blankline
1058  
1059          print "  Last Page Locks on Heaps"
1060  
1061          select @tmp_total = SUM(value)
1062          from #tempmonitors
1063          where group_name = "access" and
1064              field_name = "locklastpage"
1065  
1066          if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1067          begin
1068              select @rptline = "    Total Last Pg Locks               0.0           0.0           0       n/a"
1069              print @rptline
1070          end
1071          else
1072          begin
1073  
1074              select @tmp_int2 = value
1075              from #tempmonitors
1076              where group_name = "access" and
1077                  field_name = "lklastpg_wait"
1078  
1079              select @tmp_int = @tmp_total - @tmp_int2
1080  
1081              select @rptline = "    Granted" + space(18) +
1082                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1083                  space(2) +
1084                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1085                  space(2) +
1086                  str(@tmp_int, 10) + space(5) +
1087                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1088              print @rptline
1089  
1090              select @reco_lastpg_wait_percent = convert(int,
1091                  100.0 * ((1.0 * @tmp_int2) / @tmp_total))
1092  
1093              select @rptline = "    Waited" + space(19) +
1094                  str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
1095                  space(2) +
1096                  str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
1097                  space(2) +
1098                  str(@tmp_int2, 10) + space(5) +
1099                  str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign
1100              print @rptline
1101              print @sum1line
1102              select @rptline = "  Total Last Pg Locks" + space(8) +
1103                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1104                  space(2) +
1105                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1106                  space(2) +
1107                  str(@tmp_total, 10) + space(5) +
1108                  str(100.0 * @tmp_total / @tmp_total, 5, 1) + @psign
1109              print @rptline
1110              print @blankline
1111          end
1112          print @blankline
1113      end
1114  
1115  
1116      print "  Deadlocks by Lock Type          per sec      per xact       count  %% of total"
1117      print @sum1line
1118  
1119      select @tmp_total = value
1120      from #tempmonitors
1121      where group_name = "lock" and
1122          field_name = "deadlocks"
1123  
1124      select @totaldeadlocks = sum(value)
1125      from #tempmonitors
1126      where group_name like "buffer_%" and
1127          field_name like "physical_lock_deadlock"
1128  
1129      select @tmp_access = SUM(value)
1130      from #tempmonitors
1131      where group_name = "access" and
1132          field_name in ("getrootpg_deadlock",
1133              "lkindex_TSCK_deadlock",
1134              "lkindex_deadlock",
1135              "lkindDS_P_deadlock",
1136              "lkindDS_C_deadlock",
1137              "split_index_deadlock",
1138              "log_lock_deadlock")
1139  
1140      select @tmp_xls = value
1141      from #tempmonitors
1142      where group_name = "xls" and
1143          field_name = "log_lock_deadlock"
1144  
1145      select @tmp_total = @tmp_total + @totaldeadlocks + @tmp_access + @tmp_xls
1146  
1147      if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1148      begin
1149          select @rptline = "  Total Deadlocks                     0.0           0.0           0       n/a"
1150          print @rptline
1151      end
1152      else
1153      begin
1154  
1155          select @tmp_int = value
1156          from #tempmonitors
1157          where group_name = "lock" and
1158              field_name = "deadlock_EX_TAB"
1159  
1160          select @rptline = "    Exclusive Table" + space(10) +
1161              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1162              space(2) +
1163              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1164              space(2) +
1165              str(@tmp_int, 10) + space(5) +
1166              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1167          print @rptline
1168  
1169          select @tmp_int = value
1170          from #tempmonitors
1171          where group_name = "lock" and
1172              field_name = "deadlock_SH_TAB"
1173  
1174          select @rptline = "    Shared Table" + space(13) +
1175              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1176              space(2) +
1177              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1178              space(2) +
1179              str(@tmp_int, 10) + space(5) +
1180              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1181          print @rptline
1182  
1183          select @tmp_int = value
1184          from #tempmonitors
1185          where group_name = "lock" and
1186              field_name = "deadlock_EX_INT"
1187  
1188          select @rptline = "    Exclusive Intent" + space(9) +
1189              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1190              space(2) +
1191              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1192              space(2) +
1193              str(@tmp_int, 10) + space(5) +
1194              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1195          print @rptline
1196  
1197          select @tmp_int = value
1198          from #tempmonitors
1199          where group_name = "lock" and
1200              field_name = "deadlock_SH_INT"
1201  
1202          select @rptline = "    Shared Intent" + space(12) +
1203              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1204              space(2) +
1205              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1206              space(2) +
1207              str(@tmp_int, 10) + space(5) +
1208              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1209          print @rptline
1210  
1211          select @tmp_int = value
1212          from #tempmonitors
1213          where group_name = "lock" and
1214              field_name = "deadlock_EX_PAGE"
1215  
1216          select @rptline = "    Exclusive Page" + space(11) +
1217              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1218              space(2) +
1219              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1220              space(2) +
1221              str(@tmp_int, 10) + space(5) +
1222              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1223          print @rptline
1224  
1225          select @tmp_int = value
1226          from #tempmonitors
1227          where group_name = "lock" and
1228              field_name = "deadlock_UP_PAGE"
1229  
1230          select @rptline = "    Update Page" + space(14) +
1231              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1232              space(2) +
1233              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1234              space(2) +
1235              str(@tmp_int, 10) + space(5) +
1236              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1237          print @rptline
1238  
1239          select @tmp_int = value
1240          from #tempmonitors
1241          where group_name = "lock" and
1242              field_name = "deadlock_SH_PAGE"
1243  
1244          select @rptline = "    Shared Page" + space(14) +
1245              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1246              space(2) +
1247              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1248              space(2) +
1249              str(@tmp_int, 10) + space(5) +
1250              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1251          print @rptline
1252  
1253          select @tmp_int = value
1254          from #tempmonitors
1255          where group_name = "lock" and
1256              field_name = "deadlock_EX_ROW"
1257  
1258          select @rptline = "    Exclusive Row" + space(12) +
1259              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1260              space(2) +
1261              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1262              space(2) +
1263              str(@tmp_int, 10) + space(5) +
1264              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1265          print @rptline
1266  
1267          select @tmp_int = value
1268          from #tempmonitors
1269          where group_name = "lock" and
1270              field_name = "deadlock_UP_ROW"
1271  
1272          select @rptline = "    Update Row" + space(15) +
1273              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1274              space(2) +
1275              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1276              space(2) +
1277              str(@tmp_int, 10) + space(5) +
1278              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1279          print @rptline
1280  
1281          select @tmp_int = value
1282          from #tempmonitors
1283          where group_name = "lock" and
1284              field_name = "deadlock_SH_ROW"
1285  
1286          select @rptline = "    Shared Row" + space(15) +
1287              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1288              space(2) +
1289              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1290              space(2) +
1291              str(@tmp_int, 10) + space(5) +
1292              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1293          print @rptline
1294  
1295          select @tmp_int = value
1296          from #tempmonitors
1297          where group_name = "lock" and
1298              field_name = "deadlock_SH_NKL"
1299  
1300          select @rptline = "    Shared Next-Key" + space(10) +
1301              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1302              space(2) +
1303              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1304              space(2) +
1305              str(@tmp_int, 10) + space(5) +
1306              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1307          print @rptline
1308  
1309  
1310          select @tmp_int = value
1311          from #tempmonitors
1312          where group_name = "lock" and
1313              field_name = "deadlock_EX_ADDR"
1314  
1315          select @rptline = "    Exclusive Address" + space(8) +
1316              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1317              space(2) +
1318              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1319              space(2) +
1320              str(@tmp_int, 10) + space(5) +
1321              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1322          print @rptline
1323  
1324          select @tmp_int = value
1325          from #tempmonitors
1326          where group_name = "lock" and
1327              field_name = "deadlock_SH_ADDR"
1328  
1329          select @rptline = "    Shared Address" + space(11) +
1330              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1331              space(2) +
1332              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1333              space(2) +
1334              str(@tmp_int, 10) + space(5) +
1335              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1336          print @rptline
1337  
1338          /* Get the deadlocks for physical lock acquisition */
1339          select @rptline = "    Physical Locks      " + space(5) +
1340              str(@totaldeadlocks / @NumElapsedSec, 12, 1) +
1341              space(2) +
1342              str(@totaldeadlocks / convert(real, @NumXacts), 12, 1) +
1343              space(2) +
1344              str(@totaldeadlocks, 10) + space(5) +
1345              str(100.0 * @totaldeadlocks / @tmp_total, 5, 1) + @psign
1346          print @rptline
1347  
1348          /* Get the deadlocks for "access" group */
1349          select @tmp_access = SUM(value)
1350          from #tempmonitors
1351          where group_name = "access" and
1352              field_name in ("getrootpg_deadlock",
1353                  "lkindex_TSCK_deadlock",
1354                  "lkindex_deadlock",
1355                  "lkindDS_P_deadlock",
1356                  "lkindDS_C_deadlock",
1357                  "split_index_deadlock",
1358                  "log_lock_deadlock")
1359  
1360          /* Get the deadlocks for "xls" group */
1361          select @tmp_xls = value
1362          from #tempmonitors
1363          where group_name = "xls" and
1364              field_name = "log_lock_deadlock"
1365  
1366          /* Get the sum of the deadlocks(other than belonging to group locks) */
1367          select @tmp_int = @tmp_access + @tmp_xls
1368  
1369          select @rptline = "    Others" + space(19) +
1370              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1371              space(2) +
1372              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1373              space(2) +
1374              str(@tmp_int, 10) + space(5) +
1375              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1376          print @rptline
1377  
1378  
1379          print @sum2line
1380          select @rptline = "  Total Deadlocks" + space(12) +
1381              str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1382              space(2) +
1383              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1384              space(2) +
1385              str(@tmp_total, 10)
1386          print @rptline
1387      end
1388  
1389      print @blankline
1390      select @tmp_total = value
1391      from #tempmonitors
1392      where group_name = "locks" and
1393          field_name = "clm_deadlocks"
1394  
1395      if @tmp_total != 0 /* Avoid Divide by Zero Errors */
1396      begin
1397  
1398          select @rptline = "  Total Cluster Deadlocks" + space(4) +
1399              str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1400              space(2) +
1401              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1402              space(2) +
1403              str(@tmp_total, 10)
1404          print @rptline
1405      end
1406  
1407      print @blankline
1408      print @blankline
1409  
1410      select @tmp_int2 = @tmp_total /* save for deadlock search calc */
1411  
1412      print "  Deadlock Detection"
1413  
1414      select @tmp_total = value
1415      from #tempmonitors
1416      where group_name = "lock" and
1417          field_name = "deadlock_search"
1418  
1419      select @rptline = "    Deadlock Searches" + space(8) +
1420          str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1421          space(2) +
1422          str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1423          space(2) +
1424          str(@tmp_total, 10) + space(7) +
1425          @na_str
1426      print @rptline
1427  
1428      if @tmp_total != 0 /* Avoid Divide by Zero Errors */
1429      begin
1430  
1431          select @tmp_int = value
1432          from #tempmonitors
1433          where group_name = "lock" and
1434              field_name = "deadlock_alarm_skipped"
1435  
1436          select @rptline = "    Searches Skipped" + space(9) +
1437              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1438              space(2) +
1439              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1440              space(2) +
1441              str(@tmp_int, 10) + space(5) +
1442              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1443          print @rptline
1444  
1445          select @rptline = "    Avg Deadlocks per Search" + space(10) +
1446              @na_str +
1447              space(11) +
1448              @na_str +
1449              space(2) +
1450              str(convert(real, @tmp_int2) / @tmp_total, 10, 5) +
1451              space(7) +
1452              @na_str
1453          print @rptline
1454      end
1455      print @blankline
1456      print @blankline
1457  
1458      print "  Lock Promotions"
1459  
1460      select @tmp_total = SUM(value)
1461      from #tempmonitors
1462      where group_name = "lock" and
1463          (field_name like "promoted_%_PAGE"
1464              or field_name like "promoted_%_ROW"
1465              or field_name like "promoted_%_NKL")
1466  
1467      if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1468      begin
1469          select @rptline = "    Total Lock Promotions             0.0           0.0           0       n/a"
1470          print @rptline
1471      end
1472      else
1473      begin
1474  
1475          select @tmp_int = value
1476          from #tempmonitors
1477          where group_name = "lock" and
1478              field_name = "promoted_EX_PAGE"
1479  
1480          select @rptline = "    Ex-Page to Ex-Table" + space(6) +
1481              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1482              space(2) +
1483              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1484              space(2) +
1485              str(@tmp_int, 10) + space(5) +
1486              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1487          print @rptline
1488  
1489          select @tmp_int = value
1490          from #tempmonitors
1491          where group_name = "lock" and
1492              field_name = "promoted_SH_PAGE"
1493  
1494          select @rptline = "    Sh-Page to Sh-Table" + space(6) +
1495              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1496              space(2) +
1497              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1498              space(2) +
1499              str(@tmp_int, 10) + space(5) +
1500              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1501          print @rptline
1502  
1503          select @tmp_int = value
1504          from #tempmonitors
1505          where group_name = "lock" and
1506              field_name = "promoted_EX_ROW"
1507  
1508          select @rptline = "    Ex-Row to Ex-Table" + space(7) +
1509              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1510              space(2) +
1511              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1512              space(2) +
1513              str(@tmp_int, 10) + space(5) +
1514              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1515          print @rptline
1516  
1517          select @tmp_int = value
1518          from #tempmonitors
1519          where group_name = "lock" and
1520              field_name = "promoted_SH_ROW"
1521  
1522          select @rptline = "    Sh-Row to Sh-Table" + space(7) +
1523              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1524              space(2) +
1525              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1526              space(2) +
1527              str(@tmp_int, 10) + space(5) +
1528              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1529          print @rptline
1530  
1531          select @tmp_int = value
1532          from #tempmonitors
1533          where group_name = "lock" and
1534              field_name = "promoted_SH_NKL"
1535  
1536          select @rptline = "    Sh-Next-Key to Sh-Table" + space(2) +
1537              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1538              space(2) +
1539              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1540              space(2) +
1541              str(@tmp_int, 10) + space(5) +
1542              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1543          print @rptline
1544  
1545          print @sum2line
1546          select @rptline = "  Total Lock Promotions" + space(6) +
1547              str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1548              space(2) +
1549              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1550              space(2) +
1551              str(@tmp_total, 10)
1552          print @rptline
1553      end
1554      print @blankline
1555      print @blankline
1556  
1557      print "  Lock Timeouts by Lock Type      per sec      per xact       count  %% of total"
1558      print @sum1line
1559  
1560      select @tmp_total = sum(value)
1561      from #tempmonitors
1562      where group_name = "lock" and
1563          field_name like "timedout%"
1564  
1565      if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1566      begin
1567          select @rptline = "  Total Timeouts                      0.0           0.0           0       n/a"
1568          print @rptline
1569      end
1570      else
1571      begin
1572  
1573          select @tmp_int = value
1574          from #tempmonitors
1575          where group_name = "lock" and
1576              field_name = "timedout_EX_TAB"
1577  
1578          select @rptline = "    Exclusive Table" + space(10) +
1579              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1580              space(2) +
1581              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1582              space(2) +
1583              str(@tmp_int, 10) + space(5) +
1584              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1585          print @rptline
1586  
1587          select @tmp_int = value
1588          from #tempmonitors
1589          where group_name = "lock" and
1590              field_name = "timedout_SH_TAB"
1591  
1592          select @rptline = "    Shared Table" + space(13) +
1593              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1594              space(2) +
1595              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1596              space(2) +
1597              str(@tmp_int, 10) + space(5) +
1598              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1599          print @rptline
1600  
1601          select @tmp_int = value
1602          from #tempmonitors
1603          where group_name = "lock" and
1604              field_name = "timedout_EX_INT"
1605  
1606          select @rptline = "    Exclusive Intent" + space(9) +
1607              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1608              space(2) +
1609              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1610              space(2) +
1611              str(@tmp_int, 10) + space(5) +
1612              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1613          print @rptline
1614  
1615          select @tmp_int = value
1616          from #tempmonitors
1617          where group_name = "lock" and
1618              field_name = "timedout_SH_INT"
1619  
1620          select @rptline = "    Shared Intent" + space(12) +
1621              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1622              space(2) +
1623              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1624              space(2) +
1625              str(@tmp_int, 10) + space(5) +
1626              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1627          print @rptline
1628  
1629          select @tmp_int = value
1630          from #tempmonitors
1631          where group_name = "lock" and
1632              field_name = "timedout_EX_PAGE"
1633  
1634          select @rptline = "    Exclusive Page" + space(11) +
1635              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1636              space(2) +
1637              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1638              space(2) +
1639              str(@tmp_int, 10) + space(5) +
1640              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1641          print @rptline
1642  
1643          select @tmp_int = value
1644          from #tempmonitors
1645          where group_name = "lock" and
1646              field_name = "timedout_UP_PAGE"
1647  
1648          select @rptline = "    Update Page" + space(14) +
1649              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1650              space(2) +
1651              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1652              space(2) +
1653              str(@tmp_int, 10) + space(5) +
1654              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1655          print @rptline
1656  
1657          select @tmp_int = value
1658          from #tempmonitors
1659          where group_name = "lock" and
1660              field_name = "timedout_SH_PAGE"
1661  
1662          select @rptline = "    Shared Page" + space(14) +
1663              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1664              space(2) +
1665              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1666              space(2) +
1667              str(@tmp_int, 10) + space(5) +
1668              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1669          print @rptline
1670  
1671          select @tmp_int = value
1672          from #tempmonitors
1673          where group_name = "lock" and
1674              field_name = "timedout_EX_ROW"
1675  
1676          select @rptline = "    Exclusive Row" + space(12) +
1677              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1678              space(2) +
1679              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1680              space(2) +
1681              str(@tmp_int, 10) + space(5) +
1682              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1683          print @rptline
1684  
1685          select @tmp_int = value
1686          from #tempmonitors
1687          where group_name = "lock" and
1688              field_name = "timedout_UP_ROW"
1689  
1690          select @rptline = "    Update Row" + space(15) +
1691              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1692              space(2) +
1693              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1694              space(2) +
1695              str(@tmp_int, 10) + space(5) +
1696              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1697          print @rptline
1698  
1699          select @tmp_int = value
1700          from #tempmonitors
1701          where group_name = "lock" and
1702              field_name = "timedout_SH_ROW"
1703  
1704          select @rptline = "    Shared Row" + space(15) +
1705              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1706              space(2) +
1707              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1708              space(2) +
1709              str(@tmp_int, 10) + space(5) +
1710              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1711          print @rptline
1712  
1713          select @tmp_int = value
1714          from #tempmonitors
1715          where group_name = "lock" and
1716              field_name = "timedout_EX_ADDR"
1717  
1718          select @rptline = "    Exclusive Address" + space(8) +
1719              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1720              space(2) +
1721              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1722              space(2) +
1723              str(@tmp_int, 10) + space(5) +
1724              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1725          print @rptline
1726  
1727          select @tmp_int = value
1728          from #tempmonitors
1729          where group_name = "lock" and
1730              field_name = "timedout_SH_ADDR"
1731  
1732          select @rptline = "    Shared Address" + space(11) +
1733              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1734              space(2) +
1735              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1736              space(2) +
1737              str(@tmp_int, 10) + space(5) +
1738              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1739          print @rptline
1740  
1741          select @tmp_int = value
1742          from #tempmonitors
1743          where group_name = "lock" and
1744              field_name = "timedout_SH_NKL"
1745  
1746          select @rptline = "    Shared Next-Key" + space(10) +
1747              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1748              space(2) +
1749              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1750              space(2) +
1751              str(@tmp_int, 10) + space(5) +
1752              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1753          print @rptline
1754  
1755          print @sum2line
1756  
1757          select @rptline = "  Total Lock Timeouts" + space(8) +
1758              str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1759              space(2) +
1760              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1761              space(2) +
1762              str(@tmp_total, 10)
1763          print @rptline
1764      end
1765  
1766      print @blankline
1767      print "  Cluster Lock Summary            per sec      per xact       count  %% of total"
1768      print @sum1line
1769  
1770      select @tmp_total = sum(value)
1771      from #tempmonitors
1772      where group_name like "lock" and
1773          field_name like "clm_lock_gc"
1774      select @tmp_int = sum(value)
1775      from #tempmonitors
1776      where group_name like "lock" and
1777          field_name like "clm_lockgc_target_collected"
1778  
1779      if @tmp_total != 0 /* Avoid Divide by Zero Errors after printout */
1780      begin
1781          print @blankline
1782          select @rptline = "   Lock Garbage Collection   " +
1783              str(@tmp_total / @NumElapsedSec, 12, 1) +
1784              space(2) +
1785              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1786              space(2) +
1787              str(@tmp_total, 10) + space(7) +
1788              @na_str
1789  
1790          print @rptline
1791          select @rptline = "     Targeted Collection Sucess" +
1792              space(7) +
1793              @na_str +
1794              space(11) +
1795              @na_str +
1796              space(2) +
1797              str(@tmp_int, 10) + space(5) +
1798              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1799          print @rptline
1800          print @blankline
1801      end
1802  
1803      print @blankline
1804      print "  Physical Locks Summary          per sec      per xact       count  %% of total"
1805      print @sum1line
1806  
1807      select @totalrequested = sum(value)
1808      from #tempmonitors
1809      where group_name like "buffer_%" and
1810          field_name like "physical_lock_acquisition"
1811  
1812      select @totalretained = sum(value)
1813      from #tempmonitors
1814      where group_name like "buffer_%" and
1815          field_name like "physical_lock_retented"
1816  
1817      select @totalwaits = sum(value)
1818      from #tempmonitors
1819      where group_name like "lock" and
1820          field_name like "physical_lock_waits"
1821  
1822      select @totaltransfers = sum(value)
1823      from #tempmonitors
1824      where group_name like "buffer_%" and
1825          field_name like "physical_lock_txrecv"
1826  
1827      select @totaldiskreads = sum(value)
1828      from #tempmonitors
1829      where group_name like "buffer_%" and
1830          field_name like "diskread_lockmgr"
1831  
1832      select @totallockmgrcalls = sum(value)
1833      from #tempmonitors
1834      where group_name like "buffer_%" and
1835          field_name like "physical_lock_txrecv"
1836  
1837      if @totalrequested = 0
1838      begin
1839          print "No physical locks are acquired"
1840      end
1841      else
1842      begin
1843          select @rptline = "    Total Lock Requests" + space(6) +
1844              str(@totalrequested / @NumElapsedSec, 12, 1) +
1845              space(2) +
1846              str(@totalrequested / convert(real, @NumXacts), 12, 1) +
1847              space(2) +
1848              str(@totalrequested, 10) + space(5) +
1849              str(100.0 * @totalrequested / @totalrequested, 5, 1) + @psign
1850          print @rptline
1851  
1852          select @rptline = "    Retained Locks    " + space(7) +
1853              str(@totalretained / @NumElapsedSec, 12, 1) +
1854              space(2) +
1855              str(@totalretained / convert(real, @NumXacts), 12, 1) +
1856              space(2) +
1857              str(@totalretained, 10) + space(5) +
1858              str(100.0 * @totalretained / @totalrequested, 5, 1) + @psign
1859          print @rptline
1860  
1861          select @rptline = "    Non-retained Locks" + space(7) +
1862              str((@totalrequested - @totalretained) /
1863                  @NumElapsedSec, 12, 1) +
1864              space(2) +
1865              str((@totalrequested - @totalretained) /
1866                  convert(real, @NumXacts), 12, 1) +
1867              space(2) +
1868              str((@totalrequested - @totalretained), 10) + space(5) +
1869              str(100.0 * (@totalrequested - @totalretained) /
1870                  @totalrequested, 5, 1) + @psign
1871          print @rptline
1872  
1873          select @rptline = "    Transfers Received  " + space(5) +
1874              str(@totaltransfers / @NumElapsedSec, 12, 1) +
1875              space(2) +
1876              str(@totaltransfers / convert(real, @NumXacts), 12, 1) +
1877              space(2) +
1878              str(@totaltransfers, 10) + space(5) +
1879              str(100.0 * @totaltransfers / @totalrequested, 5, 1) + @psign
1880          print @rptline
1881  
1882          select @rptline = "    Data Read from Disk" + space(6) +
1883              str(@totaldiskreads / @NumElapsedSec, 12, 1) +
1884              space(2) +
1885              str(@totaldiskreads / convert(real, @NumXacts), 12, 1) +
1886              space(2) +
1887              str(@totaldiskreads, 10) + space(5) +
1888              str(100.0 * @totaldiskreads / @totalrequested, 5, 1) + @psign
1889          print @rptline
1890  
1891          if (@totalrequested - @totalretained) = 0
1892          begin
1893              select @rptline = "    Physical Lock Contention" + space(1) +
1894                  str(0 / @NumElapsedSec, 12, 1) +
1895                  space(2) +
1896                  str(0 / convert(real, @NumXacts), 12, 1) +
1897                  space(2) +
1898                  str(0, 10) + space(5) +
1899                  str(0.0, 5, 1) +
1900                  @psign
1901          end
1902          else
1903          begin
1904              select @rptline = "    Physical Lock Contention" + space(1) +
1905                  str(@totalwaits / @NumElapsedSec, 12, 1) +
1906                  space(2) +
1907                  str(@totalwaits / convert(real, @NumXacts), 12, 1) +
1908                  space(2) +
1909                  str(@totalwaits, 10) + space(5) +
1910                  str(100.0 * @totalwaits /
1911                      (@totalrequested - @totalretained), 5, 1) +
1912                  @psign
1913              print @rptline
1914          end
1915  
1916          if @totallockmgrcalls = 0
1917          begin
1918              select @rptline = "    Data Location Efficiency" + space(9) +
1919                  " n/a " +
1920                  space(9) +
1921                  " n/a " +
1922                  space(7) +
1923                  " n/a " + space(4) +
1924                  str(100.0, 5, 1) + @psign
1925          end
1926          else
1927          begin
1928              select @rptline = "    Data Location Efficiency" + space(9) +
1929                  " n/a " +
1930                  space(9) +
1931                  " n/a " +
1932                  space(7) +
1933                  " n/a " + space(4) +
1934                  str(100.0 - (100.0 * @totaltransfers /
1935                      @totallockmgrcalls), 5, 1) +
1936                  @psign
1937          end
1938  
1939          print @rptline
1940      end
1941  
1942      select @tmp_total = sum(value)
1943      from #tempmonitors
1944      where group_name like "bcmt" and
1945          field_name like "bcmt_pri_req_queued"
1946  
1947      if @tmp_total > 0
1948      begin
1949          print @blankline
1950          print "  Cluster Cache Manager Requests"
1951  
1952          select @rptline = "    Total Service Requests   " +
1953              str(@tmp_total / @NumElapsedSec, 12, 1) +
1954              space(2) +
1955              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1956              space(2) +
1957              str(@tmp_total, 10) + space(5) +
1958              str(100.0 * @tmp_total / @tmp_total, 5, 1) + @psign
1959          print @rptline
1960  
1961          select @tmp_int = sum(value)
1962          from #tempmonitors
1963          where group_name like "bcmt" and
1964              field_name in ("bcmt_pri_diskwrites", "bcmt_sec_diskwrites")
1965  
1966          select @rptline = "    Disk Writes              " +
1967              str(@tmp_int / @NumElapsedSec, 12, 1) +
1968              space(2) +
1969              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1970              space(2) +
1971              str(@tmp_int, 10) + space(5) +
1972              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1973          print @rptline
1974  
1975          select @tmp_int = sum(value)
1976          from #tempmonitors
1977          where group_name like "bcmt" and
1978              field_name like "bcmt_num_of_tx_initiated"
1979  
1980          select @rptline = "    Transfer Requests        " +
1981              str(@tmp_int / @NumElapsedSec, 12, 1) +
1982              space(2) +
1983              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1984              space(2) +
1985              str(@tmp_int, 10) + space(5) +
1986              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1987          print @rptline
1988  
1989          select @tmp_int = sum(value)
1990          from #tempmonitors
1991          where group_name like "bcmt" and
1992              field_name like "bcmt_num_of_downgrades"
1993  
1994          select @rptline = "    Downgrade Requests       " +
1995              str(@tmp_int / @NumElapsedSec, 12, 1) +
1996              space(2) +
1997              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1998              space(2) +
1999              str(@tmp_int, 10) + space(5) +
2000              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2001          print @rptline
2002  
2003          select @tmp_int = sum(value)
2004          from #tempmonitors
2005          where group_name like "bcmt" and
2006              field_name in ("bcmt_num_of_downgrades_data_ex_to_pr",
2007                  "bcmt_num_of_downgrades_data_ex_to_nl",
2008                  "bcmt_num_of_downgrades_data_pr_to_nl")
2009  
2010          select @rptline = "      Data Page Requests     " +
2011              str(@tmp_int / @NumElapsedSec, 12, 1) +
2012              space(2) +
2013              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2014              space(2) +
2015              str(@tmp_int, 10) + space(5) +
2016              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2017          print @rptline
2018  
2019          select @tmp_int = sum(value)
2020          from #tempmonitors
2021          where group_name like "bcmt" and
2022              field_name in ("bcmt_num_of_downgrades_index_ex_to_pr",
2023                  "bcmt_num_of_downgrades_index_ex_to_nl",
2024                  "bcmt_num_of_downgrades_index_pr_to_nl")
2025  
2026          select @rptline = "      Index Page Requests    " +
2027              str(@tmp_int / @NumElapsedSec, 12, 1) +
2028              space(2) +
2029              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2030              space(2) +
2031              str(@tmp_int, 10) + space(5) +
2032              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2033          print @rptline
2034  
2035          select @tmp_int = sum(value)
2036          from #tempmonitors
2037          where group_name like "bcmt" and
2038              field_name in ("bcmt_num_of_downgrades_sysalloc_ex_to_pr",
2039                  "bcmt_num_of_downgrades_sysalloc_ex_to_nl",
2040                  "bcmt_num_of_downgrades_sysalloc_pr_to_nl")
2041  
2042          select @rptline = "      Alloc Page Requests    " +
2043              str(@tmp_int / @NumElapsedSec, 12, 1) +
2044              space(2) +
2045              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2046              space(2) +
2047              str(@tmp_int, 10) + space(5) +
2048              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2049          print @rptline
2050  
2051          select @tmp_int = sum(value)
2052          from #tempmonitors
2053          where group_name like "bcmt" and
2054              field_name in ("bcmt_num_of_downgrades_firstoam_ex_to_pr",
2055                  "bcmt_num_of_downgrades_firstoam_ex_to_nl",
2056                  "bcmt_num_of_downgrades_firstoam_pr_to_nl")
2057  
2058          select @rptline = "      First OAM Page Requests" +
2059              str(@tmp_int / @NumElapsedSec, 12, 1) +
2060              space(2) +
2061              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2062              space(2) +
2063              str(@tmp_int, 10) + space(5) +
2064              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2065          print @rptline
2066  
2067          select @tmp_int = sum(value)
2068          from #tempmonitors
2069          where group_name like "bcmt" and
2070              field_name in ("bcmt_num_of_downgrades_nonfirstoam_ex_to_pr",
2071                  "bcmt_num_of_downgrades_nonfirstoam_ex_to_nl",
2072                  "bcmt_num_of_downgrades_nonfirstoam_pr_to_nl")
2073  
2074          select @rptline = "      Other OAM Page Requests" +
2075              str(@tmp_int / @NumElapsedSec, 12, 1) +
2076              space(2) +
2077              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2078              space(2) +
2079              str(@tmp_int, 10) + space(5) +
2080              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2081          print @rptline
2082  
2083      end
2084  
2085      select @totalreqs = sum(value)
2086      from #tempmonitors
2087      where group_name like "lock" and
2088          field_name like "clm_physical_lockreqs"
2089      select @totallocalmaster = sum(value)
2090      from #tempmonitors
2091      where group_name like "lock" and
2092          field_name like "clm_physical_local_master"
2093      select @totalwaits = sum(value)
2094      from #tempmonitors
2095      where group_name like "lock" and
2096          field_name like "clm_physical_waited"
2097      select @totalgrants = @totalreqs - @totalwaits
2098  
2099      print @blankline
2100      if @totalreqs != 0 /* Avoid Divide by Zero Errors after printout */
2101      begin
2102          select @rptline = "    Cluster Lock Requests    " +
2103              str(@totalreqs / @NumElapsedSec, 12, 1) +
2104              space(2) +
2105              str(@totalreqs / convert(real, @NumXacts), 12, 1) +
2106              space(2) +
2107              str(@totalreqs, 10) + space(5) +
2108              str(100.0 * @totalreqs / @totalreqs, 5, 1) + @psign
2109          print @rptline
2110          select @rptline = "    Local Master       " + space(6) +
2111              str(@totallocalmaster / @NumElapsedSec, 12, 1) +
2112              space(2) +
2113              str(@totallocalmaster / convert(real, @NumXacts), 12, 1) +
2114              space(2) +
2115              str(@totallocalmaster, 10) + space(5) +
2116              str(100.0 * @totallocalmaster / @totalreqs, 5, 1) + @psign
2117          print @rptline
2118          if @totalreqs != 0
2119          begin
2120              select @rptline = "      Lock Granted      " + space(5) +
2121                  str(@totalgrants / @NumElapsedSec, 12, 1) +
2122                  space(2) +
2123                  str(@totalgrants / convert(real, @NumXacts), 12, 1) +
2124                  space(2) +
2125                  str(@totalgrants, 10) + space(5) +
2126                  str(100.0 * @totalgrants / @totalreqs, 5, 1) + @psign
2127              print @rptline
2128              select @rptline = "      Lock Waited       " + space(5) +
2129                  str(@totalwaits / @NumElapsedSec, 12, 1) +
2130                  space(2) +
2131                  str(@totalwaits / convert(real, @NumXacts), 12, 1) +
2132                  space(2) +
2133                  str(@totalwaits, 10) + space(5) +
2134                  str(100.0 * @totalwaits / @totalreqs, 5, 1) + @psign
2135              print @rptline
2136          end
2137      end
2138  
2139      select @totalbastreqs = sum(value)
2140      from #tempmonitors
2141      where group_name like "lock" and
2142          field_name like "clm_physical_bast_requests"
2143  
2144      if @totalbastreqs != 0 /* Avoid Divide by Zero Errors after printout */
2145      begin
2146          select @rptline = "    Downgrade Req Recv       " +
2147              str(@totalbastreqs / @NumElapsedSec, 12, 1) +
2148              space(11) +
2149              @na_str +
2150              space(2) +
2151              str(@totalbastreqs, 10) +
2152              space(7) +
2153              @na_str
2154          print @rptline
2155      end
2156  
2157      print @blankline
2158      print "  Logical Locks Summary          per sec      per xact       count  %% of total"
2159      print @sum1line
2160      select @totalreqs = sum(value)
2161      from #tempmonitors
2162      where group_name like "lock" and
2163          field_name like "clm_logical_lockreqs"
2164      select @totallocalmaster = sum(value)
2165      from #tempmonitors
2166      where group_name like "lock" and
2167          field_name like "clm_logical_local_master"
2168      select @totalwaits = sum(value)
2169      from #tempmonitors
2170      where group_name like "lock" and
2171          field_name like "clm_logical_waited"
2172      select @totalgrants = @totalreqs - @totalwaits
2173  
2174      if @totalreqs != 0 /* Avoid Divide by Zero Errors after printout */
2175      begin
2176          select @rptline = "    Cluster Lock Requests    " +
2177              str(@totalreqs / @NumElapsedSec, 12, 1) +
2178              space(2) +
2179              str(@totalreqs / convert(real, @NumXacts), 12, 1) +
2180              space(2) +
2181              str(@totalreqs, 10) + space(5) +
2182              str(100.0 * @totalreqs / @totalreqs, 5, 1) + @psign
2183          print @rptline
2184          select @rptline = "    Local Master             " +
2185              str(@totallocalmaster / @NumElapsedSec, 12, 1) +
2186              space(2) +
2187              str(@totallocalmaster / convert(real, @NumXacts), 12, 1) +
2188              space(2) +
2189              str(@totallocalmaster, 10) + space(5) +
2190              str(100.0 * @totallocalmaster / @totalreqs, 5, 1) + @psign
2191          print @rptline
2192          if @totalreqs != 0
2193          begin
2194              select @rptline = "      Lock Granted      " + space(5) +
2195                  str(@totalgrants / @NumElapsedSec, 12, 1) +
2196                  space(2) +
2197                  str(@totalgrants / convert(real, @NumXacts), 12, 1) +
2198                  space(2) +
2199                  str(@totalgrants, 10) + space(5) +
2200                  str(100.0 * @totalgrants / @totalreqs, 5, 1) + @psign
2201              print @rptline
2202              select @rptline = "      Lock Waited       " + space(5) +
2203                  str(@totalwaits / @NumElapsedSec, 12, 1) +
2204                  space(2) +
2205                  str(@totalwaits / convert(real, @NumXacts), 12, 1) +
2206                  space(2) +
2207                  str(@totalwaits, 10) + space(5) +
2208                  str(100.0 * @totalwaits / @totalreqs, 5, 1) + @psign
2209              print @rptline
2210          end
2211      end
2212  
2213      select @totalbastreqs = sum(value)
2214      from #tempmonitors
2215      where group_name like "lock" and
2216          field_name like "clm_logical_bast_requests"
2217  
2218      if @totalbastreqs != 0 /* Avoid Divide by Zero Errors after printout */
2219      begin
2220          select @rptline = "    Downgrade Req Recv       " +
2221              str(@totalbastreqs / @NumElapsedSec, 12, 1) +
2222              space(11) +
2223              @na_str +
2224              space(2) +
2225              str(@totalbastreqs, 10) +
2226              space(7) +
2227              @na_str
2228          print @rptline
2229      end
2230  
2231      print @blankline
2232      print "  Object Locks Summary            per sec      per xact       count  %% of total"
2233      print @sum1line
2234  
2235      select @totalreqs = sum(value)
2236      from #tempmonitors
2237      where group_name like "lock" and
2238          field_name like "clm_ocm_lockreqs"
2239      select @totallocalmaster = sum(value)
2240      from #tempmonitors
2241      where group_name like "lock" and
2242          field_name like "clm_ocm_local_master"
2243      select @totalwaits = sum(value)
2244      from #tempmonitors
2245      where group_name like "lock" and
2246          field_name like "clm_ocm_waited"
2247      select @totalgrants = @totalreqs - @totalwaits
2248  
2249      if @totalreqs != 0 /* Avoid Divide by Zero Errors after printout */
2250      begin
2251          select @rptline = "    Cluster Lock Requests    " +
2252              str(@totalreqs / @NumElapsedSec, 12, 1) +
2253              space(2) +
2254              str(@totalreqs / convert(real, @NumXacts), 12, 1) +
2255              space(2) +
2256              str(@totalreqs, 10) + space(5) +
2257              str(100.0 * @totalreqs / @totalreqs, 5, 1) + @psign
2258          print @rptline
2259          select @rptline = "    Local Master             " +
2260              str(@totallocalmaster / @NumElapsedSec, 12, 1) +
2261              space(2) +
2262              str(@totallocalmaster / convert(real, @NumXacts), 12, 1) +
2263              space(2) +
2264              str(@totallocalmaster, 10) + space(5) +
2265              str(100.0 * @totallocalmaster / @totalreqs, 5, 1) + @psign
2266          print @rptline
2267          if @totalreqs != 0
2268          begin
2269              select @rptline = "      Lock Granted      " + space(5) +
2270                  str(@totalgrants / @NumElapsedSec, 12, 1) +
2271                  space(2) +
2272                  str(@totalgrants / convert(real, @NumXacts), 12, 1) +
2273                  space(2) +
2274                  str(@totalgrants, 10) + space(5) +
2275                  str(100.0 * @totalgrants / @totalreqs, 5, 1) + @psign
2276              print @rptline
2277              select @rptline = "      Lock Waited       " + space(5) +
2278                  str(@totalwaits / @NumElapsedSec, 12, 1) +
2279                  space(2) +
2280                  str(@totalwaits / convert(real, @NumXacts), 12, 1) +
2281                  space(2) +
2282                  str(@totalwaits, 10) + space(5) +
2283                  str(100.0 * @totalwaits / @totalreqs, 5, 1) + @psign
2284              print @rptline
2285          end
2286      end
2287  
2288      select @totalbastreqs = sum(value)
2289      from #tempmonitors
2290      where group_name like "lock" and
2291          field_name like "clm_ocm_bast_requests"
2292  
2293      if @totalbastreqs != 0 /* Avoid Divide by Zero Errors after printout */
2294      begin
2295          select @rptline = "    Downgrade Req Recv       " +
2296              str(@totalbastreqs / @NumElapsedSec, 12, 1) +
2297              space(11) +
2298              @na_str +
2299              space(2) +
2300              str(@totalbastreqs, 10) +
2301              space(7) +
2302              @na_str
2303          print @rptline
2304      end
2305  
2306      /*****************  Tuning Recommendations  ************************/
2307  
2308      if @Reco = 'Y'
2309      begin
2310          select @reco_hdr_prn = 0
2311          print @blankline
2312  
2313          select @deadlocks = value from #tempmonitors
2314          where group_name = "lock"
2315              and field_name = "deadlocks"
2316  
2317          select @deadlock_search = value from #tempmonitors
2318          where group_name = "lock"
2319              and field_name = "deadlock_search"
2320  
2321          /*
2322          ** If the number of deadlocks is 0 and the
2323          ** number of times deadlock search was done
2324          ** is greater than 0, consider increasing the
2325          ** 'deadlock checking period' parameter
2326          */
2327          if (@deadlocks = 0 and @deadlock_search > 0)
2328          begin
2329              if (@reco_hdr_prn = 0)
2330              begin
2331                  print @recotxt
2332                  print @recoline
2333                  select @reco_hdr_prn = 1
2334              end
2335              print "  - Consider increasing the 'deadlock checking period' parameter"
2336              print "    by 50 ms."
2337              print @blankline
2338          end
2339          /*
2340          ** If the number of deadlocks is greated than 0 and the
2341          ** number of times deadlock search was done is 0,
2342          ** consider decreasing the 'deadlock checking period' parameter
2343          */
2344          else if (@deadlocks > 0 and @deadlock_search = 0)
2345          begin
2346              if (@reco_hdr_prn = 0)
2347              begin
2348                  print @recotxt
2349                  print @recoline
2350                  select @reco_hdr_prn = 1
2351              end
2352              print "  - Consider decreasing the 'deadlock checking period' parameter"
2353              print "    by 50 ms."
2354              print @blankline
2355          end
2356  
2357          /*
2358          ** If the percentage of waits on the last page
2359          ** is > 10% consider partitioning the table or
2360          ** increasing the number of partitions if the
2361          ** table is already partitioned
2362          */
2363          if (@reco_lastpg_wait_percent > 10)
2364          begin
2365              if (@reco_hdr_prn = 0)
2366              begin
2367                  print @recotxt
2368                  print @recoline
2369                  select @reco_hdr_prn = 1
2370              end
2371              print "  - Consider partitioning the heap tables or increasing the"
2372              print "    number of partitions on already partitioned heap tables."
2373              print @blankline
2374          end
2375  
2376          /*
2377          ** If the average length of overflow chain for page & row lock 
2378          ** hashtable  is > 4 consider increasing the 'lock hashtable size'
2379          ** to the next power of 2 from the current configured value.
2380          */
2381          if (@reco_avg_chain_length_pagerow > 4)
2382          begin
2383              if (@reco_hdr_prn = 0)
2384              begin
2385                  print @recotxt
2386                  print @recoline
2387                  select @reco_hdr_prn = 1
2388              end
2389              print "  - Consider increasing the value of 'lock hashtable size' "
2390              print "    configuration option. The value for this configuration "
2391              print "    option must be a power of 2."
2392              print @blankline
2393          end
2394  
2395          /*
2396          ** If the number of engines is > 1 report recommendation for lock 
2397          ** manager related spinlock contentions.
2398          */
2399          if (@NumEngines > 1)
2400          begin /* { */
2401              /* 
2402              ** if the contention on the fglock hashtable spinlocks 
2403              ** is >= 10%, consider decreasing the 'lock spinlock ratio' 
2404              ** config option.
2405              */
2406              if (@reco_fgspin_contention >= 10)
2407              begin
2408                  if (@reco_hdr_prn = 0)
2409                  begin /* { */
2410                      print @recotxt
2411                      print @recoline
2412                      select @reco_hdr_prn = 1
2413                  end /* } */
2414                  print "    - Consider reducing the 'lock spinlock ratio'."
2415                  print @blankline
2416              end
2417              /* 
2418              ** if the contention on the table lock hashtable spinlocks 
2419              ** is >= 10%, consider decreasing the 'lock table spinlock 
2420              ** ratio' config option.
2421              */
2422              if (@reco_tabspin_contention >= 10)
2423              begin
2424                  if (@reco_hdr_prn = 0)
2425                  begin /* { */
2426                      print @recotxt
2427                      print @recoline
2428                      select @reco_hdr_prn = 1
2429                  end /* } */
2430                  print "    - Consider reducing the 'lock table spinlock ratio'."
2431                  print @blankline
2432              end
2433              /* 
2434              ** if the contention on the address lock hashtable spinlocks 
2435              ** is >= 10%, consider decreasing the 'lock address spinlock 
2436              ** ratio' config option.
2437              */
2438              if (@reco_addrspin_contention >= 10)
2439              begin
2440                  if (@reco_hdr_prn = 0)
2441                  begin /* { */
2442                      print @recotxt
2443                      print @recoline
2444                      select @reco_hdr_prn = 1
2445                  end /* } */
2446                  print "    - Consider reducing the 'lock address spinlock ratio'."
2447                  print @blankline
2448              end
2449          end /* } */
2450      end
2451      print @blankline
2452      return 0
2453  


exec sp_procxmode 'sp_sysmon_locks', 'AnyMode'
go

Grant Execute on sp_sysmon_locks to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_locks  
 MLCH 3 Char type with length>30 char(80) 22
 MLCH 3 Char type with length>30 char(67) 24
 MLCH 3 Char type with length>30 char(80) 29
 MLCH 3 Char type with length>30 char(80) 30
 MLCH 3 Char type with length>30 char(80) 33
 MLCH 3 Char type with length>30 char(80) 34
 MNER 3 No Error Check should check @@error after select into 156
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 465
 MUCO 3 Useless Code Useless Brackets 914
 MUCO 3 Useless Code Useless Brackets 1934
 MUCO 3 Useless Code Useless Brackets 2327
 MUCO 3 Useless Code Useless Brackets 2329
 MUCO 3 Useless Code Useless Brackets 2344
 MUCO 3 Useless Code Useless Brackets 2346
 MUCO 3 Useless Code Useless Brackets 2363
 MUCO 3 Useless Code Useless Brackets 2365
 MUCO 3 Useless Code Useless Brackets 2381
 MUCO 3 Useless Code Useless Brackets 2383
 MUCO 3 Useless Code Useless Brackets 2399
 MUCO 3 Useless Code Useless Brackets 2406
 MUCO 3 Useless Code Useless Brackets 2408
 MUCO 3 Useless Code Useless Brackets 2422
 MUCO 3 Useless Code Useless Brackets 2424
 MUCO 3 Useless Code Useless Brackets 2438
 MUCO 3 Useless Code Useless Brackets 2440
 QAFM 3 Var Assignment from potentially many rows 175
 QAFM 3 Var Assignment from potentially many rows 192
 QAFM 3 Var Assignment from potentially many rows 451
 QAFM 3 Var Assignment from potentially many rows 467
 QAFM 3 Var Assignment from potentially many rows 899
 QAFM 3 Var Assignment from potentially many rows 916
 QAFM 3 Var Assignment from potentially many rows 1074
 QAFM 3 Var Assignment from potentially many rows 1119
 QAFM 3 Var Assignment from potentially many rows 1140
 QAFM 3 Var Assignment from potentially many rows 1155
 QAFM 3 Var Assignment from potentially many rows 1169
 QAFM 3 Var Assignment from potentially many rows 1183
 QAFM 3 Var Assignment from potentially many rows 1197
 QAFM 3 Var Assignment from potentially many rows 1211
 QAFM 3 Var Assignment from potentially many rows 1225
 QAFM 3 Var Assignment from potentially many rows 1239
 QAFM 3 Var Assignment from potentially many rows 1253
 QAFM 3 Var Assignment from potentially many rows 1267
 QAFM 3 Var Assignment from potentially many rows 1281
 QAFM 3 Var Assignment from potentially many rows 1295
 QAFM 3 Var Assignment from potentially many rows 1310
 QAFM 3 Var Assignment from potentially many rows 1324
 QAFM 3 Var Assignment from potentially many rows 1361
 QAFM 3 Var Assignment from potentially many rows 1390
 QAFM 3 Var Assignment from potentially many rows 1414
 QAFM 3 Var Assignment from potentially many rows 1431
 QAFM 3 Var Assignment from potentially many rows 1475
 QAFM 3 Var Assignment from potentially many rows 1489
 QAFM 3 Var Assignment from potentially many rows 1503
 QAFM 3 Var Assignment from potentially many rows 1517
 QAFM 3 Var Assignment from potentially many rows 1531
 QAFM 3 Var Assignment from potentially many rows 1573
 QAFM 3 Var Assignment from potentially many rows 1587
 QAFM 3 Var Assignment from potentially many rows 1601
 QAFM 3 Var Assignment from potentially many rows 1615
 QAFM 3 Var Assignment from potentially many rows 1629
 QAFM 3 Var Assignment from potentially many rows 1643
 QAFM 3 Var Assignment from potentially many rows 1657
 QAFM 3 Var Assignment from potentially many rows 1671
 QAFM 3 Var Assignment from potentially many rows 1685
 QAFM 3 Var Assignment from potentially many rows 1699
 QAFM 3 Var Assignment from potentially many rows 1713
 QAFM 3 Var Assignment from potentially many rows 1727
 QAFM 3 Var Assignment from potentially many rows 1741
 QAFM 3 Var Assignment from potentially many rows 2313
 QAFM 3 Var Assignment from potentially many rows 2317
 QCTC 3 Conditional Table Creation 156
 QJWT 3 Join or Sarg Without Index on temp table 166
 QJWT 3 Join or Sarg Without Index on temp table 167
 QNAJ 3 Not using ANSI Inner Join 161
 VNRD 3 Variable is not read @reco_hdr_prn 2444
 VUNU 3 Variable is not used @maxengfreelocks 35
 VUNU 3 Variable is not used @max_online_engine 36
 VUNU 3 Variable is not used @freelock_tr_blksize 37
 VUNU 3 Variable is not used @tempmaxengfreelocks 40
 VUNU 3 Variable is not used @curfreelocks 41
 VUNU 3 Variable is not used @tot_movefreelock_toengine 42
 VUNU 3 Variable is not used @tot_movefreelock_toserver 43
 VUNU 3 Variable is not used @newval 44
 VUNU 3 Variable is not used @tmp_num_lock_op 45
 MTR1 2 Metrics: Comments Ratio Comments: 5% 6
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 204 = 203dec - 1exi + 2 6
 MTR3 2 Metrics: Query Complexity Complexity: 1142 6

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

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