DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_dcache_sum  31 Aug 14Defects Dependencies

1     
2     create procedure sp_sysmon_dcache_sum
3         @NumEngines tinyint, /* number of engines online */
4         @NumElapsedMs int, /* for "per Elapsed second" calculations */
5         @NumXacts int, /* for per transactions calculations */
6         @Reco char(1), /* Flag for recommendations             */
7         @instid smallint = NULL /* optional SDC instance id */
8     
9     as
10    
11        /* --------- declare local variables --------- */
12        declare @TotalSearches int /* Total Cache Searches on All Caches */
13        declare @j smallint /* loop index to iterate through multi-counter 
14        ** counters (pool...) */
15        declare @tmp_cntr varchar(35) /* temp var for build field_name's 
16        ** ie. bufgrab_Nk */
17        declare @tmp_int int /* temp var for integer storage */
18        declare @tmp_int2 int /* temp var for integer storage */
19        declare @tmp_int3 int /* temp var for integer storage used to read 
20        ** value of counter 'prefetch_kept_bp' */
21        declare @tmp_int4 int /* temp var for integer storage used to read
22        ** value of counter 'prefetch_cached_bp' */
23        declare @tmp_int_sum int /* temp var for integer storage
24        ** @tmp_int_sum = @tmp_int3 + @tmp_int4 */
25        declare @tmp_total int /* temp var for summing 'total #s' data */
26        declare @tmp_float float /* temp var for float storage */
27        declare @tmp_float2 float /* temp var for float storage */
28        declare @sum2line char(67) /* string to delimit total lines with 
29        ** percent calc on printout */
30        declare @numKBperpg int /* number of kilobytes per logical page */
31        declare @blankline char(1) /* to print blank line */
32        declare @psign char(3) /* hold a percent sign (%) for print out */
33        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
34        declare @zero_str char(80) /* hold an output string for zero "  0.0" for 
35        ** printing zero "% of total" */
36        declare @rptline char(80) /* formatted stats line for print statement */
37        declare @section char(80) /* string to delimit sections on printout */
38    
39        /* ------------- Variables for Tuning Recommendations ------------*/
40        declare @recotxt char(80)
41        declare @recoline char(80)
42        declare @reco_hdr_prn bit
43        declare @ecache_size int /* configured size of extended cache */
44        declare @ecache_searches int /* total number of searches in ecache */
45    
46        /* --------- Setup Environment --------- */
47        set nocount on /* disable row counts being sent to client */
48    
49        select @sum2line = "  -------------------------  ------------  ------------  ----------"
50        select @blankline = " "
51        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
52        select @na_str = "n/a"
53        select @zero_str = "                                      0.0           0.0           0       n/a"
54        select @section = "==============================================================================="
55    
56        print @section
57        print @blankline
58        print "Data Cache Management"
59        print "---------------------"
60        print @blankline
61        print "  Cache Statistics Summary (All Caches)"
62        print "  -------------------------------------"
63        print "                                  per sec      per xact       count  %% of total"
64        print "                             ------------  ------------  ----------  ----------"
65        print @blankline
66        print "    Cache Search Summary"
67    
68        /*
69        ** get total cache searches on all caches 
70        */
71        select @TotalSearches = SUM(value)
72        from #tempmonitors
73        where group_name like "buffer_%" and
74            field_name = "bufsearch_calls"
75    
76        select @tmp_int = SUM(value) /* get cache hits on all caches */
77        from #tempmonitors
78        where group_name like "buffer_%" and
79            field_name = "bufsearch_finds"
80    
81        select @tmp_int2 = @TotalSearches - @tmp_int /* calc total cache misses */
82    
83        /* Initilize some variables to avoid divide by zero error */
84        if @NumElapsedMs = 0
85        begin
86            select @NumElapsedMs = 1
87        end
88    
89        if @NumXacts = 0
90        begin
91            select @NumXacts = 1
92        end
93    
94        if @TotalSearches = 0 /* Avoid Divide by Zero Errors */
95            print @zero_str
96        else
97        begin
98    
99            select @rptline = "      Total Cache Hits" + space(7) +
100               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
101               space(2) +
102               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
103               space(2) +
104               str(@tmp_int, 10) + space(5) +
105               str(100.0 * @tmp_int / @TotalSearches, 5, 1) +
106               @psign
107           print @rptline
108   
109           select @rptline = "      Total Cache Misses" + space(5) +
110               str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
111               space(2) +
112               str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
113               space(2) +
114               str(@tmp_int2, 10) + space(5) +
115               str(100.0 * @tmp_int2 / @TotalSearches, 5, 1) +
116               @psign
117           print @rptline
118   
119           print @sum2line
120           select @rptline = "    Total Cache Searches" + space(5) +
121               str(@TotalSearches / (@NumElapsedMs / 1000.0), 12, 1) +
122               space(2) +
123               str(@TotalSearches / convert(real, @NumXacts), 12, 1) +
124               space(2) +
125               str(@TotalSearches, 10)
126           print @rptline
127       end
128   
129       /* Print extended cache statistics if extended cache is configured */
130       if (@instid is NULL)
131           select @ecache_size = value
132           from master.dbo.sysconfigures
133           where comment = 'extended cache size'
134       else
135       begin
136           select @ecache_size = value
137           from master.dbo.sysconfigures
138           where comment = 'extended cache size'
139   
140   
141           if (@ecache_size is NULL)
142               select @ecache_size = value
143               from master.dbo.sysconfigures
144               where comment = 'extended cache size'
145   
146       end
147   
148       if @ecache_size > 0
149       begin /* { */
150           print @blankline
151           print "    Secondary Cache Search Summary"
152   
153           /*
154           ** Ecache hit % = (ecache_read / ecache_srchcalls) * 100
155           */
156   
157           /* extended cache search calls */
158           select @ecache_searches = value
159           from #tempmonitors
160           where group_name = "ecache" and
161               field_name = "ecache_srchcalls"
162   
163           /* extended cache search hits */
164           select @tmp_int = value
165           from #tempmonitors
166           where group_name = "ecache" and
167               field_name = "ecache_read"
168   
169           /* extended cache misses */
170           select @tmp_int2 = @ecache_searches - @tmp_int
171   
172           if @ecache_searches = 0 /* Avoid Divide by Zero Errors */
173               print @zero_str
174           else
175           begin
176               select @rptline = "      Total Cache Hits" + space(7) +
177                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
178                   space(2) +
179                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
180                   space(2) +
181                   str(@tmp_int, 10) + space(5) +
182                   str(100.0 * @tmp_int / @ecache_searches, 5, 1) +
183                   @psign
184               print @rptline
185   
186               select @rptline = "      Total Cache Misses" + space(5) +
187                   str(@tmp_int2 / (@NumElapsedMs / 1000.0), 12, 1) +
188                   space(2) +
189                   str(@tmp_int2 / convert(real, @NumXacts), 12, 1) +
190                   space(2) +
191                   str(@tmp_int2, 10) + space(5) +
192                   str(100.0 * @tmp_int2 / @ecache_searches, 5, 1) +
193                   @psign
194               print @rptline
195   
196               print @sum2line
197               select @rptline = "    Total Cache Searches" + space(5) +
198                   str(@ecache_searches / (@NumElapsedMs / 1000.0), 12, 1) +
199                   space(2) +
200                   str(@ecache_searches / convert(real, @NumXacts), 12, 1) +
201                   space(2) +
202                   str(@ecache_searches, 10)
203               print @rptline
204           end
205       end /* } */
206       print @blankline
207   
208       print "    Cache Turnover"
209   
210       select @tmp_total = SUM(value)
211       from #tempmonitors
212       where group_name like "buffer_%" and
213           field_name like "bufgrab_%k" and
214           field_name not like "bufgrab_ref%k"
215   
216       select @rptline = "      Buffers Grabbed" + space(8) +
217           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
218           space(2) +
219           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
220           space(2) +
221           str(@tmp_total, 10) + space(7) +
222           @na_str
223       print @rptline
224   
225       if @tmp_total != 0 /* Avoid Divide by Zero Errors */
226       begin
227   
228           select @tmp_int = SUM(value)
229           from #tempmonitors
230           where group_name like "buffer_%" and
231               field_name like "bufgrab_dirty_%"
232   
233           select @rptline = "      Buffers Grabbed Dirty" + space(2) +
234               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
235               space(2) +
236               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
237               space(2) +
238               str(@tmp_int, 10) + space(5) +
239               str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
240           print @rptline
241       end
242       print @blankline
243   
244       print "    Cache Strategy Summary"
245   
246       /*
247       ** Sum all buf unkeeps to look at % of buffers following 
248       ** MRU vs Discard Strategy 
249       */
250   
251       select @tmp_total = SUM(value)
252       from #tempmonitors
253       where group_name like "buffer_%" and
254           field_name IN ("bufunkeep_lru", "bufunkeep_mru")
255   
256       if @tmp_total = 0 /* Avoid Divide by Zero Errors */
257           print @zero_str
258       else
259       begin
260   
261           select @tmp_int = SUM(value)
262           from #tempmonitors
263           where group_name like "buffer_%" and
264               field_name = "bufunkeep_lru"
265   
266           select @rptline = "      Cached (LRU) Buffers" + space(3) +
267               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
268               space(2) +
269               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
270               space(2) +
271               str(@tmp_int, 10) + space(5) +
272               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
273               @psign
274           print @rptline
275   
276           select @tmp_int = SUM(value)
277           from #tempmonitors
278           where group_name like "buffer_%" and
279               field_name = "bufunkeep_mru"
280   
281           select @rptline = "      Discarded (MRU) Buffers" +
282               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
283               space(2) +
284               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
285               space(2) +
286               str(@tmp_int, 10) + space(5) +
287               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
288               @psign
289           print @rptline
290       end
291       print @blankline
292   
293       print "    Large I/O Usage"
294   
295       select @tmp_total = SUM(value)
296       from #tempmonitors
297       where group_name like "buffer_%" and
298           field_name = "prefetch_req"
299   
300       if @tmp_total = 0 /* Avoid Divide by Zero Errors */
301           print @zero_str
302       else
303       begin
304   
305           select @tmp_int = SUM(value)
306           from #tempmonitors
307           where group_name like "buffer_%" and field_name IN
308               ("prefetch_as_requested", "prefetch_page_realign", "prefetch_increase")
309   
310           select @rptline = "      Large I/Os Performed" + space(3) +
311               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
312               space(2) +
313               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
314               space(2) +
315               str(@tmp_int, 10) + space(5) +
316               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
317               @psign
318           print @rptline
319           print @blankline
320   
321           select @rptline = "      Large I/Os Denied due to"
322           print @rptline
323   
324           select @tmp_int = SUM(value)
325           from #tempmonitors
326           where group_name like "buffer_%" and
327               field_name = "prefetch_decrease"
328   
329           select @tmp_int3 = SUM(value)
330           from #tempmonitors
331           where group_name like "buffer_%" and
332               field_name = "prefetch_kept_bp"
333   
334           select @tmp_int4 = SUM(value)
335           from #tempmonitors
336           where group_name like "buffer_%" and
337               field_name = "prefetch_cached_bp"
338   
339           select @tmp_int_sum = @tmp_int3 + @tmp_int4
340   
341           select @tmp_int = @tmp_int - @tmp_int_sum
342   
343           select @rptline = "        Pool < Prefetch Size" + space(1) +
344               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
345               space(2) +
346               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
347               space(2) +
348               str(@tmp_int, 10) + space(5) +
349               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
350               @psign
351           print @rptline
352   
353           select @rptline = "        Pages Requested"
354           print @rptline
355           select @rptline = "        Reside in Another"
356           print @rptline
357   
358           select @rptline = "        Buffer Pool" + space(10) +
359               str(@tmp_int_sum /
360                   (@NumElapsedMs /
361                   1000.0), 12, 1) +
362               space(2) +
363               str(@tmp_int_sum /
364                   convert(real, @NumXacts), 12, 1) +
365               space(2) +
366               str(@tmp_int_sum, 10) +
367               space(5) +
368               str(100.0 *
369                   @tmp_int_sum /
370                   @tmp_total, 5, 1) +
371               @psign
372           print @rptline
373   
374           print @sum2line
375           select @rptline = "    Total Large I/O Requests " +
376               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
377               space(2) +
378               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
379               space(2) +
380               str(@tmp_total, 10)
381           print @rptline
382       end
383   
384       print @blankline
385   
386       print "    Large I/O Effectiveness"
387   
388       /*
389       **  calc total # of (logical) pages brought into all caches from all 
390       **  large I/Os ( > logical pagesize)
391       */
392   
393       /*
394       **  init loop var's to loop through all possible pool sizes 
395       */
396       select @numKBperpg = @@maxpagesize / 1024
397       select @tmp_total = 0, @j = 2 * @numKBperpg
398   
399       while (@j <= 8 * @numKBperpg)
400       begin
401   
402           /* build pool specific counter name, bufgrab_Nk (ie bufgrab_16k) */
403           select @tmp_cntr = "bufgrab_" + convert(varchar(3), @j) + "k"
404   
405           select @tmp_total = @tmp_total + (SUM(value) * (@j / @numKBperpg))
406           from #tempmonitors
407           where group_name like "buffer_%" and
408               field_name = @tmp_cntr
409   
410           select @j = @j * 2
411       end
412   
413       select @rptline = "      Pages by Lrg I/O Cached" +
414           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
415           space(2) +
416           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
417           space(2) +
418           str(@tmp_total, 10) + space(7) +
419           @na_str
420       print @rptline
421   
422       select @tmp_cntr = "bufgrab_ref_" + convert(varchar(3), @numKBperpg) + "K"
423   
424       if @tmp_total != 0 /* Avoid Divide by Zero Errors after printout */
425       begin
426           select @tmp_int = SUM(value)
427           from #tempmonitors
428           where group_name like "buffer_%" and
429               field_name like "bufgrab_ref_%K" and
430               field_name != @tmp_cntr
431   
432           select @rptline = "      Pages by Lrg I/O Used" + space(2) +
433               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
434               space(2) +
435               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
436               space(2) +
437               str(@tmp_int, 10) + space(5) +
438               str(100.0 * @tmp_int / @tmp_total, 5, 1) +
439               @psign
440           print @rptline
441       end
442       print @blankline
443   
444   
445       if exists (select *
446               from #tempmonitors
447               where group_name = "access" and
448                   field_name like "apf%")
449       begin /*{*/
450   
451   
452           print "    Asynchronous Prefetch Activity"
453   
454           select @tmp_total = sum(value)
455           from #tempmonitors
456           where group_name = "access" and
457               field_name in ("apf_IOs_issued", "apf_could_not_start_IO_immediately",
458                   "apf_configured_limit_exceeded", "apf_unused_read_penalty",
459                   "apf_found_in_cache_with_spinlock", "apf_found_in_cache_wo_spinlock")
460   
461           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
462               print @zero_str
463           else
464           begin
465               select @tmp_int = value
466               from #tempmonitors
467               where group_name = "access"
468                   and field_name = "apf_IOs_issued"
469   
470               select @rptline = "      APFs Issued" + space(12) +
471                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
472                   space(2) +
473                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
474                   space(2) +
475                   str(@tmp_int, 10) + space(5) +
476                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
477                   @psign
478               print @rptline
479   
480               select @rptline = "      APFs Denied Due To"
481               print @rptline
482   
483   
484               select @tmp_int = value
485               from #tempmonitors
486               where group_name = "access"
487                   and field_name = "apf_could_not_start_IO_immediately"
488   
489               select @rptline = "        APF I/O Overloads " + space(3) +
490                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
491                   space(2) +
492                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
493                   space(2) +
494                   str(@tmp_int, 10) + space(5) +
495                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
496                   @psign
497               print @rptline
498   
499               select @tmp_int = value
500               from #tempmonitors
501               where group_name = "access"
502                   and field_name = "apf_configured_limit_exceeded"
503   
504               select @rptline = "        APF Limit Overloads " + space(1) +
505                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
506                   space(2) +
507                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
508                   space(2) +
509                   str(@tmp_int, 10) + space(5) +
510                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
511                   @psign
512               print @rptline
513   
514               select @tmp_int = value
515               from #tempmonitors
516               where group_name = "access"
517                   and field_name = "apf_unused_read_penalty"
518   
519               select @rptline = "        APF Reused Overloads " +
520                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
521                   space(2) +
522                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
523                   space(2) +
524                   str(@tmp_int, 10) + space(5) +
525                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
526                   @psign
527               print @rptline
528   
529               select @rptline = "      APF Buffers Found in Cache"
530               print @rptline
531   
532               select @tmp_int = value
533               from #tempmonitors
534               where group_name = "access"
535                   and field_name = "apf_found_in_cache_with_spinlock"
536   
537               select @rptline = "        With Spinlock Held" + space(3) +
538                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
539                   space(2) +
540                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
541                   space(2) +
542                   str(@tmp_int, 10) + space(5) +
543                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
544                   @psign
545               print @rptline
546   
547               select @tmp_int = value
548               from #tempmonitors
549               where group_name = "access"
550                   and field_name = "apf_found_in_cache_wo_spinlock"
551   
552               select @rptline = "        W/o Spinlock Held " + space(3) +
553                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
554                   space(2) +
555                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
556                   space(2) +
557                   str(@tmp_int, 10) + space(5) +
558                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
559                   @psign
560               print @rptline
561   
562               print @sum2line
563               select @rptline = "    Total APFs Requested " + space(4) +
564                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
565                   space(2) +
566                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
567                   space(2) +
568                   str(@tmp_total, 10)
569               print @rptline
570           end
571   
572           print @blankline
573   
574           print "    Other Asynchronous Prefetch Statistics"
575   
576           select @tmp_int = value
577           from #tempmonitors
578           where group_name = "access"
579               and field_name = "apf_IOs_used"
580   
581           select @rptline = "      APFs Used" + space(14) +
582               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
583               space(2) +
584               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
585               space(2) +
586               str(@tmp_int, 10) + space(7) +
587               @na_str
588           print @rptline
589   
590           select @tmp_int = value
591           from #tempmonitors
592           where group_name = "access"
593               and field_name = "apf_waited_for_IO_to_complete"
594   
595           select @rptline = "      APF Waits for I/O" + space(6) +
596               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
597               space(2) +
598               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
599               space(2) +
600               str(@tmp_int, 10) + space(7) +
601               @na_str
602           print @rptline
603   
604           select @tmp_int = sum(value)
605           from #tempmonitors
606           where field_name like "apf%discard%"
607   
608           select @rptline = "      APF Discards" + space(11) +
609               str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
610               space(2) +
611               str(@tmp_int / convert(real, @NumXacts), 12, 1) +
612               space(2) +
613               str(@tmp_int, 10) + space(7) +
614               @na_str
615           print @rptline
616       end /*}*/
617       print @blankline
618   
619   
620       print "    Dirty Read Behavior"
621   
622       select @tmp_total = SUM(value)
623       from #tempmonitors
624       where group_name like "buffer_%" and
625           field_name = "level0_bufpredirty"
626   
627       select @rptline = "      Page Requests" + space(10) +
628           str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
629           space(2) +
630           str(@tmp_total / convert(real, @NumXacts), 12, 1) +
631           space(2) +
632           str(@tmp_total, 10) + space(7) +
633           @na_str
634       print @rptline
635   
636       if @tmp_total != 0 /* Avoid Divide by Zero Errors after printout */
637       begin
638   
639           select @tmp_int = value
640           from #tempmonitors
641           where group_name = "access" and
642               field_name = "dirty_read_restarts"
643   
644           if @tmp_int != 0
645           begin
646               select @rptline = "      Re-Starts" + space(10) +
647                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
648                   space(2) +
649                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
650                   space(2) +
651                   str(@tmp_int, 10) + space(5) +
652                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
653                   @psign
654               print @rptline
655           end
656       end
657       print @blankline
658   
659       /*
660       ** If requested, print global cache recommendations (if any)
661       */
662   
663       if @Reco = 'Y'
664       begin
665           select @recotxt = "  Tuning Recommendations for All Caches"
666           select @recoline = "  -------------------------------------"
667           select @reco_hdr_prn = 0
668           /* recommendations for apf */
669           select @tmp_float = convert(int, (100.0 * a.value / b.value))
670           from #tempmonitors a, #tempmonitors b
671           where a.group_name = "access"
672               and a.field_name = "apf_IOs_issued"
673               and b.group_name = "access"
674               and b.field_name in ("apf_IOs_issued", "apf_could_not_start_IO_immediately",
675                   "apf_configured_limit_exceeded", "apf_unused_read_penalty",
676                   "apf_found_in_cache_with_spinlock", "apf_found_in_cache_wo_spinlock")
677               and b.value != 0
678           if (@tmp_float is not null)
679           begin
680               select @tmp_int = value
681               from #tempmonitors
682               where group_name = "access"
683                   and field_name = "apf_configured_limit_exceeded"
684   
685               /*
686               ** If the number of APF I/O's issued is greater
687               ** than 80% and if the APF configured limit 
688               ** exceeded 0 during the sampling interval
689               ** consider increasing the apf limits for the
690               ** pool or globally for all the pools
691               */
692               if (@tmp_float > 80.0 and @tmp_int > 0)
693               begin
694                   if (@reco_hdr_prn = 0)
695                   begin
696                       print @recotxt
697                       print @recoline
698                       select @reco_hdr_prn = 1
699                   end
700                   print "  - Consider increasing the 'global asynchronous prefetch limit' parameter"
701                   print "    (and the 'local asynchronous prefetch limit' parameter for each pool "
702                   print "    for which this was overidden) by 10%%."
703                   print @blankline
704               end
705           end
706       end
707   
708       return 0
709   


