DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_locks  14 déc. 14Defects Propagation 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 or replace 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 & Partition Lock Hashtable"
175           select @tmp_int = value
176           from #tempmonitors
177           where group_name = "lock" and
178               (field_name = "hashtab_lookups_table_lock" or
179                   field_name = "hashtab_lookups_partition_lock")
180   
181   
182           select @rptline = "    Lookups" + space(18) +
183               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
184               space(2) +
185               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
186               space(2) +
187               str(@tmp_int, 10) + space(7) +
188               @na_str
189           print @rptline
190   
191           /* Avoid divide by 0 error */
192           if (@tmp_int > 0)
193           begin
194               select @tmp_int2 = value
195               from #tempmonitors
196               where group_name = "lock" and
197                   (field_name = "hashtab_overflow_table_lock" or
198                       field_name = "hashtab_overflow_partition_lock")
199   
200               select @rptline = "    Avg Chain Length" + space(18) +
201                   @na_str + space(11) +
202                   @na_str + space(2) +
203                   str(convert(real, @tmp_int2) / @tmp_int, 10, 5) +
204                   space(7) + @na_str
205               print @rptline
206           end
207   
208           select @spinlock_contention = isnull(100.0 * (sum(convert(float, waits)) / sum(convert(float, grabs))), 0)
209           from #foo
210           where name = 'tablockspins'
211               and grabs > 0
212   
213           select @rptline = "    Spinlock Contention" + space(15) +
214               @na_str + space(11) +
215               @na_str + space(9) +
216               @na_str + space(5) +
217               str(@spinlock_contention, 5, 1)
218               + @psign
219               , @reco_tabspin_contention = @spinlock_contention
220           print @rptline
221           print @blankline
222   
223           print "  Exclusive Table"
224   
225           select @tmp_total = SUM(value)
226           from #tempmonitors
227           where group_name = "lock" and
228               (field_name like "granted%_EX_TAB" or
229                   field_name like "waited%_EX_TAB")
230   
231           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
232           begin
233               select @rptline = "    Total EX-Table Requests           0.0           0.0           0       n/a"
234               print @rptline
235           end
236           else
237           begin
238               select @tmp_int = sum(value)
239               from #tempmonitors
240               where group_name = "lock" and
241                   field_name like "granted%_EX_TAB"
242   
243               select @rptline = "    Granted" + space(18) +
244                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
245                   space(2) +
246                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
247                   space(2) +
248                   str(@tmp_int, 10) + space(5) +
249                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
250                   @psign
251               print @rptline
252   
253               select @tmp_int = sum(value)
254               from #tempmonitors
255               where group_name = "lock" and
256                   field_name like "waited%_EX_TAB"
257   
258               select @rptline = "    Waited" + space(19) +
259                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
260                   space(2) +
261                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
262                   space(2) +
263                   str(@tmp_int, 10) + space(5) +
264                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
265                   @psign
266               print @rptline
267               print @sum1line
268               select @rptline = "  Total EX-Table Requests" + space(4) +
269                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
270                   space(2) +
271                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
272                   space(2) +
273                   str(@tmp_total, 10) + space(5) +
274                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
275                   @psign
276               print @rptline
277           end
278           print @blankline
279   
280           print "  Shared Table"
281   
282           select @tmp_total = SUM(value)
283           from #tempmonitors
284           where group_name = "lock" and
285               (field_name like "granted%_SH_TAB" or field_name like "waited%_SH_TAB")
286   
287           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
288           begin
289               select @rptline = "    Total SH-Table Requests           0.0           0.0           0       n/a"
290               print @rptline
291           end
292           else
293           begin
294               select @tmp_int = sum(value)
295               from #tempmonitors
296               where group_name = "lock" and
297                   field_name like "granted%_SH_TAB"
298   
299               select @rptline = "    Granted" + space(18) +
300                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
301                   space(2) +
302                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
303                   space(2) +
304                   str(@tmp_int, 10) + space(5) +
305                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
306                   @psign
307               print @rptline
308   
309               select @tmp_int = sum(value)
310               from #tempmonitors
311               where group_name = "lock" and
312                   field_name like "waited%_SH_TAB"
313   
314               select @rptline = "    Waited" + space(19) +
315                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
316                   space(2) +
317                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
318                   space(2) +
319                   str(@tmp_int, 10) + space(5) +
320                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
321                   @psign
322               print @rptline
323   
324               print @sum1line
325               select @rptline = "  Total SH-Table Requests" + space(4) +
326                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
327                   space(2) +
328                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
329                   space(2) +
330                   str(@tmp_total, 10) + space(5) +
331                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
332                   @psign
333               print @rptline
334           end
335           print @blankline
336   
337           print "  Exclusive Intent"
338   
339           select @tmp_total = SUM(value)
340           from #tempmonitors
341           where group_name = "lock" and
342               (field_name like "granted%_EX_INT" or
343                   field_name like "waited%_EX_INT")
344   
345           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
346           begin
347               select @rptline = "    Total EX-Intent Requests          0.0           0.0           0       n/a"
348               print @rptline
349           end
350           else
351           begin
352               select @tmp_int = sum(value)
353               from #tempmonitors
354               where group_name = "lock" and
355                   field_name like "granted%_EX_INT"
356   
357               select @rptline = "    Granted" + space(18) +
358                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
359                   space(2) +
360                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
361                   space(2) +
362                   str(@tmp_int, 10) + space(5) +
363                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
364                   @psign
365               print @rptline
366   
367               select @tmp_int = sum(value)
368               from #tempmonitors
369               where group_name = "lock" and
370                   field_name like "waited%_EX_INT"
371   
372               select @rptline = "    Waited" + space(19) +
373                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
374                   space(2) +
375                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
376                   space(2) +
377                   str(@tmp_int, 10) + space(5) +
378                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
379                   @psign
380               print @rptline
381   
382               print @sum1line
383               select @rptline = "  Total EX-Intent Requests" + space(3) +
384                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
385                   space(2) +
386                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
387                   space(2) +
388                   str(@tmp_total, 10) + space(5) +
389                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
390                   @psign
391               print @rptline
392           end
393           print @blankline
394   
395           print "  Shared Intent"
396   
397           select @tmp_total = SUM(value)
398           from #tempmonitors
399           where group_name = "lock" and
400               (field_name like "granted%_SH_INT" or
401                   field_name like "waited%_SH_INT")
402   
403           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
404           begin
405               select @rptline = "    Total SH-Intent Requests          0.0           0.0           0       n/a"
406               print @rptline
407           end
408           else
409           begin
410               select @tmp_int = sum(value)
411               from #tempmonitors
412               where group_name = "lock" and
413                   field_name like "granted%_SH_INT"
414   
415               select @rptline = "    Granted" + space(18) +
416                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
417                   space(2) +
418                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
419                   space(2) +
420                   str(@tmp_int, 10) + space(5) +
421                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
422                   @psign
423               print @rptline
424   
425               select @tmp_int = sum(value)
426               from #tempmonitors
427               where group_name = "lock" and
428                   field_name like "waited%_SH_INT"
429   
430               select @rptline = "    Waited" + space(19) +
431                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
432                   space(2) +
433                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
434                   space(2) +
435                   str(@tmp_int, 10) + space(5) +
436                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
437                   @psign
438               print @rptline
439               print @sum1line
440   
441               select @rptline = "  Total SH-Intent Requests" + space(3) +
442                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
443                   space(2) +
444                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
445                   space(2) +
446                   str(@tmp_total, 10) + space(5) +
447                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
448                   @psign
449               print @rptline
450           end
451           print @blankline
452   
453           print "  Exclusive Partition"
454   
455           select @tmp_total = SUM(value)
456           from #tempmonitors
457           where group_name = "lock" and
458               (field_name like "granted%_EX_PTN" or
459                   field_name like "waited%_EX_PTN")
460   
461           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
462           begin
463               select @rptline = "    Total EX-Partition Reqs           0.0           0.0           0       n/a"
464               print @rptline
465           end
466           else
467           begin
468               select @tmp_int = sum(value)
469               from #tempmonitors
470               where group_name = "lock" and
471                   field_name like "granted%_EX_PTN"
472   
473               select @rptline = "    Granted" + space(18) +
474                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
475                   space(2) +
476                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
477                   space(2) +
478                   str(@tmp_int, 10) + space(5) +
479                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
480                   @psign
481               print @rptline
482   
483               select @tmp_int = sum(value)
484               from #tempmonitors
485               where group_name = "lock" and
486                   field_name like "waited%_EX_PTN"
487   
488               select @rptline = "    Waited" + space(19) +
489                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
490                   space(2) +
491                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
492                   space(2) +
493                   str(@tmp_int, 10) + space(5) +
494                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
495                   @psign
496               print @rptline
497               print @sum1line
498               select @rptline = "  Total EX-Partition Reqs" + space(4) +
499                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
500                   space(2) +
501                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
502                   space(2) +
503                   str(@tmp_total, 10) + space(5) +
504                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
505                   @psign
506               print @rptline
507           end
508           print @blankline
509   
510           print "  Shared Partition"
511   
512           select @tmp_total = SUM(value)
513           from #tempmonitors
514           where group_name = "lock" and
515               (field_name like "granted%_SH_PTN" or field_name like "waited%_SH_PTN")
516   
517           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
518           begin
519               select @rptline = "    Total SH-Partition Reqs           0.0           0.0           0       n/a"
520               print @rptline
521           end
522           else
523           begin
524               select @tmp_int = sum(value)
525               from #tempmonitors
526               where group_name = "lock" and
527                   field_name like "granted%_SH_PTN"
528   
529               select @rptline = "    Granted" + space(18) +
530                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
531                   space(2) +
532                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
533                   space(2) +
534                   str(@tmp_int, 10) + space(5) +
535                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
536                   @psign
537               print @rptline
538   
539               select @tmp_int = sum(value)
540               from #tempmonitors
541               where group_name = "lock" and
542                   field_name like "waited%_SH_PTN"
543   
544               select @rptline = "    Waited" + space(19) +
545                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
546                   space(2) +
547                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
548                   space(2) +
549                   str(@tmp_int, 10) + space(5) +
550                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
551                   @psign
552               print @rptline
553   
554               print @sum1line
555               select @rptline = "  Total SH-Partition Reqs" + space(4) +
556                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
557                   space(2) +
558                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
559                   space(2) +
560                   str(@tmp_total, 10) + space(5) +
561                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
562                   @psign
563               print @rptline
564           end
565           print @blankline
566   
567           print "  Exclusive Partition Intent"
568   
569           select @tmp_total = SUM(value)
570           from #tempmonitors
571           where group_name = "lock" and
572               (field_name like "granted%_EX_INT_PTN" or
573                   field_name like "waited%_EX_INT_PTN")
574   
575           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
576           begin
577               select @rptline = "    Total EX-Ptn_Intent Reqs          0.0           0.0           0       n/a"
578               print @rptline
579           end
580           else
581           begin
582               select @tmp_int = sum(value)
583               from #tempmonitors
584               where group_name = "lock" and
585                   field_name like "granted%_EX_INT_PTN"
586   
587               select @rptline = "    Granted" + space(18) +
588                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
589                   space(2) +
590                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
591                   space(2) +
592                   str(@tmp_int, 10) + space(5) +
593                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
594                   @psign
595               print @rptline
596   
597               select @tmp_int = sum(value)
598               from #tempmonitors
599               where group_name = "lock" and
600                   field_name like "waited%_EX_INT_PTN"
601   
602               select @rptline = "    Waited" + space(19) +
603                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
604                   space(2) +
605                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
606                   space(2) +
607                   str(@tmp_int, 10) + space(5) +
608                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
609                   @psign
610               print @rptline
611   
612               print @sum1line
613               select @rptline = "  Total EX-Ptn_Intent Reqs" + space(3) +
614                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
615                   space(2) +
616                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
617                   space(2) +
618                   str(@tmp_total, 10) + space(5) +
619                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
620                   @psign
621               print @rptline
622           end
623           print @blankline
624   
625           print "  Shared Partition Intent"
626   
627           select @tmp_total = SUM(value)
628           from #tempmonitors
629           where group_name = "lock" and
630               (field_name like "granted%_SH_INT_PTN" or
631                   field_name like "waited%_SH_INT_PTN")
632   
633           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
634           begin
635               select @rptline = "    Total SH-Ptn_Intent Reqs          0.0           0.0           0       n/a"
636               print @rptline
637           end
638           else
639           begin
640               select @tmp_int = sum(value)
641               from #tempmonitors
642               where group_name = "lock" and
643                   field_name like "granted%_SH_INT_PTN"
644   
645               select @rptline = "    Granted" + space(18) +
646                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
647                   space(2) +
648                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
649                   space(2) +
650                   str(@tmp_int, 10) + space(5) +
651                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
652                   @psign
653               print @rptline
654   
655               select @tmp_int = sum(value)
656               from #tempmonitors
657               where group_name = "lock" and
658                   field_name like "waited%_SH_INT_PTN"
659   
660               select @rptline = "    Waited" + space(19) +
661                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
662                   space(2) +
663                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
664                   space(2) +
665                   str(@tmp_int, 10) + space(5) +
666                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
667                   @psign
668               print @rptline
669               print @sum1line
670   
671               select @rptline = "  Total SH-Ptn_Intent Reqs" + space(3) +
672                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
673                   space(2) +
674                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
675                   space(2) +
676                   str(@tmp_total, 10) + space(5) +
677                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
678                   @psign
679               print @rptline
680           end
681           print @blankline
682   
683           print "  Exclusive Covering Partition Intent"
684   
685           select @tmp_total = SUM(value)
686           from #tempmonitors
687           where group_name = "lock" and
688               (field_name like "granted%_EX_CPTN" or
689                   field_name like "waited%_EX_CPTN")
690   
691           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
692           begin
693               select @rptline = "    Total EX-CPtn_Intent Reqs         0.0           0.0           0       n/a"
694               print @rptline
695           end
696           else
697           begin
698               select @tmp_int = sum(value)
699               from #tempmonitors
700               where group_name = "lock" and
701                   field_name like "granted%_EX_CPTN"
702   
703               select @rptline = "    Granted" + space(18) +
704                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
705                   space(2) +
706                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
707                   space(2) +
708                   str(@tmp_int, 10) + space(5) +
709                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
710                   @psign
711               print @rptline
712   
713               select @tmp_int = sum(value)
714               from #tempmonitors
715               where group_name = "lock" and
716                   field_name like "waited%_EX_CPTN"
717   
718               select @rptline = "    Waited" + space(19) +
719                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
720                   space(2) +
721                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
722                   space(2) +
723                   str(@tmp_int, 10) + space(5) +
724                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
725                   @psign
726               print @rptline
727   
728               print @sum1line
729               select @rptline = "  Total EX-CPtn_Intent Reqs" + space(2) +
730                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
731                   space(2) +
732                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
733                   space(2) +
734                   str(@tmp_total, 10) + space(5) +
735                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
736                   @psign
737               print @rptline
738           end
739           print @blankline
740   
741           print "  Shared Covering Partition Intent"
742   
743           select @tmp_total = SUM(value)
744           from #tempmonitors
745           where group_name = "lock" and
746               (field_name like "granted%_SH_CPTN" or
747                   field_name like "waited%_SH_CPTN")
748   
749           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
750           begin
751               select @rptline = "    Total SH-CPtn_Intent Reqs         0.0           0.0           0       n/a"
752               print @rptline
753           end
754           else
755           begin
756               select @tmp_int = sum(value)
757               from #tempmonitors
758               where group_name = "lock" and
759                   field_name like "granted%_SH_CPTN"
760   
761               select @rptline = "    Granted" + space(18) +
762                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
763                   space(2) +
764                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
765                   space(2) +
766                   str(@tmp_int, 10) + space(5) +
767                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
768                   @psign
769               print @rptline
770   
771               select @tmp_int = sum(value)
772               from #tempmonitors
773               where group_name = "lock" and
774                   field_name like "waited%_SH_CPTN"
775   
776               select @rptline = "    Waited" + space(19) +
777                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
778                   space(2) +
779                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
780                   space(2) +
781                   str(@tmp_int, 10) + space(5) +
782                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
783                   @psign
784               print @rptline
785               print @sum1line
786   
787               select @rptline = "  Total SH-CPtn_Intent Reqs" + space(2) +
788                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
789                   space(2) +
790                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
791                   space(2) +
792                   str(@tmp_total, 10) + space(5) +
793                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) +
794                   @psign
795               print @rptline
796           end
797           print @blankline
798   
799           print "  Page & Row Lock HashTable"
800           select @tmp_int = value
801           from #tempmonitors
802           where group_name = "lock" and
803               field_name = "hashtab_lookups_pagerow_lock"
804   
805           select @rptline = "    Lookups" + 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(7) + @na_str
811           print @rptline
812   
813           /* Avoid divide by 0 error */
814           if (@tmp_int > 0)
815           begin
816               select @tmp_int2 = value
817               from #tempmonitors
818               where group_name = "lock" and
819                   field_name = "hashtab_overflow_pagerow_lock"
820   
821               select @rptline = "    Avg Chain Length" + space(18) +
822                   @na_str + space(11) +
823                   @na_str + space(2) +
824                   str(convert(real, @tmp_int2) / @tmp_int, 10, 5) +
825                   space(7) + @na_str
826                   , @reco_avg_chain_length_pagerow = convert(real, @tmp_int2) / @tmp_int
827               print @rptline
828           end
829           else
830           begin
831               select @reco_avg_chain_length_pagerow = 0
832           end
833   
834           select @spinlock_contention = isnull(100.0 * (sum(convert(float, waits)) / sum(convert(float, grabs))), 0)
835           from #foo
836           where name = 'fglockspins'
837               and grabs > 0
838   
839           select @rptline = "    Spinlock Contention" + space(15) +
840               @na_str + space(11) +
841               @na_str + space(9) +
842               @na_str + space(5) +
843               str(@spinlock_contention, 5, 1)
844               + @psign
845               , @reco_fgspin_contention = @spinlock_contention
846           print @rptline
847           print @blankline
848   
849           print "  Exclusive Page"
850   
851           select @tmp_total = SUM(value)
852           from #tempmonitors
853           where group_name = "lock" and
854               (field_name like "granted%_EX_PAGE" or
855                   field_name like "waited%_EX_PAGE")
856   
857           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
858           begin
859               select @rptline = "    Total EX-Page Requests            0.0           0.0           0       n/a"
860               print @rptline
861           end
862           else
863           begin
864   
865               select @tmp_int = sum(value)
866               from #tempmonitors
867               where group_name = "lock" and
868                   field_name like "granted%_EX_PAGE"
869   
870               select @rptline = "    Granted" + space(18) +
871                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
872                   space(2) +
873                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
874                   space(2) +
875                   str(@tmp_int, 10) + space(5) +
876                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
877                   @psign
878               print @rptline
879   
880               select @tmp_int = sum(value)
881               from #tempmonitors
882               where group_name = "lock" and
883                   field_name like "waited%_EX_PAGE"
884   
885               select @rptline = "    Waited" + space(19) +
886                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
887                   space(2) +
888                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
889                   space(2) +
890                   str(@tmp_int, 10) + space(5) +
891                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
892               print @rptline
893   
894               print @sum1line
895               select @rptline = "  Total EX-Page Requests" + space(5) +
896                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
897                   space(2) +
898                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
899                   space(2) +
900                   str(@tmp_total, 10) + space(5) +
901                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
902               print @rptline
903           end
904           print @blankline
905   
906           print "  Update Page"
907   
908           select @tmp_total = SUM(value)
909           from #tempmonitors
910           where group_name = "lock" and
911               (field_name like "granted%_UP_PAGE" or
912                   field_name like "waited%_UP_PAGE")
913   
914           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
915           begin
916               select @rptline = "    Total UP-Page Requests            0.0           0.0           0       n/a"
917               print @rptline
918           end
919           else
920           begin
921   
922               select @tmp_int = sum(value)
923               from #tempmonitors
924               where group_name = "lock" and
925                   field_name like "granted%_UP_PAGE"
926   
927               select @rptline = "    Granted" + space(18) +
928                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
929                   space(2) +
930                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
931                   space(2) +
932                   str(@tmp_int, 10) + space(5) +
933                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
934               print @rptline
935   
936               select @tmp_int = sum(value)
937               from #tempmonitors
938               where group_name = "lock" and
939                   field_name like "waited%_UP_PAGE"
940   
941               select @rptline = "    Waited" + space(19) +
942                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
943                   space(2) +
944                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
945                   space(2) +
946                   str(@tmp_int, 10) + space(5) +
947                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
948               print @rptline
949   
950               print @sum1line
951               select @rptline = "  Total UP-Page Requests" + space(5) +
952                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
953                   space(2) +
954                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
955                   space(2) +
956                   str(@tmp_total, 10) + space(5) +
957                   str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
958               print @rptline
959           end
960           print @blankline
961   
962           print "  Shared Page"
963   
964           select @tmp_total = SUM(value)
965           from #tempmonitors
966           where group_name = "lock" and
967               (field_name like "granted%_SH_PAGE" or
968                   field_name like "waited%_SH_PAGE")
969   
970           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
971           begin
972               select @rptline = "    Total SH-Page Requests            0.0           0.0           0       n/a"
973               print @rptline
974           end
975           else
976           begin
977   
978               select @tmp_int = sum(value)
979               from #tempmonitors
980               where group_name = "lock" and
981                   field_name like "granted%_SH_PAGE"
982   
983               select @rptline = "    Granted" + space(18) +
984                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
985                   space(2) +
986                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
987                   space(2) +
988                   str(@tmp_int, 10) + space(5) +
989                   str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
990               print @rptline
991   
992               select @tmp_int = sum(value)
993               from #tempmonitors
994               where group_name = "lock" and
995                   field_name like "waited%_SH_PAGE"
996   
997               select @rptline = "    Waited" + space(19) +
998                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
999                   space(2) +
1000                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1001                  space(2) +
1002                  str(@tmp_int, 10) + space(5) +
1003                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1004              print @rptline
1005  
1006              print @sum1line
1007              select @rptline = "  Total SH-Page Requests" + space(5) +
1008                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1009                  space(2) +
1010                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1011                  space(2) +
1012                  str(@tmp_total, 10) + space(5) +
1013  
1014                  str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
1015              print @rptline
1016              print @blankline
1017          end
1018          print @blankline
1019  
1020          print "  Exclusive Row"
1021  
1022          select @tmp_total = SUM(value)
1023          from #tempmonitors
1024          where group_name = "lock" and
1025              (field_name like "granted%_EX_ROW" or
1026                  field_name like "waited%_EX_ROW")
1027  
1028          if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1029          begin
1030              select @rptline = "    Total EX-Row Requests             0.0           0.0           0       n/a"
1031              print @rptline
1032          end
1033          else
1034          begin
1035  
1036              select @tmp_int = sum(value)
1037              from #tempmonitors
1038              where group_name = "lock" and
1039                  field_name like "granted%_EX_ROW"
1040  
1041              select @rptline = "    Granted" + space(18) +
1042                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1043                  space(2) +
1044                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1045                  space(2) +
1046                  str(@tmp_int, 10) + space(5) +
1047                  str(100.0 * @tmp_int / @tmp_total, 5, 1) +
1048                  @psign
1049              print @rptline
1050  
1051              select @tmp_int = sum(value)
1052              from #tempmonitors
1053              where group_name = "lock" and
1054                  field_name like "waited%_EX_ROW"
1055  
1056              select @rptline = "    Waited" + space(19) +
1057                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1058                  space(2) +
1059                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1060                  space(2) +
1061                  str(@tmp_int, 10) + space(5) +
1062                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1063              print @rptline
1064  
1065              print @sum1line
1066              select @rptline = "  Total EX-Row Requests" + space(6) +
1067                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1068                  space(2) +
1069                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1070                  space(2) +
1071                  str(@tmp_total, 10) + space(5) +
1072                  str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
1073              print @rptline
1074          end
1075          print @blankline
1076  
1077          print "  Update Row"
1078  
1079          select @tmp_total = SUM(value)
1080          from #tempmonitors
1081          where group_name = "lock" and
1082              (field_name like "granted%_UP_ROW" or
1083                  field_name like "waited%_UP_ROW")
1084  
1085          if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1086          begin
1087              select @rptline = "    Total UP-Row Requests             0.0           0.0           0       n/a"
1088              print @rptline
1089          end
1090          else
1091          begin
1092  
1093              select @tmp_int = sum(value)
1094              from #tempmonitors
1095              where group_name = "lock" and
1096                  field_name like "granted%_UP_ROW"
1097  
1098              select @rptline = "    Granted" + space(18) +
1099                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1100                  space(2) +
1101                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1102                  space(2) +
1103                  str(@tmp_int, 10) + space(5) +
1104                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1105              print @rptline
1106  
1107              select @tmp_int = sum(value)
1108              from #tempmonitors
1109              where group_name = "lock" and
1110                  field_name like "waited%_UP_ROW"
1111  
1112              select @rptline = "    Waited" + space(19) +
1113                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1114                  space(2) +
1115                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1116                  space(2) +
1117                  str(@tmp_int, 10) + space(5) +
1118                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1119              print @rptline
1120  
1121              print @sum1line
1122              select @rptline = "  Total UP-Row Requests" + space(6) +
1123                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1124                  space(2) +
1125                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1126                  space(2) +
1127                  str(@tmp_total, 10) + space(5) +
1128                  str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
1129              print @rptline
1130          end
1131          print @blankline
1132  
1133          print "  Shared Row"
1134  
1135          select @tmp_total = SUM(value)
1136          from #tempmonitors
1137          where group_name = "lock" and
1138              (field_name like "granted%_SH_ROW" or
1139                  field_name like "waited%_SH_ROW")
1140  
1141          if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1142          begin
1143              select @rptline = "    Total SH-Row Requests             0.0           0.0           0       n/a"
1144              print @rptline
1145          end
1146          else
1147          begin
1148  
1149              select @tmp_int = sum(value)
1150              from #tempmonitors
1151              where group_name = "lock" and
1152                  field_name like "granted%_SH_ROW"
1153  
1154              select @rptline = "    Granted" + space(18) +
1155                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1156                  space(2) +
1157                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1158                  space(2) +
1159                  str(@tmp_int, 10) + space(5) +
1160                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1161              print @rptline
1162  
1163              select @tmp_int = sum(value)
1164              from #tempmonitors
1165              where group_name = "lock" and
1166                  field_name like "waited%_SH_ROW"
1167  
1168              select @rptline = "    Waited" + space(19) +
1169                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1170                  space(2) +
1171                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1172                  space(2) +
1173                  str(@tmp_int, 10) + space(5) +
1174                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1175              print @rptline
1176  
1177              print @sum1line
1178              select @rptline = "  Total SH-Row Requests" + space(6) +
1179                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1180                  space(2) +
1181                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1182                  space(2) +
1183                  str(@tmp_total, 10) + space(5) +
1184                  str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
1185              print @rptline
1186              print @blankline
1187          end
1188          print @blankline
1189  
1190          print "  Next-Key"
1191  
1192          select @tmp_total = SUM(value)
1193          from #tempmonitors
1194          where group_name = "lock" and
1195              (field_name like "granted%_SH_NKL" or
1196                  field_name like "waited%_SH_NKL")
1197  
1198          if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1199          begin
1200              select @rptline = "    Total Next-Key Requests           0.0           0.0           0       n/a"
1201              print @rptline
1202          end
1203          else
1204          begin
1205  
1206              select @tmp_int = sum(value)
1207              from #tempmonitors
1208              where group_name = "lock" and
1209                  field_name like "granted%_SH_NKL"
1210  
1211              select @rptline = "    Granted" + space(18) +
1212                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1213                  space(2) +
1214                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1215                  space(2) +
1216                  str(@tmp_int, 10) + space(5) +
1217                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1218              print @rptline
1219  
1220              select @tmp_int = sum(value)
1221              from #tempmonitors
1222              where group_name = "lock" and
1223                  field_name like "waited%_SH_NKL"
1224  
1225              select @rptline = "    Waited" + space(19) +
1226                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1227                  space(2) +
1228                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1229                  space(2) +
1230                  str(@tmp_int, 10) + space(5) +
1231                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1232              print @rptline
1233  
1234              print @sum1line
1235              select @rptline = "  Total Next-Key Requests" + space(4) +
1236                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1237                  space(2) +
1238                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1239                  space(2) +
1240                  str(@tmp_total, 10) + space(5) +
1241                  str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
1242              print @rptline
1243              print @blankline
1244          end
1245          print @blankline
1246  
1247          print "  Address Lock Hashtable"
1248          select @tmp_int = value
1249          from #tempmonitors
1250          where group_name = "lock" and
1251              field_name = "hashtab_lookups_address_lock"
1252  
1253          select @rptline = "    Lookups" + space(18) +
1254              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1255              space(2) +
1256              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1257              space(2) +
1258              str(@tmp_int, 10) + space(7) +
1259              @na_str
1260          print @rptline
1261  
1262          /* Avoid divide by 0 error */
1263          if (@tmp_int > 0)
1264          begin
1265              select @tmp_int2 = value
1266              from #tempmonitors
1267              where group_name = "lock" and
1268                  field_name = "hashtab_overflow_address_lock"
1269  
1270              select @rptline = "    Avg Chain Length" + space(18) +
1271                  @na_str + space(11) +
1272                  @na_str + space(2) +
1273                  str(convert(real, @tmp_int2) / @tmp_int, 10, 5) +
1274                  space(7) + @na_str
1275              print @rptline
1276          end
1277  
1278          select @spinlock_contention = isnull(100.0 * (sum(convert(float, waits)) / sum(convert(float, grabs))), 0)
1279          from #foo
1280          where name = 'addrlockspins'
1281              and grabs > 0
1282  
1283          select @rptline = "    Spinlock Contention" + space(15) +
1284              @na_str + space(11) +
1285              @na_str + space(9) +
1286              @na_str + space(5) +
1287              str(@spinlock_contention, 5, 1)
1288              + @psign
1289              , @reco_addrspin_contention = @spinlock_contention
1290          print @rptline
1291          print @blankline
1292  
1293          print "  Exclusive Address"
1294  
1295          select @tmp_total = SUM(value)
1296          from #tempmonitors
1297          where group_name = "lock" and
1298              (field_name like "granted_%EX_ADDR" or
1299                  field_name like "waited_%EX_ADDR")
1300  
1301          if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1302          begin
1303              select @rptline = "    Total EX-Address Requests         0.0           0.0           0       n/a"
1304              print @rptline
1305          end
1306          else
1307          begin
1308  
1309              select @tmp_int = sum(value)
1310              from #tempmonitors
1311              where group_name = "lock" and
1312                  field_name like "granted_%EX_ADDR"
1313  
1314              select @rptline = "    Granted" + space(18) +
1315                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1316                  space(2) +
1317                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1318                  space(2) +
1319                  str(@tmp_int, 10) + space(5) +
1320                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1321              print @rptline
1322  
1323              select @tmp_int = sum(value)
1324              from #tempmonitors
1325              where group_name = "lock" and
1326                  field_name like "waited_%EX_ADDR"
1327  
1328              select @rptline = "    Waited" + space(19) +
1329                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1330                  space(2) +
1331                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1332                  space(2) +
1333                  str(@tmp_int, 10) + space(5) +
1334                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1335              print @rptline
1336  
1337              print @sum1line
1338              select @rptline = "  Total EX-Address Requests  " +
1339                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1340                  space(2) +
1341                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1342                  space(2) +
1343                  str(@tmp_total, 10) + space(5) +
1344                  str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
1345              print @rptline
1346  
1347          end
1348          print @blankline
1349  
1350          print "  Shared Address"
1351  
1352          select @tmp_total = SUM(value)
1353          from #tempmonitors
1354          where group_name = "lock" and
1355              (field_name like "granted_%SH_ADDR" or
1356                  field_name like "waited_%SH_ADDR")
1357  
1358          if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1359          begin
1360              select @rptline = "    Total SH-Address Requests         0.0           0.0           0       n/a"
1361              print @rptline
1362          end
1363          else
1364          begin
1365  
1366              select @tmp_int = sum(value)
1367              from #tempmonitors
1368              where group_name = "lock" and
1369                  field_name like "granted_%SH_ADDR"
1370  
1371              select @rptline = "    Granted" + space(18) +
1372                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1373                  space(2) +
1374                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1375                  space(2) +
1376                  str(@tmp_int, 10) + space(5) +
1377                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1378              print @rptline
1379  
1380              select @tmp_int = sum(value)
1381              from #tempmonitors
1382              where group_name = "lock" and
1383                  field_name like "waited_%SH_ADDR"
1384  
1385              select @rptline = "    Waited" + space(19) +
1386                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1387                  space(2) +
1388                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1389                  space(2) +
1390                  str(@tmp_int, 10) + space(5) +
1391                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1392              print @rptline
1393  
1394              print @sum1line
1395              select @rptline = "  Total SH-Address Requests  " +
1396                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1397                  space(2) +
1398                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1399                  space(2) +
1400                  str(@tmp_total, 10) + space(5) +
1401                  str(100.0 * @tmp_total / @TotalLocks, 5, 1) + @psign
1402              print @rptline
1403  
1404              print @blankline
1405          end
1406          print @blankline
1407  
1408          print "  Last Page Locks on Heaps"
1409  
1410          select @tmp_total = SUM(value)
1411          from #tempmonitors
1412          where group_name = "access" and
1413              field_name = "locklastpage"
1414  
1415          if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1416          begin
1417              select @rptline = "    Total Last Pg Locks               0.0           0.0           0       n/a"
1418              print @rptline
1419          end
1420          else
1421          begin
1422  
1423              select @tmp_int2 = value
1424              from #tempmonitors
1425              where group_name = "access" and
1426                  field_name = "lklastpg_wait"
1427  
1428              select @tmp_int = @tmp_total - @tmp_int2
1429  
1430              select @rptline = "    Granted" + space(18) +
1431                  str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1432                  space(2) +
1433                  str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1434                  space(2) +
1435                  str(@tmp_int, 10) + space(5) +
1436                  str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1437              print @rptline
1438  
1439              select @reco_lastpg_wait_percent = convert(int,
1440                  100.0 * ((1.0 * @tmp_int2) / @tmp_total))
1441  
1442              select @rptline = "    Waited" + space(19) +
1443                  str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
1444                  space(2) +
1445                  str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
1446                  space(2) +
1447                  str(@tmp_int2, 10) + space(5) +
1448                  str(100.0 * @tmp_int2 / @tmp_total, 5, 1) + @psign
1449              print @rptline
1450              print @sum1line
1451              select @rptline = "  Total Last Pg Locks" + space(8) +
1452                  str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1453                  space(2) +
1454                  str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1455                  space(2) +
1456                  str(@tmp_total, 10) + space(5) +
1457                  str(100.0 * @tmp_total / @tmp_total, 5, 1) + @psign
1458              print @rptline
1459              print @blankline
1460          end
1461          print @blankline
1462      end
1463  
1464  
1465      print "  Deadlocks by Lock Type          per sec      per xact       count  %% of total"
1466      print @sum1line
1467  
1468      select @tmp_total = value
1469      from #tempmonitors
1470      where group_name = "lock" and
1471          field_name = "deadlocks"
1472  
1473      select @totaldeadlocks = sum(value)
1474      from #tempmonitors
1475      where group_name like "buffer_%" and
1476          field_name like "physical_lock_deadlock"
1477  
1478      select @tmp_access = SUM(value)
1479      from #tempmonitors
1480      where group_name = "access" and
1481          field_name in ("getrootpg_deadlock",
1482              "lkindex_TSCK_deadlock",
1483              "lkindex_deadlock",
1484              "lkindDS_P_deadlock",
1485              "lkindDS_C_deadlock",
1486              "split_index_deadlock",
1487              "log_lock_deadlock")
1488  
1489      select @tmp_xls = value
1490      from #tempmonitors
1491      where group_name = "xls" and
1492          field_name = "log_lock_deadlock"
1493  
1494      select @tmp_total = @tmp_total + @totaldeadlocks + @tmp_access + @tmp_xls
1495  
1496      if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1497      begin
1498          select @rptline = "  Total Deadlocks                     0.0           0.0           0       n/a"
1499          print @rptline
1500      end
1501      else
1502      begin
1503  
1504          select @tmp_int = value
1505          from #tempmonitors
1506          where group_name = "lock" and
1507              field_name = "deadlock_EX_TAB"
1508  
1509          select @rptline = "    Exclusive Table" + space(10) +
1510              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1511              space(2) +
1512              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1513              space(2) +
1514              str(@tmp_int, 10) + space(5) +
1515              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1516          print @rptline
1517  
1518          select @tmp_int = value
1519          from #tempmonitors
1520          where group_name = "lock" and
1521              field_name = "deadlock_SH_TAB"
1522  
1523          select @rptline = "    Shared Table" + space(13) +
1524              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1525              space(2) +
1526              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1527              space(2) +
1528              str(@tmp_int, 10) + space(5) +
1529              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1530          print @rptline
1531  
1532          select @tmp_int = value
1533          from #tempmonitors
1534          where group_name = "lock" and
1535              field_name = "deadlock_EX_INT"
1536  
1537          select @rptline = "    Exclusive Intent" + space(9) +
1538              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1539              space(2) +
1540              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1541              space(2) +
1542              str(@tmp_int, 10) + space(5) +
1543              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1544          print @rptline
1545  
1546          select @tmp_int = value
1547          from #tempmonitors
1548          where group_name = "lock" and
1549              field_name = "deadlock_SH_INT"
1550  
1551          select @rptline = "    Shared Intent" + space(12) +
1552              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1553              space(2) +
1554              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1555              space(2) +
1556              str(@tmp_int, 10) + space(5) +
1557              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1558          print @rptline
1559  
1560          select @tmp_int = value
1561          from #tempmonitors
1562          where group_name = "lock" and
1563              field_name = "deadlock_EX_PTN"
1564  
1565          select @rptline = "    Exclusive Partition" + space(6) +
1566              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1567              space(2) +
1568              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1569              space(2) +
1570              str(@tmp_int, 10) + space(5) +
1571              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1572          print @rptline
1573  
1574          select @tmp_int = value
1575          from #tempmonitors
1576          where group_name = "lock" and
1577              field_name = "deadlock_SH_PTN"
1578  
1579          select @rptline = "    Shared Partition" + space(9) +
1580              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1581              space(2) +
1582              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1583              space(2) +
1584              str(@tmp_int, 10) + space(5) +
1585              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1586          print @rptline
1587  
1588          select @tmp_int = value
1589          from #tempmonitors
1590          where group_name = "lock" and
1591              field_name = "deadlock_EX_INT_PTN"
1592  
1593          select @rptline = "    Exclusive Ptn_Intent" + space(5) +
1594              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1595              space(2) +
1596              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1597              space(2) +
1598              str(@tmp_int, 10) + space(5) +
1599              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1600          print @rptline
1601  
1602          select @tmp_int = value
1603          from #tempmonitors
1604          where group_name = "lock" and
1605              field_name = "deadlock_SH_INT_PTN"
1606  
1607          select @rptline = "    Shared Ptn_Intent" + space(8) +
1608              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1609              space(2) +
1610              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1611              space(2) +
1612              str(@tmp_int, 10) + space(5) +
1613              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1614          print @rptline
1615  
1616          select @tmp_int = value
1617          from #tempmonitors
1618          where group_name = "lock" and
1619              field_name = "deadlock_SH_CPTN"
1620  
1621          select @rptline = "    Shared CPtn_Intent" + space(7) +
1622              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1623              space(2) +
1624              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1625              space(2) +
1626              str(@tmp_int, 10) + space(5) +
1627              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1628          print @rptline
1629  
1630  
1631          select @tmp_int = value
1632          from #tempmonitors
1633          where group_name = "lock" and
1634              field_name = "deadlock_EX_CPTN"
1635  
1636          select @rptline = "    Exclusive CPtn_Intent" + space(4) +
1637              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1638              space(2) +
1639              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1640              space(2) +
1641              str(@tmp_int, 10) + space(5) +
1642              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1643          print @rptline
1644  
1645          select @tmp_int = value
1646          from #tempmonitors
1647          where group_name = "lock" and
1648              field_name = "deadlock_EX_PAGE"
1649  
1650          select @rptline = "    Exclusive Page" + space(11) +
1651              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1652              space(2) +
1653              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1654              space(2) +
1655              str(@tmp_int, 10) + space(5) +
1656              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1657          print @rptline
1658  
1659          select @tmp_int = value
1660          from #tempmonitors
1661          where group_name = "lock" and
1662              field_name = "deadlock_UP_PAGE"
1663  
1664          select @rptline = "    Update Page" + space(14) +
1665              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1666              space(2) +
1667              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1668              space(2) +
1669              str(@tmp_int, 10) + space(5) +
1670              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1671          print @rptline
1672  
1673          select @tmp_int = value
1674          from #tempmonitors
1675          where group_name = "lock" and
1676              field_name = "deadlock_SH_PAGE"
1677  
1678          select @rptline = "    Shared Page" + space(14) +
1679              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1680              space(2) +
1681              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1682              space(2) +
1683              str(@tmp_int, 10) + space(5) +
1684              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1685          print @rptline
1686  
1687          select @tmp_int = value
1688          from #tempmonitors
1689          where group_name = "lock" and
1690              field_name = "deadlock_EX_ROW"
1691  
1692          select @rptline = "    Exclusive Row" + space(12) +
1693              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1694              space(2) +
1695              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1696              space(2) +
1697              str(@tmp_int, 10) + space(5) +
1698              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1699          print @rptline
1700  
1701          select @tmp_int = value
1702          from #tempmonitors
1703          where group_name = "lock" and
1704              field_name = "deadlock_UP_ROW"
1705  
1706          select @rptline = "    Update Row" + space(15) +
1707              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1708              space(2) +
1709              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1710              space(2) +
1711              str(@tmp_int, 10) + space(5) +
1712              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1713          print @rptline
1714  
1715          select @tmp_int = value
1716          from #tempmonitors
1717          where group_name = "lock" and
1718              field_name = "deadlock_SH_ROW"
1719  
1720          select @rptline = "    Shared Row" + space(15) +
1721              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1722              space(2) +
1723              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1724              space(2) +
1725              str(@tmp_int, 10) + space(5) +
1726              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1727          print @rptline
1728  
1729          select @tmp_int = value
1730          from #tempmonitors
1731          where group_name = "lock" and
1732              field_name = "deadlock_SH_NKL"
1733  
1734          select @rptline = "    Shared Next-Key" + space(10) +
1735              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1736              space(2) +
1737              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1738              space(2) +
1739              str(@tmp_int, 10) + space(5) +
1740              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1741          print @rptline
1742  
1743  
1744          select @tmp_int = value
1745          from #tempmonitors
1746          where group_name = "lock" and
1747              field_name = "deadlock_EX_ADDR"
1748  
1749          select @rptline = "    Exclusive Address" + space(8) +
1750              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1751              space(2) +
1752              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1753              space(2) +
1754              str(@tmp_int, 10) + space(5) +
1755              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1756          print @rptline
1757  
1758          select @tmp_int = value
1759          from #tempmonitors
1760          where group_name = "lock" and
1761              field_name = "deadlock_SH_ADDR"
1762  
1763          select @rptline = "    Shared Address" + space(11) +
1764              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1765              space(2) +
1766              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1767              space(2) +
1768              str(@tmp_int, 10) + space(5) +
1769              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1770          print @rptline
1771  
1772          /* Get the deadlocks for physical lock acquisition */
1773          select @rptline = "    Physical Locks      " + space(5) +
1774              str(@totaldeadlocks / @NumElapsedSec, 12, 1) +
1775              space(2) +
1776              str(@totaldeadlocks / convert(real, @NumXacts), 12, 1) +
1777              space(2) +
1778              str(@totaldeadlocks, 10) + space(5) +
1779              str(100.0 * @totaldeadlocks / @tmp_total, 5, 1) + @psign
1780          print @rptline
1781  
1782          /* Get the deadlocks for "access" group */
1783          select @tmp_access = SUM(value)
1784          from #tempmonitors
1785          where group_name = "access" and
1786              field_name in ("getrootpg_deadlock",
1787                  "lkindex_TSCK_deadlock",
1788                  "lkindex_deadlock",
1789                  "lkindDS_P_deadlock",
1790                  "lkindDS_C_deadlock",
1791                  "split_index_deadlock",
1792                  "log_lock_deadlock")
1793  
1794          /* Get the deadlocks for "xls" group */
1795          select @tmp_xls = value
1796          from #tempmonitors
1797          where group_name = "xls" and
1798              field_name = "log_lock_deadlock"
1799  
1800          /* Get the sum of the deadlocks(other than belonging to group locks) */
1801          select @tmp_int = @tmp_access + @tmp_xls
1802  
1803          select @rptline = "    Others" + space(19) +
1804              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1805              space(2) +
1806              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1807              space(2) +
1808              str(@tmp_int, 10) + space(5) +
1809              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1810          print @rptline
1811  
1812  
1813          print @sum2line
1814          select @rptline = "  Total Deadlocks" + space(12) +
1815              str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1816              space(2) +
1817              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1818              space(2) +
1819              str(@tmp_total, 10)
1820          print @rptline
1821      end
1822  
1823      print @blankline
1824      select @tmp_total = value
1825      from #tempmonitors
1826      where group_name = "locks" and
1827          field_name = "clm_deadlocks"
1828  
1829      if @tmp_total != 0 /* Avoid Divide by Zero Errors */
1830      begin
1831  
1832          select @rptline = "  Total Cluster Deadlocks" + space(4) +
1833              str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1834              space(2) +
1835              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1836              space(2) +
1837              str(@tmp_total, 10)
1838          print @rptline
1839      end
1840  
1841      print @blankline
1842      print @blankline
1843  
1844      select @tmp_int2 = @tmp_total /* save for deadlock search calc */
1845  
1846      print "  Deadlock Detection"
1847  
1848      select @tmp_total = value
1849      from #tempmonitors
1850      where group_name = "lock" and
1851          field_name = "deadlock_search"
1852  
1853      select @rptline = "    Deadlock Searches" + space(8) +
1854          str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1855          space(2) +
1856          str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1857          space(2) +
1858          str(@tmp_total, 10) + space(7) +
1859          @na_str
1860      print @rptline
1861  
1862      if @tmp_total != 0 /* Avoid Divide by Zero Errors */
1863      begin
1864  
1865          select @tmp_int = value
1866          from #tempmonitors
1867          where group_name = "lock" and
1868              field_name = "deadlock_alarm_skipped"
1869  
1870          select @rptline = "    Searches Skipped" + space(9) +
1871              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1872              space(2) +
1873              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1874              space(2) +
1875              str(@tmp_int, 10) + space(5) +
1876              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1877          print @rptline
1878  
1879          select @rptline = "    Avg Deadlocks per Search" + space(10) +
1880              @na_str +
1881              space(11) +
1882              @na_str +
1883              space(2) +
1884              str(convert(real, @tmp_int2) / @tmp_total, 10, 5) +
1885              space(7) +
1886              @na_str
1887          print @rptline
1888      end
1889      print @blankline
1890      print @blankline
1891  
1892      print "  Lock Promotions"
1893  
1894      select @tmp_total = SUM(value)
1895      from #tempmonitors
1896      where group_name = "lock" and
1897          (field_name like "promoted_%_PAGE"
1898              or field_name like "promoted_%_ROW"
1899              or field_name like "promoted_%_NKL")
1900  
1901      if @tmp_total = 0 /* Avoid Divide by Zero Errors */
1902      begin
1903          select @rptline = "    Total Lock Promotions             0.0           0.0           0       n/a"
1904          print @rptline
1905      end
1906      else
1907      begin
1908  
1909          select @tmp_int = SUM(value)
1910          from #tempmonitors
1911          where group_name = "lock" and
1912              field_name = "promoted_EX_PAGE"
1913  
1914          select @rptline = "    Ex-Page to Ex-Table" + space(6) +
1915              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1916              space(2) +
1917              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1918              space(2) +
1919              str(@tmp_int, 10) + space(5) +
1920              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1921          print @rptline
1922  
1923          select @tmp_int = SUM(value)
1924          from #tempmonitors
1925          where group_name = "lock" and
1926              field_name = "promoted_SH_PAGE"
1927  
1928          select @rptline = "    Sh-Page to Sh-Table" + space(6) +
1929              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1930              space(2) +
1931              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1932              space(2) +
1933              str(@tmp_int, 10) + space(5) +
1934              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1935          print @rptline
1936  
1937          select @tmp_int = SUM(value)
1938          from #tempmonitors
1939          where group_name = "lock" and
1940              field_name = "promoted_EX_ROW"
1941  
1942          select @rptline = "    Ex-Row to Ex-Table" + space(7) +
1943              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1944              space(2) +
1945              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1946              space(2) +
1947              str(@tmp_int, 10) + space(5) +
1948              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1949          print @rptline
1950  
1951          select @tmp_int = SUM(value)
1952          from #tempmonitors
1953          where group_name = "lock" and
1954              field_name = "promoted_SH_ROW"
1955  
1956          select @rptline = "    Sh-Row to Sh-Table" + space(7) +
1957              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1958              space(2) +
1959              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1960              space(2) +
1961              str(@tmp_int, 10) + space(5) +
1962              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1963          print @rptline
1964  
1965          select @tmp_int = SUM(value)
1966          from #tempmonitors
1967          where group_name = "lock" and
1968              field_name = "promoted_SH_NKL"
1969  
1970          select @rptline = "    Sh-Next-Key to Sh-Table" + space(2) +
1971              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
1972              space(2) +
1973              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
1974              space(2) +
1975              str(@tmp_int, 10) + space(5) +
1976              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
1977          print @rptline
1978  
1979          print @sum2line
1980          select @rptline = "  Total Lock Promotions" + space(6) +
1981              str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
1982              space(2) +
1983              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
1984              space(2) +
1985              str(@tmp_total, 10)
1986          print @rptline
1987      end
1988      print @blankline
1989      print @blankline
1990  
1991      print "  Lock Timeouts by Lock Type      per sec      per xact       count  %% of total"
1992      print @sum1line
1993  
1994      select @tmp_total = sum(value)
1995      from #tempmonitors
1996      where group_name = "lock" and
1997          field_name like "timedout%"
1998  
1999      if @tmp_total = 0 /* Avoid Divide by Zero Errors */
2000      begin
2001          select @rptline = "  Total Timeouts                      0.0           0.0           0       n/a"
2002          print @rptline
2003      end
2004      else
2005      begin
2006  
2007          select @tmp_int = value
2008          from #tempmonitors
2009          where group_name = "lock" and
2010              field_name = "timedout_EX_TAB"
2011  
2012          select @rptline = "    Exclusive Table" + space(10) +
2013              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2014              space(2) +
2015              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2016              space(2) +
2017              str(@tmp_int, 10) + space(5) +
2018              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2019          print @rptline
2020  
2021          select @tmp_int = value
2022          from #tempmonitors
2023          where group_name = "lock" and
2024              field_name = "timedout_SH_TAB"
2025  
2026          select @rptline = "    Shared Table" + space(13) +
2027              str(@tmp_int / (@NumElapsedMs / 1000.0), 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 = value
2036          from #tempmonitors
2037          where group_name = "lock" and
2038              field_name = "timedout_EX_INT"
2039  
2040          select @rptline = "    Exclusive Intent" + space(9) +
2041              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2042              space(2) +
2043              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2044              space(2) +
2045              str(@tmp_int, 10) + space(5) +
2046              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2047          print @rptline
2048  
2049          select @tmp_int = value
2050          from #tempmonitors
2051          where group_name = "lock" and
2052              field_name = "timedout_SH_INT"
2053  
2054          select @rptline = "    Shared Intent" + space(12) +
2055              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2056              space(2) +
2057              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2058              space(2) +
2059              str(@tmp_int, 10) + space(5) +
2060              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2061          print @rptline
2062  
2063          select @tmp_int = value
2064          from #tempmonitors
2065          where group_name = "lock" and
2066              field_name = "timedout_EX_PAGE"
2067  
2068          select @rptline = "    Exclusive Page" + space(11) +
2069              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2070              space(2) +
2071              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2072              space(2) +
2073              str(@tmp_int, 10) + space(5) +
2074              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2075          print @rptline
2076  
2077          select @tmp_int = value
2078          from #tempmonitors
2079          where group_name = "lock" and
2080              field_name = "timedout_UP_PAGE"
2081  
2082          select @rptline = "    Update Page" + space(14) +
2083              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2084              space(2) +
2085              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2086              space(2) +
2087              str(@tmp_int, 10) + space(5) +
2088              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2089          print @rptline
2090  
2091          select @tmp_int = value
2092          from #tempmonitors
2093          where group_name = "lock" and
2094              field_name = "timedout_SH_PAGE"
2095  
2096          select @rptline = "    Shared Page" + space(14) +
2097              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2098              space(2) +
2099              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2100              space(2) +
2101              str(@tmp_int, 10) + space(5) +
2102              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2103          print @rptline
2104  
2105          select @tmp_int = value
2106          from #tempmonitors
2107          where group_name = "lock" and
2108              field_name = "timedout_EX_ROW"
2109  
2110          select @rptline = "    Exclusive Row" + space(12) +
2111              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2112              space(2) +
2113              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2114              space(2) +
2115              str(@tmp_int, 10) + space(5) +
2116              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2117          print @rptline
2118  
2119          select @tmp_int = value
2120          from #tempmonitors
2121          where group_name = "lock" and
2122              field_name = "timedout_UP_ROW"
2123  
2124          select @rptline = "    Update Row" + space(15) +
2125              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2126              space(2) +
2127              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2128              space(2) +
2129              str(@tmp_int, 10) + space(5) +
2130              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2131          print @rptline
2132  
2133          select @tmp_int = value
2134          from #tempmonitors
2135          where group_name = "lock" and
2136              field_name = "timedout_SH_ROW"
2137  
2138          select @rptline = "    Shared Row" + space(15) +
2139              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2140              space(2) +
2141              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2142              space(2) +
2143              str(@tmp_int, 10) + space(5) +
2144              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2145          print @rptline
2146  
2147          select @tmp_int = value
2148          from #tempmonitors
2149          where group_name = "lock" and
2150              field_name = "timedout_EX_ADDR"
2151  
2152          select @rptline = "    Exclusive Address" + space(8) +
2153              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2154              space(2) +
2155              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2156              space(2) +
2157              str(@tmp_int, 10) + space(5) +
2158              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2159          print @rptline
2160  
2161          select @tmp_int = value
2162          from #tempmonitors
2163          where group_name = "lock" and
2164              field_name = "timedout_SH_ADDR"
2165  
2166          select @rptline = "    Shared Address" + space(11) +
2167              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2168              space(2) +
2169              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2170              space(2) +
2171              str(@tmp_int, 10) + space(5) +
2172              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2173          print @rptline
2174  
2175          select @tmp_int = value
2176          from #tempmonitors
2177          where group_name = "lock" and
2178              field_name = "timedout_SH_NKL"
2179  
2180          select @rptline = "    Shared Next-Key" + space(10) +
2181              str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
2182              space(2) +
2183              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2184              space(2) +
2185              str(@tmp_int, 10) + space(5) +
2186              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2187          print @rptline
2188  
2189          print @sum2line
2190  
2191          select @rptline = "  Total Lock Timeouts" + space(8) +
2192              str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
2193              space(2) +
2194              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
2195              space(2) +
2196              str(@tmp_total, 10)
2197          print @rptline
2198      end
2199  
2200      print @blankline
2201      print "  Cluster Lock Summary            per sec      per xact       count  %% of total"
2202      print @sum1line
2203  
2204      select @tmp_total = sum(value)
2205      from #tempmonitors
2206      where group_name like "lock" and
2207          field_name like "clm_lock_gc"
2208      select @tmp_int = sum(value)
2209      from #tempmonitors
2210      where group_name like "lock" and
2211          field_name like "clm_lockgc_target_collected"
2212  
2213      if @tmp_total != 0 /* Avoid Divide by Zero Errors after printout */
2214      begin
2215          print @blankline
2216          select @rptline = "   Lock Garbage Collection   " +
2217              str(@tmp_total / @NumElapsedSec, 12, 1) +
2218              space(2) +
2219              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
2220              space(2) +
2221              str(@tmp_total, 10) + space(7) +
2222              @na_str
2223  
2224          print @rptline
2225          select @rptline = "     Targeted Collection Sucess" +
2226              space(7) +
2227              @na_str +
2228              space(11) +
2229              @na_str +
2230              space(2) +
2231              str(@tmp_int, 10) + space(5) +
2232              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2233          print @rptline
2234          print @blankline
2235      end
2236  
2237      print @blankline
2238      print "  Physical Locks Summary          per sec      per xact       count  %% of total"
2239      print @sum1line
2240  
2241      select @totalrequested = sum(value)
2242      from #tempmonitors
2243      where group_name like "buffer_%" and
2244          field_name like "physical_lock_acquisition"
2245  
2246      select @totalretained = sum(value)
2247      from #tempmonitors
2248      where group_name like "buffer_%" and
2249          field_name like "physical_lock_retented"
2250  
2251      select @totalwaits = sum(value)
2252      from #tempmonitors
2253      where group_name like "lock" and
2254          field_name like "physical_lock_waits"
2255  
2256      select @totaltransfers = sum(value)
2257      from #tempmonitors
2258      where group_name like "buffer_%" and
2259          field_name like "physical_lock_txrecv"
2260  
2261      select @totaldiskreads = sum(value)
2262      from #tempmonitors
2263      where group_name like "buffer_%" and
2264          field_name like "diskread_lockmgr"
2265  
2266      select @totallockmgrcalls = sum(value)
2267      from #tempmonitors
2268      where group_name like "buffer_%" and
2269          field_name like "physical_lock_txrecv"
2270  
2271      if @totalrequested = 0
2272      begin
2273          print "No physical locks are acquired"
2274      end
2275      else
2276      begin
2277          select @rptline = "    Total Lock Requests" + space(6) +
2278              str(@totalrequested / @NumElapsedSec, 12, 1) +
2279              space(2) +
2280              str(@totalrequested / convert(real, @NumXacts), 12, 1) +
2281              space(2) +
2282              str(@totalrequested, 10) + space(5) +
2283              str(100.0 * @totalrequested / @totalrequested, 5, 1) + @psign
2284          print @rptline
2285  
2286          select @rptline = "    Retained Locks    " + space(7) +
2287              str(@totalretained / @NumElapsedSec, 12, 1) +
2288              space(2) +
2289              str(@totalretained / convert(real, @NumXacts), 12, 1) +
2290              space(2) +
2291              str(@totalretained, 10) + space(5) +
2292              str(100.0 * @totalretained / @totalrequested, 5, 1) + @psign
2293          print @rptline
2294  
2295          select @rptline = "    Non-retained Locks" + space(7) +
2296              str((@totalrequested - @totalretained) /
2297                  @NumElapsedSec, 12, 1) +
2298              space(2) +
2299              str((@totalrequested - @totalretained) /
2300                  convert(real, @NumXacts), 12, 1) +
2301              space(2) +
2302              str((@totalrequested - @totalretained), 10) + space(5) +
2303              str(100.0 * (@totalrequested - @totalretained) /
2304                  @totalrequested, 5, 1) + @psign
2305          print @rptline
2306  
2307          select @rptline = "    Transfers Received  " + space(5) +
2308              str(@totaltransfers / @NumElapsedSec, 12, 1) +
2309              space(2) +
2310              str(@totaltransfers / convert(real, @NumXacts), 12, 1) +
2311              space(2) +
2312              str(@totaltransfers, 10) + space(5) +
2313              str(100.0 * @totaltransfers / @totalrequested, 5, 1) + @psign
2314          print @rptline
2315  
2316          select @rptline = "    Data Read from Disk" + space(6) +
2317              str(@totaldiskreads / @NumElapsedSec, 12, 1) +
2318              space(2) +
2319              str(@totaldiskreads / convert(real, @NumXacts), 12, 1) +
2320              space(2) +
2321              str(@totaldiskreads, 10) + space(5) +
2322              str(100.0 * @totaldiskreads / @totalrequested, 5, 1) + @psign
2323          print @rptline
2324  
2325          if (@totalrequested - @totalretained) = 0
2326          begin
2327              select @rptline = "    Physical Lock Contention" + space(1) +
2328                  str(0 / @NumElapsedSec, 12, 1) +
2329                  space(2) +
2330                  str(0 / convert(real, @NumXacts), 12, 1) +
2331                  space(2) +
2332                  str(0, 10) + space(5) +
2333                  str(0.0, 5, 1) +
2334                  @psign
2335          end
2336          else
2337          begin
2338              select @rptline = "    Physical Lock Contention" + space(1) +
2339                  str(@totalwaits / @NumElapsedSec, 12, 1) +
2340                  space(2) +
2341                  str(@totalwaits / convert(real, @NumXacts), 12, 1) +
2342                  space(2) +
2343                  str(@totalwaits, 10) + space(5) +
2344                  str(100.0 * @totalwaits /
2345                      (@totalrequested - @totalretained), 5, 1) +
2346                  @psign
2347              print @rptline
2348          end
2349  
2350          if @totallockmgrcalls = 0
2351          begin
2352              select @rptline = "    Data Location Efficiency" + space(9) +
2353                  " n/a " +
2354                  space(9) +
2355                  " n/a " +
2356                  space(7) +
2357                  " n/a " + space(4) +
2358                  str(100.0, 5, 1) + @psign
2359          end
2360          else
2361          begin
2362              select @rptline = "    Data Location Efficiency" + space(9) +
2363                  " n/a " +
2364                  space(9) +
2365                  " n/a " +
2366                  space(7) +
2367                  " n/a " + space(4) +
2368                  str(100.0 - (100.0 * @totaltransfers /
2369                      @totallockmgrcalls), 5, 1) +
2370                  @psign
2371          end
2372  
2373          print @rptline
2374      end
2375  
2376      select @tmp_total = sum(value)
2377      from #tempmonitors
2378      where group_name like "bcmt" and
2379          field_name like "bcmt_pri_req_queued"
2380  
2381      if @tmp_total > 0
2382      begin
2383          print @blankline
2384          print "  Cluster Cache Manager Requests"
2385  
2386          select @rptline = "    Total Service Requests   " +
2387              str(@tmp_total / @NumElapsedSec, 12, 1) +
2388              space(2) +
2389              str(@tmp_total / convert(real, @NumXacts), 12, 1) +
2390              space(2) +
2391              str(@tmp_total, 10) + space(5) +
2392              str(100.0 * @tmp_total / @tmp_total, 5, 1) + @psign
2393          print @rptline
2394  
2395          select @tmp_int = sum(value)
2396          from #tempmonitors
2397          where group_name like "bcmt" and
2398              field_name in ("bcmt_pri_diskwrites", "bcmt_sec_diskwrites")
2399  
2400          select @rptline = "    Disk Writes              " +
2401              str(@tmp_int / @NumElapsedSec, 12, 1) +
2402              space(2) +
2403              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2404              space(2) +
2405              str(@tmp_int, 10) + space(5) +
2406              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2407          print @rptline
2408  
2409          select @tmp_int = sum(value)
2410          from #tempmonitors
2411          where group_name like "bcmt" and
2412              field_name like "bcmt_num_of_tx_initiated"
2413  
2414          select @rptline = "    Transfer Requests        " +
2415              str(@tmp_int / @NumElapsedSec, 12, 1) +
2416              space(2) +
2417              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2418              space(2) +
2419              str(@tmp_int, 10) + space(5) +
2420              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2421          print @rptline
2422  
2423          select @tmp_int = sum(value)
2424          from #tempmonitors
2425          where group_name like "bcmt" and
2426              field_name like "bcmt_num_of_downgrades"
2427  
2428          select @rptline = "    Downgrade Requests       " +
2429              str(@tmp_int / @NumElapsedSec, 12, 1) +
2430              space(2) +
2431              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2432              space(2) +
2433              str(@tmp_int, 10) + space(5) +
2434              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2435          print @rptline
2436  
2437          select @tmp_int = sum(value)
2438          from #tempmonitors
2439          where group_name like "bcmt" and
2440              field_name in ("bcmt_num_of_downgrades_data_ex_to_pr",
2441                  "bcmt_num_of_downgrades_data_ex_to_nl",
2442                  "bcmt_num_of_downgrades_data_pr_to_nl")
2443  
2444          select @rptline = "      Data Page Requests     " +
2445              str(@tmp_int / @NumElapsedSec, 12, 1) +
2446              space(2) +
2447              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2448              space(2) +
2449              str(@tmp_int, 10) + space(5) +
2450              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2451          print @rptline
2452  
2453          select @tmp_int = sum(value)
2454          from #tempmonitors
2455          where group_name like "bcmt" and
2456              field_name in ("bcmt_num_of_downgrades_index_ex_to_pr",
2457                  "bcmt_num_of_downgrades_index_ex_to_nl",
2458                  "bcmt_num_of_downgrades_index_pr_to_nl")
2459  
2460          select @rptline = "      Index Page Requests    " +
2461              str(@tmp_int / @NumElapsedSec, 12, 1) +
2462              space(2) +
2463              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2464              space(2) +
2465              str(@tmp_int, 10) + space(5) +
2466              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2467          print @rptline
2468  
2469          select @tmp_int = sum(value)
2470          from #tempmonitors
2471          where group_name like "bcmt" and
2472              field_name in ("bcmt_num_of_downgrades_sysalloc_ex_to_pr",
2473                  "bcmt_num_of_downgrades_sysalloc_ex_to_nl",
2474                  "bcmt_num_of_downgrades_sysalloc_pr_to_nl")
2475  
2476          select @rptline = "      Alloc Page Requests    " +
2477              str(@tmp_int / @NumElapsedSec, 12, 1) +
2478              space(2) +
2479              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2480              space(2) +
2481              str(@tmp_int, 10) + space(5) +
2482              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2483          print @rptline
2484  
2485          select @tmp_int = sum(value)
2486          from #tempmonitors
2487          where group_name like "bcmt" and
2488              field_name in ("bcmt_num_of_downgrades_firstoam_ex_to_pr",
2489                  "bcmt_num_of_downgrades_firstoam_ex_to_nl",
2490                  "bcmt_num_of_downgrades_firstoam_pr_to_nl")
2491  
2492          select @rptline = "      First OAM Page Requests" +
2493              str(@tmp_int / @NumElapsedSec, 12, 1) +
2494              space(2) +
2495              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2496              space(2) +
2497              str(@tmp_int, 10) + space(5) +
2498              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2499          print @rptline
2500  
2501          select @tmp_int = sum(value)
2502          from #tempmonitors
2503          where group_name like "bcmt" and
2504              field_name in ("bcmt_num_of_downgrades_nonfirstoam_ex_to_pr",
2505                  "bcmt_num_of_downgrades_nonfirstoam_ex_to_nl",
2506                  "bcmt_num_of_downgrades_nonfirstoam_pr_to_nl")
2507  
2508          select @rptline = "      Other OAM Page Requests" +
2509              str(@tmp_int / @NumElapsedSec, 12, 1) +
2510              space(2) +
2511              str(@tmp_int / convert(real, @NumXacts), 12, 1) +
2512              space(2) +
2513              str(@tmp_int, 10) + space(5) +
2514              str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
2515          print @rptline
2516  
2517      end
2518  
2519      select @totalreqs = sum(value)
2520      from #tempmonitors
2521      where group_name like "lock" and
2522          field_name like "clm_physical_lockreqs"
2523      select @totallocalmaster = sum(value)
2524      from #tempmonitors
2525      where group_name like "lock" and
2526          field_name like "clm_physical_local_master"
2527      select @totalwaits = sum(value)
2528      from #tempmonitors
2529      where group_name like "lock" and
2530          field_name like "clm_physical_waited"
2531      select @totalgrants = @totalreqs - @totalwaits
2532  
2533      print @blankline
2534      if @totalreqs != 0 /* Avoid Divide by Zero Errors after printout */
2535      begin
2536          select @rptline = "    Cluster Lock Requests    " +
2537              str(@totalreqs / @NumElapsedSec, 12, 1) +
2538              space(2) +
2539              str(@totalreqs / convert(real, @NumXacts), 12, 1) +
2540              space(2) +
2541              str(@totalreqs, 10) + space(5) +
2542              str(100.0 * @totalreqs / @totalreqs, 5, 1) + @psign
2543          print @rptline
2544          select @rptline = "    Local Master       " + space(6) +
2545              str(@totallocalmaster / @NumElapsedSec, 12, 1) +
2546              space(2) +
2547              str(@totallocalmaster / convert(real, @NumXacts), 12, 1) +
2548              space(2) +
2549              str(@totallocalmaster, 10) + space(5) +
2550              str(100.0 * @totallocalmaster / @totalreqs, 5, 1) + @psign
2551          print @rptline
2552          if @totalreqs != 0
2553          begin
2554              select @rptline = "      Lock Granted      " + space(5) +
2555                  str(@totalgrants / @NumElapsedSec, 12, 1) +
2556                  space(2) +
2557                  str(@totalgrants / convert(real, @NumXacts), 12, 1) +
2558                  space(2) +
2559                  str(@totalgrants, 10) + space(5) +
2560                  str(100.0 * @totalgrants / @totalreqs, 5, 1) + @psign
2561              print @rptline
2562              select @rptline = "      Lock Waited       " + space(5) +
2563                  str(@totalwaits / @NumElapsedSec, 12, 1) +
2564                  space(2) +
2565                  str(@totalwaits / convert(real, @NumXacts), 12, 1) +
2566                  space(2) +
2567                  str(@totalwaits, 10) + space(5) +
2568                  str(100.0 * @totalwaits / @totalreqs, 5, 1) + @psign
2569              print @rptline
2570          end
2571      end
2572  
2573      select @totalbastreqs = sum(value)
2574      from #tempmonitors
2575      where group_name like "lock" and
2576          field_name like "clm_physical_bast_requests"
2577  
2578      if @totalbastreqs != 0 /* Avoid Divide by Zero Errors after printout */
2579      begin
2580          select @rptline = "    Downgrade Req Recv       " +
2581              str(@totalbastreqs / @NumElapsedSec, 12, 1) +
2582              space(11) +
2583              @na_str +
2584              space(2) +
2585              str(@totalbastreqs, 10) +
2586              space(7) +
2587              @na_str
2588          print @rptline
2589      end
2590  
2591      print @blankline
2592      print "  Logical Locks Summary          per sec      per xact       count  %% of total"
2593      print @sum1line
2594      select @totalreqs = sum(value)
2595      from #tempmonitors
2596      where group_name like "lock" and
2597          field_name like "clm_logical_lockreqs"
2598      select @totallocalmaster = sum(value)
2599      from #tempmonitors
2600      where group_name like "lock" and
2601          field_name like "clm_logical_local_master"
2602      select @totalwaits = sum(value)
2603      from #tempmonitors
2604      where group_name like "lock" and
2605          field_name like "clm_logical_waited"
2606      select @totalgrants = @totalreqs - @totalwaits
2607  
2608      if @totalreqs != 0 /* Avoid Divide by Zero Errors after printout */
2609      begin
2610          select @rptline = "    Cluster Lock Requests    " +
2611              str(@totalreqs / @NumElapsedSec, 12, 1) +
2612              space(2) +
2613              str(@totalreqs / convert(real, @NumXacts), 12, 1) +
2614              space(2) +
2615              str(@totalreqs, 10) + space(5) +
2616              str(100.0 * @totalreqs / @totalreqs, 5, 1) + @psign
2617          print @rptline
2618          select @rptline = "    Local Master             " +
2619              str(@totallocalmaster / @NumElapsedSec, 12, 1) +
2620              space(2) +
2621              str(@totallocalmaster / convert(real, @NumXacts), 12, 1) +
2622              space(2) +
2623              str(@totallocalmaster, 10) + space(5) +
2624              str(100.0 * @totallocalmaster / @totalreqs, 5, 1) + @psign
2625          print @rptline
2626          if @totalreqs != 0
2627          begin
2628              select @rptline = "      Lock Granted      " + space(5) +
2629                  str(@totalgrants / @NumElapsedSec, 12, 1) +
2630                  space(2) +
2631                  str(@totalgrants / convert(real, @NumXacts), 12, 1) +
2632                  space(2) +
2633                  str(@totalgrants, 10) + space(5) +
2634                  str(100.0 * @totalgrants / @totalreqs, 5, 1) + @psign
2635              print @rptline
2636              select @rptline = "      Lock Waited       " + space(5) +
2637                  str(@totalwaits / @NumElapsedSec, 12, 1) +
2638                  space(2) +
2639                  str(@totalwaits / convert(real, @NumXacts), 12, 1) +
2640                  space(2) +
2641                  str(@totalwaits, 10) + space(5) +
2642                  str(100.0 * @totalwaits / @totalreqs, 5, 1) + @psign
2643              print @rptline
2644          end
2645      end
2646  
2647      select @totalbastreqs = sum(value)
2648      from #tempmonitors
2649      where group_name like "lock" and
2650          field_name like "clm_logical_bast_requests"
2651  
2652      if @totalbastreqs != 0 /* Avoid Divide by Zero Errors after printout */
2653      begin
2654          select @rptline = "    Downgrade Req Recv       " +
2655              str(@totalbastreqs / @NumElapsedSec, 12, 1) +
2656              space(11) +
2657              @na_str +
2658              space(2) +
2659              str(@totalbastreqs, 10) +
2660              space(7) +
2661              @na_str
2662          print @rptline
2663      end
2664  
2665      print @blankline
2666      print "  Object Locks Summary            per sec      per xact       count  %% of total"
2667      print @sum1line
2668  
2669      select @totalreqs = sum(value)
2670      from #tempmonitors
2671      where group_name like "lock" and
2672          field_name like "clm_ocm_lockreqs"
2673      select @totallocalmaster = sum(value)
2674      from #tempmonitors
2675      where group_name like "lock" and
2676          field_name like "clm_ocm_local_master"
2677      select @totalwaits = sum(value)
2678      from #tempmonitors
2679      where group_name like "lock" and
2680          field_name like "clm_ocm_waited"
2681      select @totalgrants = @totalreqs - @totalwaits
2682  
2683      if @totalreqs != 0 /* Avoid Divide by Zero Errors after printout */
2684      begin
2685          select @rptline = "    Cluster Lock Requests    " +
2686              str(@totalreqs / @NumElapsedSec, 12, 1) +
2687              space(2) +
2688              str(@totalreqs / convert(real, @NumXacts), 12, 1) +
2689              space(2) +
2690              str(@totalreqs, 10) + space(5) +
2691              str(100.0 * @totalreqs / @totalreqs, 5, 1) + @psign
2692          print @rptline
2693          select @rptline = "    Local Master             " +
2694              str(@totallocalmaster / @NumElapsedSec, 12, 1) +
2695              space(2) +
2696              str(@totallocalmaster / convert(real, @NumXacts), 12, 1) +
2697              space(2) +
2698              str(@totallocalmaster, 10) + space(5) +
2699              str(100.0 * @totallocalmaster / @totalreqs, 5, 1) + @psign
2700          print @rptline
2701          if @totalreqs != 0
2702          begin
2703              select @rptline = "      Lock Granted      " + space(5) +
2704                  str(@totalgrants / @NumElapsedSec, 12, 1) +
2705                  space(2) +
2706                  str(@totalgrants / convert(real, @NumXacts), 12, 1) +
2707                  space(2) +
2708                  str(@totalgrants, 10) + space(5) +
2709                  str(100.0 * @totalgrants / @totalreqs, 5, 1) + @psign
2710              print @rptline
2711              select @rptline = "      Lock Waited       " + space(5) +
2712                  str(@totalwaits / @NumElapsedSec, 12, 1) +
2713                  space(2) +
2714                  str(@totalwaits / convert(real, @NumXacts), 12, 1) +
2715                  space(2) +
2716                  str(@totalwaits, 10) + space(5) +
2717                  str(100.0 * @totalwaits / @totalreqs, 5, 1) + @psign
2718              print @rptline
2719          end
2720      end
2721  
2722      select @totalbastreqs = sum(value)
2723      from #tempmonitors
2724      where group_name like "lock" and
2725          field_name like "clm_ocm_bast_requests"
2726  
2727      if @totalbastreqs != 0 /* Avoid Divide by Zero Errors after printout */
2728      begin
2729          select @rptline = "    Downgrade Req Recv       " +
2730              str(@totalbastreqs / @NumElapsedSec, 12, 1) +
2731              space(11) +
2732              @na_str +
2733              space(2) +
2734              str(@totalbastreqs, 10) +
2735              space(7) +
2736              @na_str
2737          print @rptline
2738      end
2739  
2740      /*****************  Tuning Recommendations  ************************/
2741  
2742      if @Reco = 'Y'
2743      begin
2744          select @reco_hdr_prn = 0
2745          print @blankline
2746  
2747          select @deadlocks = value from #tempmonitors
2748          where group_name = "lock"
2749              and field_name = "deadlocks"
2750  
2751          select @deadlock_search = value from #tempmonitors
2752          where group_name = "lock"
2753              and field_name = "deadlock_search"
2754  
2755          /*
2756          ** If the number of deadlocks is 0 and the
2757          ** number of times deadlock search was done
2758          ** is greater than 0, consider increasing the
2759          ** 'deadlock checking period' parameter
2760          */
2761          if (@deadlocks = 0 and @deadlock_search > 0)
2762          begin
2763              if (@reco_hdr_prn = 0)
2764              begin
2765                  print @recotxt
2766                  print @recoline
2767                  select @reco_hdr_prn = 1
2768              end
2769              print "  - Consider increasing the 'deadlock checking period' parameter"
2770              print "    by 50 ms."
2771              print @blankline
2772          end
2773          /*
2774          ** If the number of deadlocks is greated than 0 and the
2775          ** number of times deadlock search was done is 0,
2776          ** consider decreasing the 'deadlock checking period' parameter
2777          */
2778          else if (@deadlocks > 0 and @deadlock_search = 0)
2779          begin
2780              if (@reco_hdr_prn = 0)
2781              begin
2782                  print @recotxt
2783                  print @recoline
2784                  select @reco_hdr_prn = 1
2785              end
2786              print "  - Consider decreasing the 'deadlock checking period' parameter"
2787              print "    by 50 ms."
2788              print @blankline
2789          end
2790  
2791          /*
2792          ** If the percentage of waits on the last page
2793          ** is > 10% consider partitioning the table or
2794          ** increasing the number of partitions if the
2795          ** table is already partitioned
2796          */
2797          if (@reco_lastpg_wait_percent > 10)
2798          begin
2799              if (@reco_hdr_prn = 0)
2800              begin
2801                  print @recotxt
2802                  print @recoline
2803                  select @reco_hdr_prn = 1
2804              end
2805              print "  - Consider partitioning the heap tables or increasing the"
2806              print "    number of partitions on already partitioned heap tables."
2807              print @blankline
2808          end
2809  
2810          /*
2811          ** If the average length of overflow chain for page & row lock 
2812          ** hashtable  is > 4 consider increasing the 'lock hashtable size'
2813          ** to the next power of 2 from the current configured value.
2814          */
2815          if (@reco_avg_chain_length_pagerow > 4)
2816          begin
2817              if (@reco_hdr_prn = 0)
2818              begin
2819                  print @recotxt
2820                  print @recoline
2821                  select @reco_hdr_prn = 1
2822              end
2823              print "  - Consider increasing the value of 'lock hashtable size' "
2824              print "    configuration option. The value for this configuration "
2825              print "    option must be a power of 2."
2826              print @blankline
2827          end
2828  
2829          /*
2830          ** If the number of engines is > 1 report recommendation for lock 
2831          ** manager related spinlock contentions.
2832          */
2833          if (@NumEngines > 1)
2834          begin /* { */
2835              /* 
2836              ** if the contention on the fglock hashtable spinlocks 
2837              ** is >= 10%, consider decreasing the 'lock spinlock ratio' 
2838              ** config option.
2839              */
2840              if (@reco_fgspin_contention >= 10)
2841              begin
2842                  if (@reco_hdr_prn = 0)
2843                  begin /* { */
2844                      print @recotxt
2845                      print @recoline
2846                      select @reco_hdr_prn = 1
2847                  end /* } */
2848                  print "    - Consider reducing the 'lock spinlock ratio'."
2849                  print @blankline
2850              end
2851              /* 
2852              ** if the contention on the table lock hashtable spinlocks 
2853              ** is >= 10%, consider decreasing the 'lock table spinlock 
2854              ** ratio' config option.
2855              */
2856              if (@reco_tabspin_contention >= 10)
2857              begin
2858                  if (@reco_hdr_prn = 0)
2859                  begin /* { */
2860                      print @recotxt
2861                      print @recoline
2862                      select @reco_hdr_prn = 1
2863                  end /* } */
2864                  print "    - Consider reducing the 'lock table spinlock ratio'."
2865                  print @blankline
2866              end
2867              /* 
2868              ** if the contention on the address lock hashtable spinlocks 
2869              ** is >= 10%, consider decreasing the 'lock address spinlock 
2870              ** ratio' config option.
2871              */
2872              if (@reco_addrspin_contention >= 10)
2873              begin
2874                  if (@reco_hdr_prn = 0)
2875                  begin /* { */
2876                      print @recotxt
2877                      print @recoline
2878                      select @reco_hdr_prn = 1
2879                  end /* } */
2880                  print "    - Consider reducing the 'lock address spinlock ratio'."
2881                  print @blankline
2882              end
2883          end /* } */
2884      end
2885      print @blankline
2886      return 0
2887  

DEFECTS
 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 192
 MUCO 3 Useless Code Useless Brackets 814
 MUCO 3 Useless Code Useless Brackets 1263
 MUCO 3 Useless Code Useless Brackets 2368
 MUCO 3 Useless Code Useless Brackets 2761
 MUCO 3 Useless Code Useless Brackets 2763
 MUCO 3 Useless Code Useless Brackets 2778
 MUCO 3 Useless Code Useless Brackets 2780
 MUCO 3 Useless Code Useless Brackets 2797
 MUCO 3 Useless Code Useless Brackets 2799
 MUCO 3 Useless Code Useless Brackets 2815
 MUCO 3 Useless Code Useless Brackets 2817
 MUCO 3 Useless Code Useless Brackets 2833
 MUCO 3 Useless Code Useless Brackets 2840
 MUCO 3 Useless Code Useless Brackets 2842
 MUCO 3 Useless Code Useless Brackets 2856
 MUCO 3 Useless Code Useless Brackets 2858
 MUCO 3 Useless Code Useless Brackets 2872
 MUCO 3 Useless Code Useless Brackets 2874
 QAFM 3 Var Assignment from potentially many rows 175
 QAFM 3 Var Assignment from potentially many rows 194
 QAFM 3 Var Assignment from potentially many rows 800
 QAFM 3 Var Assignment from potentially many rows 816
 QAFM 3 Var Assignment from potentially many rows 1248
 QAFM 3 Var Assignment from potentially many rows 1265
 QAFM 3 Var Assignment from potentially many rows 1423
 QAFM 3 Var Assignment from potentially many rows 1468
 QAFM 3 Var Assignment from potentially many rows 1489
 QAFM 3 Var Assignment from potentially many rows 1504
 QAFM 3 Var Assignment from potentially many rows 1518
 QAFM 3 Var Assignment from potentially many rows 1532
 QAFM 3 Var Assignment from potentially many rows 1546
 QAFM 3 Var Assignment from potentially many rows 1560
 QAFM 3 Var Assignment from potentially many rows 1574
 QAFM 3 Var Assignment from potentially many rows 1588
 QAFM 3 Var Assignment from potentially many rows 1602
 QAFM 3 Var Assignment from potentially many rows 1616
 QAFM 3 Var Assignment from potentially many rows 1631
 QAFM 3 Var Assignment from potentially many rows 1645
 QAFM 3 Var Assignment from potentially many rows 1659
 QAFM 3 Var Assignment from potentially many rows 1673
 QAFM 3 Var Assignment from potentially many rows 1687
 QAFM 3 Var Assignment from potentially many rows 1701
 QAFM 3 Var Assignment from potentially many rows 1715
 QAFM 3 Var Assignment from potentially many rows 1729
 QAFM 3 Var Assignment from potentially many rows 1744
 QAFM 3 Var Assignment from potentially many rows 1758
 QAFM 3 Var Assignment from potentially many rows 1795
 QAFM 3 Var Assignment from potentially many rows 1824
 QAFM 3 Var Assignment from potentially many rows 1848
 QAFM 3 Var Assignment from potentially many rows 1865
 QAFM 3 Var Assignment from potentially many rows 2007
 QAFM 3 Var Assignment from potentially many rows 2021
 QAFM 3 Var Assignment from potentially many rows 2035
 QAFM 3 Var Assignment from potentially many rows 2049
 QAFM 3 Var Assignment from potentially many rows 2063
 QAFM 3 Var Assignment from potentially many rows 2077
 QAFM 3 Var Assignment from potentially many rows 2091
 QAFM 3 Var Assignment from potentially many rows 2105
 QAFM 3 Var Assignment from potentially many rows 2119
 QAFM 3 Var Assignment from potentially many rows 2133
 QAFM 3 Var Assignment from potentially many rows 2147
 QAFM 3 Var Assignment from potentially many rows 2161
 QAFM 3 Var Assignment from potentially many rows 2175
 QAFM 3 Var Assignment from potentially many rows 2747
 QAFM 3 Var Assignment from potentially many rows 2751
 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 2878
 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: 243 = 242dec - 1exi + 2 6
 MTR3 2 Metrics: Query Complexity Complexity: 1336 6

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

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