DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_dcachestats  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_sysmon_dcachestats
3         @top_n_objs varchar(14), /* top 'n' objects to report on */
4         @cachename varchar(255), /* cache name */
5         @seconds int,
6         @Reco_option char(1) /* print recommendations */
7     as
8     
9         declare @blankline char(80)
10        declare @separator_line char(80)
11        declare @rpt_line char(80) /* formatted line for print */
12        declare @exec_str char(1250) /* used in exec immediate */
13        declare @ldspc char(1)
14        declare @col_sep char(1)
15        declare @psign char(1) /* percent sign */
16        declare @ret int
17        declare @cache_size_in_KB bigint
18        declare @top_n int /* int value of top_n_objs */
19    
20        declare @cache_sz_str varchar(13)
21        declare @cache_hit_per varchar(6)
22        declare @cache_usage_per varchar(6)
23        declare @cache_sz_used real
24        declare @logical_reads int
25        declare @physical_reads int
26        declare @spinlock_contention float
27        declare @cache_partitions int
28        declare @cache_id int
29    
30        declare @db_name varchar(255)
31        declare @obj_dbid int
32        declare @tab_id int
33        declare @obj_ind_id int
34        declare @obj_size int
35        declare @obj_lreads int
36        declare @obj_preads int
37        declare @obj_cached_KB int
38        declare @cache_occp_by_obj_per varchar(6)
39        declare @obj_hit_per varchar(6)
40        declare @display_order int /* 1 - desc, 0 - asc */
41        declare @begin_time datetime /* values were cleared */
42        declare @end_time datetime /* values are reported */
43        declare @cut_off_lreads int /* value that decides rows
44        ** selection */
45        declare @max_objname_len int /* auto format object name */
46        declare @tmpval int
47        declare @reco varchar(255) /* recommendation string */
48        declare @max_reco_len int
49        declare @num_engines int
50        declare @numKBperpg int
51        declare @tmp_int int
52        declare @iter_cnt int
53        declare @tmp_cntr varchar(35)
54        declare @tmp_grp varchar(18) /* group id in sysmonitors */
55    
56        declare @LReads_col varchar(6)
57        declare @PReads_col varchar(6)
58        declare @LReads_rename_col varchar(6)
59        declare @PReads_rename_col varchar(6)
60        declare @Usage_Per_col varchar(6)
61        declare @Run_Size_col varchar(8)
62        declare @Cache_Partitions_col varchar(16)
63        declare @Spin_Contention_col varchar(20)
64        declare @Hit_Per_col varchar(5)
65        declare @IO_Size_col varchar(7)
66        declare @Wash_col varchar(9)
67        declare @APF_Per_col varchar(5)
68        declare @APF_Eff_Per_col varchar(9)
69        declare @Object_col varchar(6)
70        declare @Obj_Size_col varchar(8)
71        declare @Size_In_Cache_col varchar(13)
72        declare @Obj_Cached_Per_col varchar(12)
73        declare @Cache_Occupied_Per_col varchar(12)
74    
75        declare @tempdbname varchar(30) /* assigned tempdb */
76    
77        select @blankline = " "
78        select @rpt_line = " "
79        select @separator_line = replicate("-", 80)
80        select @ldspc = " "
81        select @col_sep = ":"
82        select @psign = "%"
83        select @numKBperpg = @@maxpagesize / 1024
84    
85        select @LReads_col = "LReads"
86        select @PReads_col = "PReads"
87        select @LReads_rename_col = "LR/sec"
88        select @PReads_rename_col = "PR/sec"
89        select @Usage_Per_col = "Usage%"
90        select @Run_Size_col = "Run Size"
91        select @Cache_Partitions_col = "Cache Partitions"
92        select @Spin_Contention_col = "Spinlock Contention%"
93        select @Hit_Per_col = "Hit%"
94        select @IO_Size_col = "IO Size"
95        select @Wash_col = "Wash Size"
96        select @APF_Per_col = "APF%"
97        select @APF_Eff_Per_col = "APF-Eff%"
98        select @Object_col = "Object"
99        select @Obj_Size_col = "Obj Size"
100       select @Size_In_Cache_col = "Size in Cache"
101       select @Obj_Cached_Per_col = "Obj_Cached%"
102       select @Cache_Occupied_Per_col = "Cache_Occp%"
103   
104       create table #obj_details(dbid int, owner varchar(30), objid int,
105           indid int, index_name varchar(30),
106           size_KB int)
107   
108       create table #recommendations_tab(reco_col varchar(255))
109   
110       select co.config, parent, convert(char(30), co.name) name,
111           convert(char(30), co.comment) comment, cu.value run_size,
112           memory_used wash_size, apf_percent apf_value
113       into #syscacheconfig
114       from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu
115       where parent = 19
116           and co.config = cu.config
117           and co.name = cu.comment
118       order by name, config
119   
120       select @begin_time = min(InsertTime),
121           @end_time = max(InsertTime)
122       from tempdb.dbo.tempcachestats
123   
124       select @top_n = convert(int, @top_n_objs)
125       select @display_order = sign(@top_n)
126       if @display_order = 0
127       begin
128           select @display_order = 1
129       end
130       select @top_n = abs(@top_n)
131   
132       set nocount on
133   
134       /* Create the diff tables */
135       select 1 as "PrintOrder", cs2.CacheName, cs2.CachePartitions, cs2.CacheID,
136           cs2.LogicalReads - cs1.LogicalReads as LogicalReads,
137           cs2.PhysicalReads - cs1.PhysicalReads as PhysicalReads
138       into #tempcachestats
139       from tempdb.dbo.tempcachestats cs1,
140           tempdb.dbo.tempcachestats cs2
141       where cs2.CacheName = cs1.CacheName
142           and cs2.InsertTime = @end_time
143           and cs1.InsertTime = @begin_time
144   
145       update #tempcachestats
146       set PrintOrder = 0
147       where CacheName = 'default data cache'
148   
149       select bs2.CacheName,
150           bs2.IOBufferSize,
151           bs2.AllocatedKB,
152           bs2.PagesTouched * convert(numeric, @@maxpagesize / 1024) as PoolUsed,
153           bs2.PhysicalReads - bs1.PhysicalReads as PhysicalReads,
154           tm1.value + tm2.value as LReads,
155           tm2.value as PReads,
156           tm3.value as APFs_Used
157       into #tempbufpoolstats
158       from tempdb.dbo.tempbufpoolstats bs2,
159           tempdb.dbo.tempbufpoolstats bs1,
160           #tempmonitors tm1,
161           #tempmonitors tm2,
162           #tempmonitors tm3
163       where bs2.InsertTime = @end_time
164           and bs1.InsertTime = @begin_time
165           and bs2.CacheName = bs1.CacheName
166           and bs2.IOBufferSize = bs1.IOBufferSize
167           and tm1.group_name = "buffer_"
168           + convert(varchar(4), bs2.CacheID)
169           and tm2.group_name = tm1.group_name
170           and tm3.group_name = tm1.group_name
171           and tm1.field_name = "bufsearch_finds_"
172           + convert(varchar(3), bs2.IOBufferSize / 1024) + "k"
173           and tm2.field_name = "bufread_"
174           + convert(varchar(3), bs2.IOBufferSize / 1024) + "k"
175           and tm3.field_name = "apf_ios_used_"
176           + convert(varchar(3), bs2.IOBufferSize / 1024) + "k"
177   
178   
179       select InsertTime, sum(CachedKB) as CachedKB, CacheName,
180           DBID, ObjectID, IndexID
181       into #tempcachedobjstats
182       from tempdb.dbo.tempcachedobjstats
183       group by InsertTime, ObjectID, IndexID
184   
185       select co1.CacheName,
186           os2.LogicalReads - os1.LogicalReads as LogicalReads,
187           os2.PhysicalReads - os1.PhysicalReads as PhysicalReads,
188           os2.DBID, os2.ObjectID, os2.IndexID,
189           0 as CachedKB
190       into #tempobjstats
191       from tempdb.dbo.tempobjstats os1,
192           tempdb.dbo.tempobjstats os2,
193           #tempcachedobjstats co1
194       where os1.DBID *= co1.DBID
195           and os1.ObjectID *= co1.ObjectID
196           and os1.IndexID *= co1.IndexID
197           and os2.DBID = os1.DBID
198           and os2.ObjectID = os1.ObjectID
199           and os2.IndexID = os1.IndexID
200           and os2.InsertTime = @end_time
201           and os1.InsertTime = @begin_time
202           and co1.InsertTime = @end_time
203       union
204       select co.CacheName,
205           os.LogicalReads,
206           os.PhysicalReads,
207           os.DBID, os.ObjectID, os.IndexID,
208           co.CachedKB
209       from tempdb.dbo.tempobjstats os,
210           #tempcachedobjstats co
211       where os.DBID = co.DBID
212           and os.ObjectID = co.ObjectID
213           and os.IndexID = co.IndexID
214           and os.InsertTime = @end_time
215           and co.InsertTime = @end_time
216           and not exists
217               (select ObjectID from tempdb.dbo.tempobjstats
218               where ObjectID = os.ObjectID
219                   and ObjectID = co.ObjectID
220                   and DBID = os.DBID
221                   and IndexID = os.IndexID
222                   and InsertTime = @begin_time)
223   
224       /* Objects that have 0 as LogicalReads are removed */
225       delete #tempobjstats
226       where LogicalReads = 0
227   
228       update #tempobjstats
229       set os.CachedKB = co.CachedKB,
230           os.CacheName = co.CacheName
231       from #tempobjstats os,
232           #tempcachedobjstats co
233       where os.DBID = co.DBID
234           and os.ObjectID = co.ObjectID
235           and os.IndexID = co.IndexID
236           and co.InsertTime = @end_time
237   
238       /*
239       ** create cursor to process cache rows
240       */
241       if (@cachename = "NULL")
242       begin
243           select @cachename = "%"
244       end
245   
246       declare named_cache_cursor cursor for
247       select CacheName,
248           CachePartitions,
249           CacheID,
250           LogicalReads,
251           PhysicalReads
252       from #tempcachestats
253       where CacheName like @cachename
254       order by PrintOrder, CacheName
255       for read only
256   
257       /*
258       ** Declare cursor for storing sampled object stats
259       */
260       declare objstats_cursor cursor for
261       select LogicalReads, PhysicalReads,
262           DBID, ObjectID, IndexID, CachedKB
263       from #tempobjstats
264       where CacheName like @cachename
265           and
266           ((@display_order = 1 and LogicalReads >= @cut_off_lreads)
267               or
268               (@display_order = - 1 and LogicalReads <= @cut_off_lreads)
269           )
270       order by PhysicalReads desc, (LogicalReads * @display_order) desc
271       for read only
272   
273       open named_cache_cursor
274       fetch named_cache_cursor into @cachename, @cache_partitions, @cache_id,
275           @logical_reads, @physical_reads
276   
277       dump tran tempdb with truncate_only
278   
279       /*
280       ** If assigned temporary database is different than system tempdb, then
281       ** dump that one as well.
282       */
283       select @tempdbname = db_name(@@tempdbid)
284       if (@tempdbname != "tempdb")
285       begin
286           dump tran @tempdbname with truncate_only
287       end
288   
289       select @num_engines = count(*) from #tempmonitors
290       where field_name = "clock_ticks"
291           and group_name like "engine_%"
292           and value > 0
293   
294       /*
295       ** Print Report Header
296       */
297       select @rpt_line = replicate("=", 80)
298       print @rpt_line
299   
300       select @rpt_line = space(34) + "Cache Wizard" + space(34)
301       print @rpt_line
302   
303       select @rpt_line = replicate("=", 80)
304       print @rpt_line
305   
306       while (@@sqlstatus = 0)
307       begin
308           print @blankline
309           select @cachename = ltrim(rtrim(@cachename))
310           select @rpt_line = replicate('-', datalength(@cachename))
311           print @rpt_line
312   
313           print @cachename
314   
315           print @rpt_line
316   
317           select @cache_size_in_KB = run_size from #syscacheconfig
318           where config = 19
319               and parent = 19
320               and rtrim(name) = @cachename
321   
322           select @cache_sz_str = str(@cache_size_in_KB * 1.0 / 1024, 9, 2) + " Mb"
323   
324           select @cache_sz_used = sum(PoolUsed),
325               @logical_reads = sum(LReads),
326               @physical_reads = sum(PReads)
327           from #tempbufpoolstats
328           where CacheName like @cachename
329   
330           if (@logical_reads = 0)
331           begin
332               select @cache_hit_per = "   n/a"
333           end
334           else
335           begin
336               select @cache_hit_per = str(((@logical_reads - @physical_reads)
337                       * 100.0
338                       / @logical_reads), 6, 2)
339           end
340   
341           select @cache_usage_per = str(@cache_sz_used * 100.0
342                   / @cache_size_in_KB, 6, 2)
343   
344           /* P.value is grabs and W.value is waits */
345           select @spinlock_contention = isnull(100.0 * sum(convert(float, W.value)) / sum(convert(float, P.value)), 0)
346           from #tempmonitors P,
347               #tempmonitors W
348           where P.field_name = @cachename
349               and P.group_name = "spinlock_p"
350               and W.group_name = "spinlock_w"
351               and P.field_id = W.field_id
352               and P.value > 0
353   
354           select @rpt_line = @Run_Size_col + @ldspc + @ldspc
355               + @ldspc + @ldspc + @ldspc + @ldspc + @ldspc
356               + @ldspc + @col_sep
357               + @cache_sz_str + @ldspc + @ldspc + @ldspc
358               + @Usage_Per_col + @psign + @ldspc + @ldspc
359               + @ldspc + @ldspc + @ldspc + @ldspc + @ldspc
360               + @ldspc + @ldspc + @ldspc + @ldspc
361               + @ldspc + @ldspc + @ldspc
362               + @col_sep + @ldspc + @ldspc + @ldspc + @ldspc
363               + @cache_usage_per
364           print @rpt_line
365   
366           /* If the sampled period is less than 1 sec then @seconds will be '0'.*/
367           /* Round @seconds to '1' if it is '0' to avoid devide by zero problems */
368   
369           if (@seconds = 0) select @seconds = 1
370   
371           select @rpt_line = @LReads_rename_col + @ldspc + @ldspc
372               + @ldspc + @ldspc + @ldspc + @ldspc + @ldspc
373               + @ldspc + @ldspc + @ldspc
374               + @col_sep
375               + str(@logical_reads * 1.0 / @seconds, 9, 2)
376               + @ldspc + @ldspc + @ldspc + @ldspc
377               + @ldspc + @ldspc
378               + @PReads_rename_col + @ldspc + @ldspc + @ldspc
379               + @ldspc + @ldspc + @ldspc + @ldspc + @ldspc
380               + @ldspc + @ldspc + @ldspc
381               + @ldspc + @ldspc + @ldspc
382               + @col_sep + @ldspc
383               + str(@physical_reads * 1.0 / @seconds, 9, 2)
384               + @ldspc + @ldspc
385               + @ldspc + @Hit_Per_col + @psign + @col_sep + @ldspc
386               + @cache_hit_per
387           print @rpt_line
388   
389           select @rpt_line = @Cache_Partitions_col + @col_sep
390               + str(@cache_partitions, 9) + @ldspc + @ldspc
391               + @ldspc + @ldspc + @ldspc + @ldspc
392               + @Spin_Contention_col + @psign
393               + @col_sep + @ldspc + @ldspc + @ldspc + @ldspc
394               + str(@spinlock_contention, 6, 2)
395           print @rpt_line
396   
397           print @blankline
398   
399           /*
400           ** Print the Buffer Pool Stats
401           */
402           select @rpt_line = "Buffer Pool Information"
403           print @rpt_line
404   
405           print @separator_line
406   
407           select @exec_str = "select str(IOBufferSize / 1024, 3) + ' Kb' as '"
408               + @IO_Size_col
409               + "', str(wash_size, 7) + ' Kb' as '"
410               + @Wash_col
411               + "', str(AllocatedKB * 1.0 / 1024, 8, 2) + ' Mb' as '"
412               + @Run_Size_col
413               + "', str(apf_value, 6, 2) as '"
414               + @APF_Per_col
415               + "', str(" + @LReads_col + " * 1.0 / "
416               + convert(varchar(6), @seconds)
417               + ", 8, 2) as '" + @LReads_rename_col
418               + "', str(" + @PReads_col + " * 1.0 / "
419               + convert(varchar(6), @seconds)
420               + ", 8, 2) as '" + @PReads_rename_col
421               + "', case when ("
422               + @LReads_col
423               + " = 0) then '   n/a' else str(("
424               + @LReads_col + " - " + @PReads_col
425               + ") * 100.0 / ("
426               + @LReads_col
427               + "), 6, 2) end as '" + @Hit_Per_col
428               + "', case when (PhysicalReads - "
429               + @PReads_col + " <= 0) then '   n/a' else str("
430               + "APFs_Used * 100.0 / (PhysicalReads - "
431               + @PReads_col + "), 6, 2) end as '"
432               + @APF_Eff_Per_col
433               + "', str(PoolUsed * 100.0 / AllocatedKB, 6, 2) as '"
434               + @Usage_Per_col
435               + "' from #syscacheconfig, #tempbufpoolstats "
436               + "where config = (19 + floor(1.45 * "
437               + "log(IOBufferSize / 1024))) and parent=19 "
438               + " and rtrim(name)='" + @cachename
439               + "' and CacheName like '" + @cachename
440               + "' order by IOBufferSize desc"
441           exec (@exec_str)
442           print @blankline
443   
444           if @Reco_option = 'Y'
445           begin
446               if (@cache_sz_used * 100.0 / @cache_size_in_KB < 5.0)
447               begin
448                   select @reco = @Usage_Per_col + " for '"
449                       + @cachename + "'"
450                       + " is low (< 5%)"
451                   insert into #recommendations_tab(reco_col)
452                   values (@reco)
453               end
454   
455               select @exec_str = "insert into #recommendations_tab "
456                   + " select '" + @Usage_Per_col + "'"
457                   + " + ' for '"
458                   + " + str(IOBufferSize/1024, 2) "
459                   + " + 'k buffer pool in cache:' + CacheName"
460                   + " + ' is low (< 5%)' "
461                   + "from #tempbufpoolstats "
462                   + "where PoolUsed * 100.0 / AllocatedKB < 5.0"
463                   + " and CacheName like '" + @cachename + "'"
464                   + " order by IOBufferSize desc"
465               exec (@exec_str)
466   
467               select @tmp_grp = "buffer_"
468                   + convert(varchar(4), @cache_id)
469   
470               if (@num_engines > 1 and (@spinlock_contention >= 10 or @spinlock_contention < 0))
471               begin /* { */
472                   select @reco = "Consider using Named Caches or creating more cache partitions for '" + @cachename + "' or both."
473                   insert into #recommendations_tab(reco_col)
474                   values (@reco)
475               end /* } */
476   
477               /* recommendations for pool wash size */
478               select @tmp_int = SUM(value)
479               from #tempmonitors
480               where group_name = @tmp_grp
481                   and field_name like "bufgrab_dirty_%"
482   
483               if (@tmp_int is not null)
484               begin /* { */
485                   select @iter_cnt = @numKBperpg
486                   while (@iter_cnt <= 8 * @numKBperpg)
487                   begin /* { */
488                       /*
489                       **  build pool specific counter name,
490                       **  bufgrab_Nk (ie bufgrab_16k)
491                       */
492                       select @tmp_cntr = "bufgrab_dirty_"
493                           + convert(varchar(3), @iter_cnt) + "k"
494   
495                       select @tmp_int = value
496                       from #tempmonitors
497                       where group_name = @tmp_grp and
498                           field_name = @tmp_cntr
499   
500                       if @tmp_int != 0
501                       begin /* { */
502                           /*
503                           ** If We grabbed a buffer that was
504                           ** dirty from this pool consider
505                           ** increasing the wash size for
506                           ** this buffer pool
507                           */
508                           select @reco =
509                               "Consider increasing the 'wash size' of the " + ltrim(str(@iter_cnt, 3)) + "k pool for '" + @cachename + "'"
510                           insert into #recommendations_tab(reco_col) values (@reco)
511   
512                       end /* } */
513                       /* get next pool size (power of 2) */
514                       select @iter_cnt = @iter_cnt * 2
515                   end /* } */
516   
517               end /* } */
518   
519               /* recommendations for pool addition */
520               if (select value from #tempmonitors
521                       where group_name = @tmp_grp
522                           and field_name like "bufopt_lrgmass_reqd") > 0
523               begin /* { */
524                   select @reco =
525                       "Consider adding a large I/O pool for '"
526                       + @cachename + "'"
527                   insert into #recommendations_tab(reco_col)
528                   values (@reco)
529               end /* } */
530   
531               insert into #recommendations_tab(reco_col) values (@blankline)
532           end
533           print @blankline
534   
535           /*
536           ** We want to order the objects by LogicalReads and then while printing
537           ** order them by PhysicalReads. The ordering being 'desc' or 'ascending'
538           ** is decided by @display_order. To achieve this, we will select the
539           ** cut-off
540           ** value from the ordered table, which will be the value at row position
541           ** @top_n when @top_n > 0. In case of 'asc', we will delete all values
542           ** greater than this value and in case of 'desc', delete all values
543           ** lesser than this value.
544           */
545           set rowcount @top_n
546           select @cut_off_lreads = LogicalReads,
547               @tmpval = LogicalReads * @display_order
548           from #tempobjstats
549           where CacheName = @cachename
550           order by 2 desc
551   
552           set rowcount 0
553   
554           open objstats_cursor
555           fetch objstats_cursor into @obj_lreads,
556               @obj_preads, @obj_dbid,
557               @tab_id, @obj_ind_id,
558               @obj_cached_KB
559           /*
560           ** Printing object stats
561           */
562           select @rpt_line = "Object Statistics"
563           print @rpt_line
564   
565           print @separator_line
566           print @blankline
567   
568           while (@@sqlstatus = 0)
569           begin
570               select @db_name = db_name(@obj_dbid)
571   
572               select @exec_str = "exec " + @db_name +
573                   "..sp_dcachestats_obj_details "
574                   + str(@tab_id)
575                   + ", "
576                   + str(@obj_ind_id)
577               exec (@exec_str)
578   
579               fetch objstats_cursor into @obj_lreads,
580                   @obj_preads, @obj_dbid,
581                   @tab_id, @obj_ind_id,
582                   @obj_cached_KB
583   
584           end
585   
586           close objstats_cursor
587   
588           select @max_objname_len = max(datalength(db_name(dbid))
589               + datalength(owner)
590               + datalength(object_name(objid, dbid))
591               + datalength(index_name))
592               + 3
593           from #obj_details
594   
595           /* Print only when objects are present */
596           if @max_objname_len is not NULL
597           begin
598               select @exec_str = " select convert(varchar("
599                   + convert(varchar(3), @max_objname_len)
600                   + "), db_name(o.dbid) + '.' + owner + '.' + "
601                   + "isnull(object_name(o.objid, o.dbid),"
602                   + "convert(varchar(10), o.objid)) + case when "
603                   + " o.indid != 0 then '.' + o.index_name end) as "
604                   + @Object_col
605                   + ", str(LogicalReads * 1.0 / "
606                   + convert(varchar(6), @seconds)
607                   + ", 7, 2) as '" + @LReads_rename_col
608                   + "', str(PhysicalReads * 1.0 / "
609                   + convert(varchar(6), @seconds)
610                   + ", 7, 2) as '" + @PReads_rename_col
611                   + "', case when LogicalReads = 0 then '   n/a' else "
612                   + " str(100.0 * (LogicalReads - PhysicalReads)"
613                   + " / LogicalReads, 6, 2) end as '"
614                   + @Hit_Per_col
615                   + "', case when size_KB = 0 then '        n/a' else "
616                   + "str(CachedKB * 100.0 / size_KB, 11, 2) end "
617                   + "as '" + @Obj_Cached_Per_col
618                   + "', str(CachedKB * 100.0 / "
619                   + convert(varchar(19), @cache_size_in_KB)
620                   + ", 11, 2) as '"
621                   + @Cache_Occupied_Per_col
622                   + "' from #tempobjstats t, #obj_details o "
623                   + " where t.DBID = o.dbid and t.ObjectID = o.objid "
624                   + " and t.IndexID = o.indid "
625                   + " order by PhysicalReads desc, (LogicalReads * "
626                   + convert(varchar(2), @display_order)
627                   + ") desc"
628   
629               exec (@exec_str)
630   
631               print @blankline
632   
633               select @exec_str = " select convert(varchar("
634                   + convert(varchar(3), @max_objname_len)
635                   + "), db_name(o.dbid) + '.' + owner + '.' + "
636                   + " isnull(object_name(o.objid, o.dbid),"
637                   + "convert(varchar(10), objid)) + case when "
638                   + " o.indid != 0 then '.' + index_name end) as "
639                   + @Object_col
640                   + ", str(size_KB, 8) + ' Kb' as '"
641                   + @Obj_Size_col
642                   + "', str(CachedKB, 8) + ' Kb' as '"
643                   + @Size_In_Cache_col
644                   + "' from #tempobjstats t, #obj_details o "
645                   + " where t.DBID = o.dbid and t.ObjectID = o.objid "
646                   + " and t.IndexID = o.indid"
647                   + " order by PhysicalReads desc, (LogicalReads * "
648                   + convert(varchar(2), @display_order)
649                   + ") desc"
650               exec (@exec_str)
651   
652               /* 
653               ** truncate #obj_details so that the output is not repeated
654               ** for the previous cache' objects
655               */
656               truncate table #obj_details
657           end
658           else
659           begin
660               print "No Activity for objects in this interval"
661           end
662   
663           fetch named_cache_cursor into @cachename, @cache_partitions, @cache_id,
664               @logical_reads, @physical_reads
665       end
666       print @blankline
667       select @max_reco_len = isnull(max(datalength(reco_col)), 1)
668       from #recommendations_tab
669       select @exec_str = "if (select count(*) from #recommendations_tab "
670           + "where reco_col != '" + @blankline
671           + "' ) > 0 " + " begin select convert(varchar("
672           + convert(varchar(3), @max_reco_len)
673           + "), reco_col) as 'TUNING RECOMMENDATIONS' "
674           + "from #recommendations_tab end"
675       exec (@exec_str)
676   
677       print @blankline
678       print "LEGEND"
679       select @rpt_line = replicate('-', 6)
680       print @rpt_line
681       print "%1!		- number of logical reads per second, i.e. sum of cache & disk reads",
682           @LReads_rename_col
683       print "%1!		- number of physical reads per second i.e. disk reads",
684           @PReads_rename_col
685       print "%1!	- size of cache or buffer pool in Kilobytes",
686           @Run_Size_col
687       print "%1!	- number of cache partitions", @Cache_Partitions_col
688       print "%1!	- Percentage spinlock contention for the cache", @Spin_Contention_col
689       print "%1!		- ratio of hits to total searches", @Hit_Per_col
690       print "%1!		- ratio of pages referenced to Run Size", @Usage_Per_col
691       print @blankline
692       print "%1!	- wash size of buffer pool in Kilobytes", @Wash_col
693       print "%1!		- asynchronous prefetch %% for this buffer pool",
694           @APF_Per_col
695       print "%1!	- Ratio of buffers found in cache and brought in because",
696           @APF_Eff_Per_col
697       print "			  of APF to the number of APF disk reads performed"
698       print @blankline
699       print "%1!		- combination of db, owner, object and index name",
700           @Object_col
701       print "%1!	- size of the object in Kilobytes", @Obj_Size_col
702       print "%1!	- size occupied in cache in Kilobytes at the end of sample",
703           @Size_In_Cache_col
704       print "%1!	- Ratio of 'Size in Cache' to 'Obj Size'", @Obj_Cached_Per_col
705       print "%1!	- Ratio of 'Size in Cache' to 'Run Size' of cache",
706           @Cache_Occupied_Per_col
707       close named_cache_cursor
708       deallocate cursor named_cache_cursor
709       deallocate cursor objstats_cursor
710       return 0
711   

DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..tempcachestats 122
 PERR 6 Parsing Error Could not find definition for table tempdb..tempcachestats 139
 PERR 6 Parsing Error Could not find definition for table tempdb..tempbufpoolstats 158
 PERR 6 Parsing Error Could not find definition for table tempdb..tempcachedobjstats 182
 PERR 6 Parsing Error Could not find definition for table tempdb..tempobjstats 191
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @cache_size_in_KB: bigint = int 317
 MULT 4 Using literal database 'tempdb' tempdb..tempcachestats 122
 MULT 4 Using literal database 'tempdb' tempdb..tempcachestats 139
 MULT 4 Using literal database 'tempdb' tempdb..tempcachestats 140
 MULT 4 Using literal database 'tempdb' tempdb..tempbufpoolstats 158
 MULT 4 Using literal database 'tempdb' tempdb..tempbufpoolstats 159
 MULT 4 Using literal database 'tempdb' tempdb..tempcachedobjstats 182
 MULT 4 Using literal database 'tempdb' tempdb..tempobjstats 191
 MULT 4 Using literal database 'tempdb' tempdb..tempobjstats 192
 MULT 4 Using literal database 'tempdb' tempdb..tempobjstats 209
 MULT 4 Using literal database 'tempdb' tempdb..tempobjstats 217
 MULT 4 Using literal database 'tempdb' 277
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent}
115
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 115
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 142
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 143
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 163
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 164
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(7) 167
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(16) 171
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(8) 173
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(13) 175
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 200
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 201
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 202
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 214
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 215
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs datetime 222
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 318
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 319
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 VRUN 4 Variable is read and not initialized @cut_off_lreads 266
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 2
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MLCH 3 Char type with length>30 char(80) 9
 MLCH 3 Char type with length>30 char(80) 10
 MLCH 3 Char type with length>30 char(80) 11
 MLCH 3 Char type with length>30 char(1250) 12
 MNER 3 No Error Check should check @@error after select into 110
 MNER 3 No Error Check should check @@error after select into 135
 MNER 3 No Error Check should check @@error after update 145
 MNER 3 No Error Check should check @@error after select into 149
 MNER 3 No Error Check should check @@error after select into 179
 MNER 3 No Error Check should check @@error after select into 185
 MNER 3 No Error Check should check @@error after delete 225
 MNER 3 No Error Check should check @@error after update 228
 MNER 3 No Error Check should check @@error after insert 451
 MNER 3 No Error Check should check @@error after insert 473
 MNER 3 No Error Check should check @@error after insert 510
 MNER 3 No Error Check should check @@error after insert 527
 MNER 3 No Error Check should check @@error after insert 531
 MNER 3 No Error Check should check @@error after truncate 656
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 306
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 336
 MUCO 3 Useless Code Useless Brackets 369
 MUCO 3 Useless Code Useless Brackets 446
 MUCO 3 Useless Code Useless Brackets 470
 MUCO 3 Useless Code Useless Brackets 483
 MUCO 3 Useless Code Useless Brackets 486
 MUCO 3 Useless Code Useless Brackets 568
 MUIN 3 Column created using implicit nullability 104
 MUIN 3 Column created using implicit nullability 108
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachestats 122
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachestats 139
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachestats 140
 MZMB 3 Zombie: use of non-existent object tempdb..tempbufpoolstats 158
 MZMB 3 Zombie: use of non-existent object tempdb..tempbufpoolstats 159
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachedobjstats 182
 MZMB 3 Zombie: use of non-existent object tempdb..tempobjstats 191
 MZMB 3 Zombie: use of non-existent object tempdb..tempobjstats 192
 MZMB 3 Zombie: use of non-existent object tempdb..tempobjstats 209
 MZMB 3 Zombie: use of non-existent object tempdb..tempobjstats 217
 QAFM 3 Var Assignment from potentially many rows 317
 QAFM 3 Var Assignment from potentially many rows 495
 QAFM 3 Var Assignment from potentially many rows 546
 QJWT 3 Join or Sarg Without Index on temp table 233
 QJWT 3 Join or Sarg Without Index on temp table 351
 QNAJ 3 Not using ANSI Inner Join 114
 QNAJ 3 Not using ANSI Inner Join 139
 QNAJ 3 Not using ANSI Inner Join 158
 QNAJ 3 Not using ANSI Inner Join 209
 QNAJ 3 Not using ANSI Inner Join 231
 QNAJ 3 Not using ANSI Inner Join 346
 QNAO 3 Not using ANSI Outer Join 191
 QNUA 3 Should use Alias: Column parent should use alias co 110
 QNUA 3 Should use Alias: Column apf_percent should use alias cu 112
 QNUA 3 Should use Alias: Column memory_used should use alias cu 112
 QNUA 3 Should use Alias: Column parent should use alias co 115
 QPNC 3 No column in condition 266
 QPNC 3 No column in condition 268
 VUNU 3 Variable is not used @ret 16
 VUNU 3 Variable is not used @obj_size 34
 VUNU 3 Variable is not used @cache_occp_by_obj_per 38
 VUNU 3 Variable is not used @obj_hit_per 39
 CRDO 2 Read Only Cursor Marker (has for read only clause) 247
 CRDO 2 Read Only Cursor Marker (has for read only clause) 261
 MDYS 2 Dynamic SQL Marker 441
 MDYS 2 Dynamic SQL Marker 465
 MDYS 2 Dynamic SQL Marker 577
 MDYS 2 Dynamic SQL Marker 629
 MDYS 2 Dynamic SQL Marker 650
 MDYS 2 Dynamic SQL Marker 675
 MSUB 2 Subquery Marker 520
 MTR1 2 Metrics: Comments Ratio Comments: 9% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 64 = 63dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 364 2
 PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 110

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#tempcachedobjstats (1) 
reads table master..syscurconfigs (1)  
read_writes table tempdb..#obj_details (1) 
read_writes table tempdb..#recommendations_tab (1) 
read_writes table tempdb..#tempbufpoolstats (1) 
reads table tempdb..#tempmonitors (1) 
read_writes table tempdb..#tempcachestats (1) 
reads table master..sysconfigures (1)  
read_writes table tempdb..#tempobjstats (1) 
read_writes table tempdb..#syscacheconfig (1) 

CALLERS
called by proc sybsystemprocs..sp_sysmon_analyze_mda  
   called by proc sybsystemprocs..sp_sysmon