exec sp_procxmode 'sp_sysmon_dcache_sum', 'AnyMode'
go

Grant Execute on sp_sysmon_dcache_sum to public
go
DEFECTS
 QCAR 6 Cartesian product between tables #tempmonitors a and [#tempmonitors b] 670
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_dcache_sum  
 MLCH 3 Char type with length>30 char(67) 28
 MLCH 3 Char type with length>30 char(80) 34
 MLCH 3 Char type with length>30 char(80) 36
 MLCH 3 Char type with length>30 char(80) 37
 MLCH 3 Char type with length>30 char(80) 40
 MLCH 3 Char type with length>30 char(80) 41
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 399
 MUCO 3 Useless Code Useless Brackets 405
 MUCO 3 Useless Code Useless Brackets 669
 MUCO 3 Useless Code Useless Brackets 678
 MUCO 3 Useless Code Useless Brackets 692
 MUCO 3 Useless Code Useless Brackets 694
 QAFM 3 Var Assignment from potentially many rows 131
 QAFM 3 Var Assignment from potentially many rows 136
 QAFM 3 Var Assignment from potentially many rows 142
 QAFM 3 Var Assignment from potentially many rows 158
 QAFM 3 Var Assignment from potentially many rows 164
 QAFM 3 Var Assignment from potentially many rows 465
 QAFM 3 Var Assignment from potentially many rows 484
 QAFM 3 Var Assignment from potentially many rows 499
 QAFM 3 Var Assignment from potentially many rows 514
 QAFM 3 Var Assignment from potentially many rows 532
 QAFM 3 Var Assignment from potentially many rows 547
 QAFM 3 Var Assignment from potentially many rows 576
 QAFM 3 Var Assignment from potentially many rows 590
 QAFM 3 Var Assignment from potentially many rows 639
 QAFM 3 Var Assignment from potentially many rows 669
 QAFM 3 Var Assignment from potentially many rows 680
 QNAJ 3 Not using ANSI Inner Join 670
 VNRD 3 Variable is not read @reco_hdr_prn 698
 VUNU 3 Variable is not used @NumEngines 3
 VUNU 3 Variable is not used @tmp_float2 27
 MSUB 2 Subquery Marker 445
 MTR1 2 Metrics: Comments Ratio Comments: 13% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 57 = 56dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 336 2

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

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