DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_dcache_dtl  31 Aug 14Defects Dependencies

1     
2     
3     create procedure sp_sysmon_dcache_dtl
4         @NumEngines tinyint, /* number of engines online */
5         @NumElapsedMs int, /* for "per Elapsed second" calculations */
6         @NumXacts int, /* for per transactions calculations */
7         @Reco char(1) /* Flag for recommendations             */
8     as
9         /* --------- declare local variables --------- */
10        declare @CacheName varchar(255) /* Cache Name from cache id lookup */
11        declare @CacheID smallint /* Cache ID to map to buffer_N group */
12        declare @NumCaches smallint /* Number of Caches to Report On */
13        declare @TotalSearches int /* Total Cache Searches on All Caches */
14        declare @j smallint /* loop index to iterate through multi-counter
15        ** counters (pool...) */
16        declare @lrgpool tinyint /* boolean (0=No, 1=yes) logic to print
17        ** "Lrg Pool Not Used" Msg
18        */
19        declare @gtlogpgszpool tinyint /* Boolean set while looking for a pool 
20        **  > logical pagesize in current cache
21        **      0 : Did not find pool
22        **      1 : Did find a pool of current size.
23        */
24        declare @tmp_grp varchar(25) /* temp var for build group_name's
25        ** ie. engine_N, disk_N */
26        declare @cfg_repl varchar(24) /* configured value of replacement policy */
27        declare @run_repl varchar(24) /* run value of replacement policy */
28        declare @tmp_cntr varchar(35) /* temp var for build field_name's
29        ** ie. bufgrab_Nk */
30        /* temp var for building fieldname for
31        ** searches found in buffer pool */
32        declare @srchfound_cntr varchar(35)
33        declare @tmp_int int /* temp var for integer storage */
34        declare @tmp_int2 int /* temp var for integer storage */
35        declare @tmp_int3 int /* temp var for integer storage used to read 
36        ** value of counter 'prefetch_kept_bp' */
37        declare @tmp_int4 int /* temp var for integer storage used to read 
38        ** value of counter 'prefetch_cached_bp' */
39        declare @tmp_int_sum int /* temp var for integer storage
40        ** @tmp_int_sum = @tmp_int3 + @tmp_int4 */
41        declare @tmp_total int /* temp var for summing 'total #s' data */
42        declare @tmp_total_send int /* temp var for summing 'total #s' data */
43        declare @tmp_total_recv int /* temp var for summing 'total #s' data */
44        declare @tmp_float float /* temp var for float storage */
45        declare @tmp_float2 float /* temp var for float storage */
46        declare @numKBperpg int /* number of kilobytes per logical page */
47        declare @subsection char(80) /* string to delimit subsections on printout */
48        declare @sum1line char(80) /* string to delimit total lines without
49        ** percent calc on printout */
50        declare @sum2line char(67) /* string to delimit total lines with
51        ** percent calc on printout */
52        declare @blankline char(1) /* to print blank line */
53        declare @psign char(3) /* hold a percent sign (%) for print out */
54        declare @na_str char(3) /* holds 'n/a' for 'not applicable' strings */
55        declare @zero_str char(80) /* hold an output string for zero "  0.0" for
56        ** printing zero "% of total" */
57        declare @rptline varchar(530) /* formatted stats line for print statement */
58        declare @section char(80) /* string to delimit sections on printout */
59        declare @totalrequested int /* total no of physical locks requested */
60        declare @totalretained int /* total no of physical locks retained */
61        declare @totaldiskreads int /* total no of diskreads completed for physical
62        ** lock acquisition.*/
63        declare @totaldeadlocks int /* total no of deadlocks occured while taking
64        ** physical lock. */
65        declare @totaltransfers int /* total no. of buffers transferred to this
66        ** instance. */
67        declare @totalwaits int /* totan no. of times tasks had to wait for a
68        ** transfer to this instance. */
69        declare @totaldiskwrites int /* total no of disk writes when a transfer
70        ** is requested.*/
71        declare @totallockmgrcalls int /* total no. of times lock manager was 
72        ** consulted for physical lock. */
73        declare @NumElapsedSec real /* No. of elapsed seconds */
74    
75        /* ------------- Variables for Tuning Recommendations ------------*/
76        declare @recotxt varchar(300)
77        declare @recoline char(80)
78        declare @reco_hdr_prn bit
79        declare @spinlock_contention float
80    
81        /* --------- Setup Environment --------- */
82        set nocount on /* disable row counts being sent to client */
83    
84        select @subsection = "-------------------------------------------------------------------------------"
85        select @sum1line = "  -------------------------  ------------  ------------  ----------  ----------"
86        select @sum2line = "  -------------------------  ------------  ------------  ----------"
87        select @blankline = " "
88        select @psign = " %%" /* extra % symbol because '%' is escape char in print statement */
89        select @na_str = "n/a"
90        select @zero_str = "                                      0.0           0.0           0       n/a"
91        select @section = "==============================================================================="
92    
93        /*
94        ** Declare cursor to walk temp cache table in cache name
95        ** order to print cache-specific statistics
96        */
97        select @TotalSearches = SUM(value)
98        from #tempmonitors
99        where group_name like "buffer_%" and
100           field_name = "bufsearch_calls"
101   
102       declare cache_info cursor for
103       select cid, name, group_name,
104           ltrim(rtrim(config_replacement)), ltrim(rtrim(run_replacement))
105       from #cachemap
106       order by name
107       for read only
108   
109       open cache_info
110       fetch cache_info into @CacheID, @CacheName, @tmp_grp, @cfg_repl, @run_repl
111   
112       /* 
113       ** Get all Spinlock related counters and cache names
114       ** for printing potential spinlock contention.
115       */
116       select P.field_name as name,
117           P.value as grabs,
118           W.value as waits,
119           S.value as spins into #foo
120       from #tempmonitors P, #tempmonitors W, #tempmonitors S
121       where
122           P.group_name = "spinlock_p"
123           and W.group_name = "spinlock_w"
124           and S.group_name = "spinlock_s"
125           and P.field_id = W.field_id
126           and P.field_id = S.field_id
127           and P.field_name in (select name from #cachemap)
128   
129       /* Create a #temp table to store information on currently configured
130       ** pools for each cache.
131       */
132   
133       /* Initilize some variables to avoid divide by zero error */
134       if @NumElapsedMs = 0
135       begin
136           select @NumElapsedMs = 1
137       end
138   
139       if @NumXacts = 0
140       begin
141           select @NumXacts = 1
142       end
143   
144       select @NumElapsedSec = @NumElapsedMs / 1000.0
145   
146       while (@@sqlstatus = 0) /* { */
147       begin
148           print @subsection
149           select @rptline = space(2) + "Cache: " + @CacheName
150           print @rptline
151           print "                                  per sec      per xact       count  %% of total"
152           print @sum1line
153   
154           /* Print spinlock contention */
155   
156           select @spinlock_contention =
157               isnull(100.0 * (convert(float, sum(waits) / sum(
158                   (case when grabs >= 0 then grabs
159                       else (power(2.0, 32) + grabs) end)))), 0)
160           from #foo
161           where name = @CacheName
162               and grabs != 0
163   
164           select @rptline = "    Spinlock Contention" + space(15) +
165               @na_str + space(11) +
166               @na_str + space(9) +
167               @na_str + space(5) +
168               str(@spinlock_contention, 5, 1)
169               + @psign
170           print @rptline
171           print @blankline
172   
173           if @TotalSearches != 0
174           begin
175               select @tmp_int = value
176               from #tempmonitors
177               where group_name = @tmp_grp and
178                   field_name = "bufsearch_calls"
179   
180               select @rptline = "    Utilization " + space(22) +
181                   @na_str + space(11) +
182                   @na_str + space(9) +
183                   @na_str + space(5) +
184                   str(100.0 * @tmp_int /
185                       @TotalSearches, 5, 1) +
186                   @psign
187   
188               print @rptline
189               print @blankline
190           end
191   
192           print "    Cache Searches"
193   
194           select @tmp_total = value
195           from #tempmonitors
196           where group_name = @tmp_grp and
197               field_name = "bufsearch_calls"
198   
199           if @tmp_total = 0 /* Avoid Divide by Zero Errors */
200           begin
201               select @rptline = "      Total Cache Searches            0.0           0.0           0       n/a"
202               print @rptline
203           end
204           else
205           begin
206   
207               select @tmp_int = value
208               from #tempmonitors
209               where group_name = @tmp_grp and
210                   field_name = "bufsearch_finds"
211   
212               select @rptline = "      Cache Hits" + space(13) +
213                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
214                   space(2) +
215                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
216                   space(2) +
217                   str(@tmp_int, 10) + space(5) +
218                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
219                   @psign
220               print @rptline
221   
222               /* save hits for wash % and missed calc */
223               select @tmp_int2 = @tmp_int
224   
225               select @tmp_int = value
226               from #tempmonitors
227               where group_name = @tmp_grp and
228                   field_name = "bufsearch_finds_in_wash"
229   
230               if @tmp_int2 != 0
231               begin
232                   select @rptline = "         Found in Wash" + space(7) +
233                       str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
234                       space(2) +
235                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
236                       space(2) +
237                       str(@tmp_int, 10) + space(5) +
238                       str(100.0 * @tmp_int / @tmp_int2, 5, 1) +
239                       @psign
240                   print @rptline
241               end
242   
243               select @tmp_int = @tmp_total - @tmp_int2 /* missed searches */
244   
245               select @rptline = "      Cache Misses" + space(11) +
246                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
247                   space(2) +
248                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
249                   space(2) +
250                   str(@tmp_int, 10) + space(5) +
251                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
252                   @psign
253               print @rptline
254           end /* else @tmp_total = 0 */
255   
256           print @sum2line
257           select @rptline = "    Total Cache Searches" + space(5) +
258               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
259               space(2) +
260               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
261               space(2) +
262               str(@tmp_total, 10)
263           print @rptline
264   
265           print @blankline
266   
267   
268           select @tmp_total = SUM(value)
269           from #tempmonitors
270           where group_name = @tmp_grp and
271               field_name like "bufgrab_%k" and field_name not like "bufgrab_ref%k"
272   
273           select @numKBperpg = @@maxpagesize / 1024
274           if @tmp_total = 0
275           begin
276               select @rptline = "    Pool Turnover                     0.0           0.0           0       n/a"
277               print @rptline
278           end
279           else
280           begin /* { */
281               print "    Pool Turnover"
282               /* init loop ctr to loop through all pool sizes */
283               select @j = @numKBperpg
284               while (@j <= 8 * @numKBperpg) /* { */
285               begin
286   
287                   /* 
288                   **  build pool specific counter name, 
289                   **  bufgrab_Nk (ie bufgrab_16k) 
290                   */
291                   select @tmp_cntr = "bufgrab_" + rtrim(convert(varchar(3), @j)) + "k"
292   
293                   select @tmp_int = value
294                   from #tempmonitors
295                   where group_name = @tmp_grp and
296                       field_name = @tmp_cntr
297   
298                   if @tmp_int != 0 /* { */
299                   begin
300   
301                       select @rptline = space(6) +
302                           convert(char(3), @j) + "Kb Pool"
303                       print @rptline
304   
305                       select @rptline = "          LRU Buffer Grab" +
306                           space(4) +
307                           str(@tmp_int / (@NumElapsedMs /
308                               1000.0), 12, 1) +
309                           space(2) +
310                           str(@tmp_int /
311                               convert(real, @NumXacts), 12, 1) +
312                           space(2) +
313                           str(@tmp_int, 10) + space(5) +
314                           str(100.0 * @tmp_int / @tmp_total, 5, 1) +
315                           @psign
316                       print @rptline
317   
318                       select @tmp_cntr = "bufgrab_locked_" +
319                           convert(varchar(3), @j) + "k"
320   
321                       select @tmp_int2 = value
322                       from #tempmonitors
323                       where group_name = @tmp_grp and
324                           field_name = @tmp_cntr
325   
326                       select @rptline = space(12) + "Grabbed Locked Buffer" +
327                           space(1) +
328                           str(@tmp_int2 / (@NumElapsedMs /
329                               1000.0), 7, 1) +
330                           space(2) +
331                           str(@tmp_int2 /
332                               convert(real, @NumXacts), 12, 1) +
333                           space(2) +
334                           str(@tmp_int2, 10) + space(5) +
335                           str(100.0 * @tmp_int2 / @tmp_int, 5, 1) +
336                           @psign
337                       print @rptline
338   
339                       select @tmp_cntr = "bufgrab_dirty_" +
340                           convert(varchar(3), @j) + "k"
341   
342                       select @tmp_int2 = value
343                       from #tempmonitors
344                       where group_name = @tmp_grp and
345                           field_name = @tmp_cntr
346   
347                       select @rptline = space(12) + "Grabbed Dirty" +
348                           space(4) +
349                           str(@tmp_int2 / (@NumElapsedMs /
350                               1000.0), 12, 1) +
351                           space(2) +
352                           str(@tmp_int2 /
353                               convert(real, @NumXacts), 12, 1) +
354                           space(2) +
355                           str(@tmp_int2, 10) + space(5) +
356                           str(100.0 * @tmp_int2 / @tmp_int, 5, 1) +
357                           @psign
358                       print @rptline
359   
360                   end /* } if @tmp_int != 0 */
361   
362                   /* get next pool size (power of 2) */
363                   select @j = @j * 2
364   
365               end /* } while */
366   
367   
368               print @sum2line /* calc cache turnover percent of all caches */
369               select @rptline = "    Total Cache Turnover" + space(5) +
370                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
371                   space(2) +
372                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
373                   space(2) +
374                   str(@tmp_total, 10)
375               print @rptline
376           end /* } else @tmp_total != 0 */
377           print @blankline
378   
379           print "    Cluster Cache Behavior"
380   
381           select @totalrequested = sum(value)
382           from #tempmonitors
383           where group_name = @tmp_grp and
384               field_name like "physical_lock_acquisition"
385   
386           select @totalretained = sum(value)
387           from #tempmonitors
388           where group_name = @tmp_grp and
389               field_name = "physical_lock_retented"
390   
391           select @totaldeadlocks = sum(value)
392           from #tempmonitors
393           where group_name = @tmp_grp and
394               field_name = "physical_lock_deadlock"
395   
396           select @totalwaits = sum(value)
397           from #tempmonitors
398           where group_name = @tmp_grp and
399               field_name = "waited_on_tx"
400   
401           select @totaltransfers = sum(value)
402           from #tempmonitors
403           where group_name = @tmp_grp and
404               field_name = "physical_lock_txrecv"
405   
406           select @totaldiskreads = sum(value)
407           from #tempmonitors
408           where group_name = @tmp_grp and
409               field_name = "diskread_lockmgr"
410   
411           select @totaldiskwrites = sum(value)
412           from #tempmonitors
413           where group_name = @tmp_grp and
414               field_name = "diskwrite"
415   
416           select @totallockmgrcalls = sum(value)
417           from #tempmonitors
418           where group_name = @tmp_grp and
419               field_name = "physical_lock_lockmgr"
420   
421           if @totalrequested = 0
422           begin
423               print "      No physical locks are acquired on buffers in this cache"
424           end
425           else
426           begin
427               select @rptline = "      Total Lock Requests    " +
428                   str(@totalrequested / @NumElapsedSec, 12, 1) +
429                   space(2) +
430                   str(@totalrequested / convert(real, @NumXacts), 12, 1) +
431                   space(2) +
432                   str(@totalrequested, 10) + space(5) +
433                   str(100.0 * @totalrequested / @totalrequested, 5, 1) +
434                   @psign
435               print @rptline
436   
437               select @rptline = "      Retained Locks         " +
438                   str(@totalretained / @NumElapsedSec, 12, 1) +
439                   space(2) +
440                   str(@totalretained / convert(real, @NumXacts), 12, 1) +
441                   space(2) +
442                   str(@totalretained, 10) + space(5) +
443                   str(100.0 * @totalretained / @totalrequested, 5, 1) +
444                   @psign
445               print @rptline
446   
447               select @rptline = "      Non-retained Locks     " +
448                   str((@totalrequested - @totalretained) /
449                       @NumElapsedSec, 12, 1) +
450                   space(2) +
451                   str((@totalrequested - @totalretained) /
452                       convert(real, @NumXacts), 12, 1) +
453                   space(2) +
454                   str((@totalrequested - @totalretained), 10) + space(5) +
455                   str(100.0 * (@totalrequested - @totalretained) /
456                       @totalrequested, 5, 1) +
457                   @psign
458               print @rptline
459   
460               select @rptline = "      Data Read from Disk    " +
461                   str(@totaldiskreads / @NumElapsedSec, 12, 1) +
462                   space(2) +
463                   str(@totaldiskreads / convert(real, @NumXacts), 12, 1) +
464                   space(2) +
465                   str(@totaldiskreads, 10) + space(5) +
466                   str(100.0 * @totaldiskreads / @totalrequested, 5, 1) +
467                   @psign
468               print @rptline
469   
470               select @tmp_total_recv = sum(value)
471               from #tempmonitors
472               where group_name like "buffer_%" and
473                   field_name = "physical_lock_txrecv"
474   
475               if @tmp_total_recv > 0
476               begin
477                   select @rptline = "      Transfers Received     " +
478                       str(@totaltransfers / @NumElapsedSec, 12, 1) +
479                       space(2) +
480                       str(@totaltransfers /
481                           convert(real, @NumXacts), 12, 1) +
482                       space(2) +
483                       str(@totaltransfers, 10) + space(5) +
484                       str(100.0 * @totaltransfers /
485                           @totalrequested, 5, 1) +
486                       @psign
487                   print @rptline
488               end
489               else
490               begin
491                   select @rptline = "      Transfers Received     " +
492                       str(0.0, 12, 1) +
493                       space(2) +
494                       str(0.0, 12, 1) +
495                       space(2) +
496                       str(0.0, 10) + space(5) +
497                       str(0.0, 5, 1) + @psign
498                   print @rptline
499               end
500   
501               select @rptline = "      Waited for Data Transfer" +
502                   str(@totalwaits / @NumElapsedSec, 11, 1) +
503                   space(2) +
504                   str(@totalwaits / convert(real, @NumXacts), 12, 1) +
505                   space(2) +
506                   str(@totalwaits, 10) + space(5) +
507                   str(100.0 * @totalwaits / @totalrequested, 5, 1) +
508                   @psign
509               print @rptline
510   
511               select @rptline = "      Deadlocks              " +
512                   str(@totaldeadlocks / @NumElapsedSec, 12, 1) +
513                   space(2) +
514                   str(@totaldeadlocks / convert(real, @NumXacts), 12, 1) +
515                   space(2) +
516                   str(@totaldeadlocks, 10) + space(5) +
517                   str(100.0 * @totaldeadlocks / @totalrequested, 5, 1) +
518                   @psign
519               print @rptline
520   
521               select @rptline = "      Data Write to Disk     " +
522                   str(@totaldiskwrites / @NumElapsedSec, 12, 1) +
523                   space(2) +
524                   str(@totaldiskwrites / convert(real, @NumXacts), 12, 1) +
525                   space(2) +
526                   str(@totaldiskwrites, 10) + space(5) + "  n/a "
527               print @rptline
528   
529               select @tmp_total_send = sum(value)
530               from #tempmonitors
531               where group_name like "buffer_%" and
532                   field_name = "physical_lock_txsend"
533   
534               if @tmp_total_send > 0
535               begin
536                   select @tmp_int = sum(value)
537                   from #tempmonitors
538                   where group_name = @tmp_grp and
539                       field_name = "physical_lock_txsend"
540   
541                   select @rptline = "      Transfers Sent         " +
542                       str(@tmp_int / @NumElapsedSec, 12, 1) +
543                       space(2) +
544                       str(@tmp_int / convert(real, @NumXacts), 12, 1) +
545                       space(2) +
546                       str(@tmp_int, 10) + space(5) + "  n/a "
547                   print @rptline
548               end
549               else
550               begin
551                   select @rptline = "      Transfers Sent         " +
552                       str(0.0, 12, 1) +
553                       space(2) +
554                       str(0.0, 12, 1) +
555                       space(2) +
556                       str(0.0, 10) + space(5) + "  n/a "
557                   print @rptline
558               end
559   
560               if (@totallockmgrcalls) = 0
561               begin
562                   select @rptline = "      Data Location Efficiency " +
563                       space(7) +
564                       @na_str + space(11) +
565                       @na_str + space(9) +
566                       @na_str + space(5) +
567                       str(100.0, 5, 1) +
568                       @psign
569               end
570               else
571               begin
572                   select @rptline = "      Data Location Efficiency " +
573                       space(7) +
574                       @na_str + space(11) +
575                       @na_str + space(9) +
576                       @na_str + space(5) +
577                       str(100.0 - (100.0 * @totaltransfers /
578                           (@totallockmgrcalls)), 5, 1) +
579                       @psign
580               end
581               print @rptline
582           end
583   
584           print @blankline
585   
586           print "    Buffer Wash Behavior"
587   
588           select @tmp_total = value
589           from #tempmonitors
590           where group_name = @tmp_grp and
591               field_name = "bufwash_throughput"
592   
593           if @tmp_total != 0 /* any buffers move through wash yet? */
594           begin
595   
596               select @tmp_int = value
597               from #tempmonitors
598               where group_name = @tmp_grp and
599                   field_name = "bufwash_pass_clean"
600   
601               select @rptline = "      Buffers Passed Clean" + space(3) +
602                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
603                   space(2) +
604                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
605                   space(2) +
606                   str(@tmp_int, 10) + space(5) +
607                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
608                   @psign
609               print @rptline
610   
611               select @tmp_int = value
612               from #tempmonitors
613               where group_name = @tmp_grp and
614                   field_name = "bufwash_pass_writing"
615   
616               select @rptline = "      Buffers Already in I/O " +
617                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
618                   space(2) +
619                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
620                   space(2) +
621                   str(@tmp_int, 10) + space(5) +
622                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
623                   @psign
624               print @rptline
625   
626               select @tmp_int = value
627               from #tempmonitors
628               where group_name = @tmp_grp and
629                   field_name = "bufwash_write_dirty"
630   
631               select @rptline = "      Buffers Washed Dirty   " +
632                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
633                   space(2) +
634                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
635                   space(2) +
636                   str(@tmp_int, 10) + space(5) +
637                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
638                   @psign
639               print @rptline
640           end
641           else
642               print "      Statistics Not Available - No Buffers Entered Wash Section Yet"
643   
644           print @blankline
645   
646           print "    Cache Strategy"
647   
648           /* 
649           ** Sum all buf unkeeps to look at % of buffers following 
650           ** MRU vs Discard Strategy 
651           */
652           select @tmp_total = SUM(value)
653           from #tempmonitors
654           where group_name = @tmp_grp and
655               field_name IN ("bufunkeep_lru", "bufunkeep_mru")
656   
657           if @tmp_total != 0
658           begin
659               select @tmp_int = value
660               from #tempmonitors
661               where group_name = @tmp_grp and
662                   field_name = "bufunkeep_lru"
663   
664               select @rptline = "      Cached (LRU) Buffers   " +
665                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
666                   space(2) +
667                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
668                   space(2) +
669                   str(@tmp_int, 10) + space(5) +
670                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
671                   @psign
672               print @rptline
673   
674               select @tmp_int = value
675               from #tempmonitors
676               where group_name = @tmp_grp and
677                   field_name = "bufunkeep_mru"
678   
679               select @rptline = "      Discarded (MRU) Buffers" +
680                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
681                   space(2) +
682                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
683                   space(2) +
684                   str(@tmp_int, 10) + space(5) +
685                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
686                   @psign
687               print @rptline
688           end
689           else
690               print "      Statistics Not Available - No Buffers Displaced Yet"
691   
692           print @blankline
693   
694           print "    Large I/O Usage"
695   
696           select @tmp_total = value
697           from #tempmonitors
698           where group_name = @tmp_grp and
699               field_name = "prefetch_req"
700   
701           if @tmp_total = 0
702           begin
703               select @rptline = "      Total Large I/O Requests        0.0           0.0           0       n/a"
704               print @rptline
705           end
706           else
707           begin
708   
709               select @tmp_int = SUM(value)
710               from #tempmonitors
711               where group_name = @tmp_grp and field_name IN
712                   ("prefetch_as_requested", "prefetch_page_realign", "prefetch_increase")
713   
714               select @rptline = "      Large I/Os Performed" + space(3) +
715                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
716                   space(2) +
717                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
718                   space(2) +
719                   str(@tmp_int, 10) + space(5) +
720                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
721                   @psign
722               print @rptline
723               print @blankline
724   
725               select @rptline = "      Large I/Os Denied due to"
726               print @rptline
727   
728               select @tmp_int = value
729               from #tempmonitors
730               where group_name = @tmp_grp and
731                   field_name = "prefetch_decrease"
732   
733               select @tmp_int3 = value
734               from #tempmonitors
735               where group_name = @tmp_grp and
736                   field_name = "prefetch_kept_bp"
737   
738               select @tmp_int4 = value
739               from #tempmonitors
740               where group_name = @tmp_grp and
741                   field_name = "prefetch_cached_bp"
742   
743               select @tmp_int_sum = @tmp_int3 + @tmp_int4
744               select @tmp_int = @tmp_int - @tmp_int_sum
745   
746               select @rptline = "        Pool < Prefetch Size" + space(1) +
747                   str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
748                   space(2) +
749                   str(@tmp_int / convert(real, @NumXacts), 12, 1) +
750                   space(2) +
751                   str(@tmp_int, 10) + space(5) +
752                   str(100.0 * @tmp_int / @tmp_total, 5, 1) +
753                   @psign
754               print @rptline
755   
756               select @rptline = "        Pages Requested"
757               print @rptline
758               select @rptline = "        Reside in Another"
759               print @rptline
760   
761               select @rptline = "        Buffer Pool" + space(10) +
762                   str(@tmp_int_sum /
763                       (@NumElapsedMs /
764                       1000.0), 12, 1) +
765                   space(2) +
766                   str(@tmp_int_sum /
767                       convert(real, @NumXacts), 12, 1) +
768                   space(2) +
769                   str(@tmp_int_sum, 10) +
770                   space(5) +
771                   str(100.0 *
772                       @tmp_int_sum /
773                       @tmp_total, 5, 1) +
774                   @psign
775               print @rptline
776               print @sum2line
777               select @rptline = "    Total Large I/O Requests " +
778                   str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
779                   space(2) +
780                   str(@tmp_total / convert(real, @NumXacts), 12, 1) +
781                   space(2) +
782                   str(@tmp_total, 10)
783               print @rptline
784           end /* else */
785   
786           print @blankline
787   
788           print "    Large I/O Detail"
789   
790           /*
791           **  default to NO large pools found for this cache 
792           */
793           select @lrgpool = 0
794           /* 
795           ** init loop counter to loop through all large I/O pool 
796           */
797           select @j = @numKBperpg * 2
798           while (@j <= 8 * @numKBperpg) /* { */
799           begin
800   
801               /* Check that the current cache has a pool configured of size @j */
802               select @gtlogpgszpool = count(*)
803               from #pool_detail_per_cache pd
804               where pd.io_size = convert(varchar(8), @j)
805                   and name = @CacheName
806   
807               if (@gtlogpgszpool > 0)
808               begin /* { */
809   
810                   /* Remember that we _did_ find a large I/O pool */
811                   select @lrgpool = 1
812   
813                   /* 
814                   ** build pool specific counter name, 
815                   ** bufgrab_Nk (ie bufgrab_16k) 
816                   */
817                   select @tmp_cntr = "bufgrab_" + convert(varchar(3), @j) + "k"
818   
819                   select @tmp_total = value
820                   from #tempmonitors
821                   where group_name = @tmp_grp and
822                       field_name = @tmp_cntr
823   
824                   select @rptline = space(5) + convert(char(4), @j) +
825                       "Kb Pool"
826                   print @rptline
827   
828                   if @tmp_total = 0
829                   begin
830                       select @rptline = "        Pages Cached" + space(18) +
831                           ltrim(@zero_str)
832                       print @rptline
833   
834                       select @rptline = "        Pages Used" + space(20) +
835                           ltrim(@zero_str)
836                       print @rptline
837                   end
838   
839                   else
840                   begin
841   
842                       /* turn # of masses into # of logical pages */
843                       select @tmp_total = @tmp_total * (@j / @numKBperpg)
844   
845                       select @rptline = "        Pages Cached" + space(9) +
846                           str(@tmp_total /
847                               (@NumElapsedMs / 1000.0), 12, 1) +
848                           space(2) +
849                           str(@tmp_total /
850                               convert(real, @NumXacts), 12, 1) +
851                           space(2) +
852                           str(@tmp_total, 10) + space(7) +
853                           @na_str
854                       print @rptline
855   
856                       select @tmp_cntr = "bufgrab_ref_" +
857                           convert(varchar(3), @j) + "K"
858   
859                       select @tmp_int = value
860                       from #tempmonitors
861                       where group_name = @tmp_grp and
862                           field_name = @tmp_cntr
863   
864                       select @rptline = "        Pages Used" + space(11) +
865                           str(@tmp_int / (@NumElapsedMs / 1000.0), 12, 1) +
866                           space(2) +
867                           str(@tmp_int / convert(real, @NumXacts), 12, 1) +
868                           space(2) +
869                           str(@tmp_int, 10) + space(5) +
870                           str(100.0 * @tmp_int / @tmp_total, 5, 1) + @psign
871                       print @rptline
872                   end
873   
874               end /* } if @gtlogpgszpool > 0 */
875   
876               select @j = @j * 2 /* get next pool size */
877           end /* } @j <= 8*@numKBperpg */
878   
879           if @lrgpool = 0 /* No large pools in this cache */
880           begin
881               print "      No Large Pool(s) In This Cache"
882           end
883   
884           print @blankline
885   
886           print "    Dirty Read Behavior"
887   
888           select @tmp_total = value
889           from #tempmonitors
890           where group_name = @tmp_grp and
891               field_name = "level0_bufpredirty"
892   
893           select @rptline = "	  Page Requests" + space(6) +
894               str(@tmp_total / (@NumElapsedMs / 1000.0), 12, 1) +
895               space(2) +
896               str(@tmp_total / convert(real, @NumXacts), 12, 1) +
897               space(2) +
898               str(@tmp_total, 10) + space(7) +
899               @na_str
900           print @rptline
901           print @blankline
902   
903           if @Reco = 'Y'
904           begin /* { */
905               select @recotxt = "    Tuning Recommendations for Data cache : " + @CacheName
906               select @recoline = "    -------------------------------------"
907               select @reco_hdr_prn = 0
908   
909               /* recommendations for cache replacement policy */
910   
911               select @tmp_float = convert(int, (100.0 * a.value / b.value))
912               from #tempmonitors a, #tempmonitors b
913               where a.group_name = @tmp_grp and
914                   b.group_name = @tmp_grp and
915                   a.group_name = b.group_name and
916                   a.field_name = "bufsearch_finds" and
917                   b.field_name = "bufsearch_calls" and
918                   b.value != 0
919               if (@tmp_float is not null)
920               begin /* { */
921                   select @tmp_float2 = 0
922                   select @tmp_float2 = (100.0 * a.value) / b.value
923                   from #tempmonitors a, #tempmonitors b
924                   where a.group_name = @tmp_grp and
925                       b.group_name = @tmp_grp and
926                       a.group_name = b.group_name and
927                       a.field_name = "bufwash_write_dirty" and
928                       b.field_name = "bufwash_throughput" and
929                       b.value != 0
930   
931                   if (@tmp_float2 is null)
932                       select @tmp_float2 = 0
933   
934                   /* 
935                   ** If the Cache Hit Rate is greater than 95% and
936                   ** the replacement is less than 5% and if the
937                   ** existing replacement policy is "strict LRU"
938                   ** then consider using "relaxed lru replacement"
939                   ** policy for this cache.
940                   */
941                   if ((@tmp_float >= 95.0 and @tmp_float2 <= 5.0) and
942                           @NumEngines > 1)
943                   begin /* { */
944                       if (@run_repl = "strict LRU")
945                       begin /* { */
946                           if (@reco_hdr_prn = 0)
947                           begin /* { */
948                               print @recotxt
949                               print @recoline
950                               select @reco_hdr_prn = 1
951                           end /* } */
952                           print "    - Consider using 'relaxed LRU replacement policy'"
953                           print "      for this cache."
954                           print @blankline
955                       end /* } */
956                   end /* } */
957                   else
958                   begin /* { */
959                       /* 
960                       ** If the Cache Hit Rate is less than 95% and
961                       ** the replacement is greater than 5% and if the
962                       ** existing replacement policy is "relaxed LRU"
963                       ** then consider using "Strict lru replacement"
964                       ** policy for this cache.
965                       */
966                       if (@run_repl = "relaxed LRU")
967                       begin /* { */
968                           if (@reco_hdr_prn = 0)
969                           begin /* { */
970                               print @recotxt
971                               print @recoline
972                               select @reco_hdr_prn = 1
973                           end /* } */
974                           print "    - Consider using 'strict LRU replacement policy'."
975                           print "      for this cache."
976                           print @blankline
977                       end /* } */
978                   end /* } */
979               end /* } */
980   
981               /* recommendations for pool wash size */
982   
983               select @tmp_int = SUM(value)
984               from #tempmonitors
985               where group_name = @tmp_grp and
986                   field_name like "bufgrab_dirty_%"
987   
988               if (@tmp_int is not null)
989               begin /* { */
990                   select @j = @numKBperpg
991                   while (@j <= 8 * @numKBperpg)
992                   begin /* { */
993   
994                       /* 
995                       **  build pool specific counter name, 
996                       **  bufgrab_Nk (ie bufgrab_16k) 
997                       */
998                       select @tmp_cntr = "bufgrab_dirty_" +
999                           convert(varchar(3), @j) + "k"
1000  
1001                      select @tmp_int = value
1002                      from #tempmonitors
1003                      where group_name = @tmp_grp and
1004                          field_name = @tmp_cntr
1005  
1006                      if @tmp_int != 0
1007                      begin /* { */
1008                          if (@reco_hdr_prn = 0)
1009                          begin /* { */
1010                              print @recotxt
1011                              print @recoline
1012                              select @reco_hdr_prn = 1
1013                          end /* } */
1014                          /*
1015                          ** If We grabbed a buffer that was
1016                          ** dirty from this pool consider increasing
1017                          ** the wash size for this buffer pool
1018                          */
1019                          select @rptline = "    - Consider increasing the 'wash size' of the " + ltrim(str(@j, 3)) + "k pool for this cache."
1020                          print @rptline
1021                          print @blankline
1022  
1023                      end /* } */
1024                      /* get next pool size (power of 2) */
1025                      select @j = @j * 2
1026                  end /* } */
1027  
1028              end /* } */
1029  
1030              /* recommendations for pool addition */
1031              if (select value from #tempmonitors
1032                      where group_name = @tmp_grp and
1033                          field_name like "bufopt_lrgmass_reqd") > 0
1034              begin /* { */
1035                  if (@reco_hdr_prn = 0)
1036                  begin /* { */
1037                      print @recotxt
1038                      print @recoline
1039                      select @reco_hdr_prn = 1
1040                  end /* } */
1041                  /*
1042                  ** If the optimizer wanted to do large I/O but could
1043                  ** not find a buffer pool configured to be able
1044                  ** to do this large I/O consider having a large I/O
1045                  ** pool for this cache
1046                  */
1047                  print "    - Consider adding a large I/O pool for this cache."
1048                  print @blankline
1049              end /* } */
1050  
1051  
1052              /* recommendations for pool removal */
1053              select @j = @numKBperpg * 2
1054              select @tmp_cntr = "bufgrab_" +
1055                  convert(varchar(3), @j) + "k"
1056              select @srchfound_cntr = "bufsearch_finds_" +
1057                  convert(varchar(3), @j) + "k"
1058  
1059              /*
1060              ** The recommendation to remove a large buffer pool will be
1061              ** printed only when the bufgrabs and bufsearch_finds are 0
1062              ** for the buffer pool. This is to avoid
1063              ** printing this message when the data is entirely cached
1064              ** in the buffer pool and hence not having any grabs.
1065              */
1066              if ((select value from #tempmonitors
1067                          where group_name = @tmp_grp and
1068                              field_name like @tmp_cntr) = 0
1069                      and (select value from #tempmonitors
1070                          where group_name = @tmp_grp and
1071                              field_name like @srchfound_cntr) = 0
1072                      and exists (select * from #pool_detail_per_cache
1073                          where io_size = convert(varchar(8), @j)
1074                              and name = @CacheName))
1075              begin /* { */
1076                  if (@reco_hdr_prn = 0)
1077                  begin /* { */
1078                      print @recotxt
1079                      print @recoline
1080                      select @reco_hdr_prn = 1
1081                  end /* } */
1082                  /*
1083                  ** If there are no grabs for this buffer pool
1084                  ** consider removing this buffer pool.
1085                  */
1086                  select @rptline = "    - Consider removing the " + ltrim(str(@j, 3)) + "k pool for this cache."
1087                  print @rptline
1088                  print @blankline
1089              end /* } */
1090  
1091              select @j = @j * 2
1092              select @tmp_cntr = "bufgrab_" + convert(varchar(3), @j) + "k"
1093              select @srchfound_cntr = "bufsearch_finds_" +
1094                  convert(varchar(3), @j) + "k"
1095  
1096              if ((select value from #tempmonitors
1097                          where group_name = @tmp_grp and
1098                              field_name like @tmp_cntr) = 0
1099                      and (select value from #tempmonitors
1100                          where group_name = @tmp_grp and
1101                              field_name like @srchfound_cntr) = 0
1102                      and exists (select * from #pool_detail_per_cache
1103                          where io_size = convert(varchar(8), @j)
1104                              and name = @CacheName))
1105              begin /* { */
1106                  if (@reco_hdr_prn = 0)
1107                  begin /* { */
1108                      print @recotxt
1109                      print @recoline
1110                      select @reco_hdr_prn = 1
1111                  end /* } */
1112                  /*
1113                  ** If there are no grabs for this buffer pool
1114                  ** consider removing this buffer pool.
1115                  */
1116                  select @rptline = "    - Consider removing the " + ltrim(str(@j, 3)) + "k pool for this cache."
1117                  print @rptline
1118                  print @blankline
1119              end /* } */
1120  
1121              select @j = @j * 2
1122              select @tmp_cntr = "bufgrab_" + convert(varchar(3), @j) + "k"
1123              select @srchfound_cntr = "bufsearch_finds_" +
1124                  convert(varchar(3), @j) + "k"
1125  
1126              if ((select value from #tempmonitors
1127                          where group_name = @tmp_grp and
1128                              field_name like @tmp_cntr) = 0
1129                      and (select value from #tempmonitors
1130                          where group_name = @tmp_grp and
1131                              field_name like @srchfound_cntr) = 0
1132                      and exists (select * from #pool_detail_per_cache
1133                          where io_size = convert(varchar(8), @j)
1134                              and name = @CacheName))
1135              begin /* { */
1136                  if (@reco_hdr_prn = 0)
1137                  begin /* { */
1138                      print @recotxt
1139                      print @recoline
1140                      select @reco_hdr_prn = 1
1141                  end /* } */
1142                  /*
1143                  ** If there are no grabs for this buffer pool
1144                  ** consider removing this buffer pool.
1145                  */
1146                  select @rptline = "    - Consider removing the " + ltrim(str(@j, 3)) + "k pool for this cache."
1147                  print @rptline
1148                  print @blankline
1149              end /* } */
1150  
1151              /* recommendations for cache splitting  */
1152  
1153              /*
1154              ** If the number of engines is > 1 
1155              ** and if the contention on the buffer
1156              ** manager spinlock is > 10%
1157              ** consider using cache partitions or named caches
1158              ** or both
1159              ** Also there are potential conditions where the waits or
1160              ** grabs might go negative because of the counter overflowing
1161              ** what an integer can hold; Cover for those cases as well.
1162              */
1163              if (@NumEngines > 1 and (@spinlock_contention >= 10 or
1164                          @spinlock_contention < 0))
1165              begin /* { */
1166                  if (@reco_hdr_prn = 0)
1167                  begin /* { */
1168                      print @recotxt
1169                      print @recoline
1170                      select @reco_hdr_prn = 1
1171                  end /* } */
1172                  print "    - Consider using Named Caches or Cache partitions or both."
1173                  print @blankline
1174              end /* } */
1175          end /* } */
1176  
1177          fetch cache_info into @CacheID, @CacheName, @tmp_grp, @cfg_repl, @run_repl
1178      end /* } while @@sqlstatus */
1179  
1180      close cache_info
1181      deallocate cursor cache_info
1182  
1183      return 0
1184  


exec sp_procxmode 'sp_sysmon_dcache_dtl', 'AnyMode'
go

Grant Execute on sp_sysmon_dcache_dtl to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @j: smallint = int 283
 MTYP 4 Assignment type mismatch @j: smallint = int 990
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_dcache_dtl  
 MLCH 3 Char type with length>30 char(80) 47
 MLCH 3 Char type with length>30 char(80) 48
 MLCH 3 Char type with length>30 char(67) 50
 MLCH 3 Char type with length>30 char(80) 55
 MLCH 3 Char type with length>30 char(80) 58
 MLCH 3 Char type with length>30 char(80) 77
 MNER 3 No Error Check should check @@error after select into 116
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 560
 MUCO 3 Useless Code Useless Brackets 577
 MUCO 3 Useless Code Useless Brackets 798
 MUCO 3 Useless Code Useless Brackets 807
 MUCO 3 Useless Code Useless Brackets 911
 MUCO 3 Useless Code Useless Brackets 919
 MUCO 3 Useless Code Useless Brackets 931
 MUCO 3 Useless Code Useless Brackets 941
 MUCO 3 Useless Code Useless Brackets 944
 MUCO 3 Useless Code Useless Brackets 946
 MUCO 3 Useless Code Useless Brackets 966
 MUCO 3 Useless Code Useless Brackets 968
 MUCO 3 Useless Code Useless Brackets 988
 MUCO 3 Useless Code Useless Brackets 991
 MUCO 3 Useless Code Useless Brackets 1008
 MUCO 3 Useless Code Useless Brackets 1035
 MUCO 3 Useless Code Useless Brackets 1066
 MUCO 3 Useless Code Useless Brackets 1076
 MUCO 3 Useless Code Useless Brackets 1096
 MUCO 3 Useless Code Useless Brackets 1106
 MUCO 3 Useless Code Useless Brackets 1126
 MUCO 3 Useless Code Useless Brackets 1136
 MUCO 3 Useless Code Useless Brackets 1163
 MUCO 3 Useless Code Useless Brackets 1166
 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 207
 QAFM 3 Var Assignment from potentially many rows 225
 QAFM 3 Var Assignment from potentially many rows 293
 QAFM 3 Var Assignment from potentially many rows 321
 QAFM 3 Var Assignment from potentially many rows 342
 QAFM 3 Var Assignment from potentially many rows 588
 QAFM 3 Var Assignment from potentially many rows 596
 QAFM 3 Var Assignment from potentially many rows 611
 QAFM 3 Var Assignment from potentially many rows 626
 QAFM 3 Var Assignment from potentially many rows 659
 QAFM 3 Var Assignment from potentially many rows 674
 QAFM 3 Var Assignment from potentially many rows 696
 QAFM 3 Var Assignment from potentially many rows 728
 QAFM 3 Var Assignment from potentially many rows 733
 QAFM 3 Var Assignment from potentially many rows 738
 QAFM 3 Var Assignment from potentially many rows 819
 QAFM 3 Var Assignment from potentially many rows 859
 QAFM 3 Var Assignment from potentially many rows 888
 QAFM 3 Var Assignment from potentially many rows 911
 QAFM 3 Var Assignment from potentially many rows 922
 QAFM 3 Var Assignment from potentially many rows 1001
 QJWT 3 Join or Sarg Without Index on temp table 125
 QJWT 3 Join or Sarg Without Index on temp table 126
 QJWT 3 Join or Sarg Without Index on temp table 127
 QJWT 3 Join or Sarg Without Index on temp table 915
 QJWT 3 Join or Sarg Without Index on temp table 926
 QNAJ 3 Not using ANSI Inner Join 120
 QNAJ 3 Not using ANSI Inner Join 912
 QNAJ 3 Not using ANSI Inner Join 923
 QNUA 3 Should use Alias: Table #cachemap 127
 VNRD 3 Variable is not read @section 91
 VUNU 3 Variable is not used @NumCaches 12
 CRDO 2 Read Only Cursor Marker (has for read only clause) 103
 MSUB 2 Subquery Marker 1031
 MSUB 2 Subquery Marker 1066
 MSUB 2 Subquery Marker 1069
 MSUB 2 Subquery Marker 1072
 MSUB 2 Subquery Marker 1096
 MSUB 2 Subquery Marker 1099
 MSUB 2 Subquery Marker 1102
 MSUB 2 Subquery Marker 1126
 MSUB 2 Subquery Marker 1129
 MSUB 2 Subquery Marker 1132
 MTR1 2 Metrics: Comments Ratio Comments: 19% 3
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 118 = 117dec - 1exi + 2 3
 MTR3 2 Metrics: Query Complexity Complexity: 578 3

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

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