The Cache Wizard section output contains three main sections for each cache. This is followed by a recommendation section and a legend section at the end of the report.:
Cache section – provides summary statistics for a specific cache:
default data cache ------------------ Run Size: 100.00 Mb Usage%: 2.86 LR/sec: 41.10 PR/sec: 22.57 Hit%: 45.09 Cache Partitions: 4 Spinlock Contention%: 0.00
Usage% – each time a set of pages is brought into the cache, Adaptive Server tracks whether that page is referenced (used). When the page is removed from the cache, this count is reduced. Usage% is the current usage of the cache as a percentage of the cache size.
LR/sec – a logical read is any read from the cache (a hit) or a disk read (a miss). LR/sec is the number of logical reads in the cache during the interval divided by the length of the sample interval.
PR/sec – a physical read is a read from disk (miss). PR/sec is the number of physical reads in the cache during the interval divided by the sample interval.
Hit% – ratio of hits to total cache reads, such as the ratio of (LR/sec minus PR/sec) to LR/sec.
Buffer pool section – breaks down the cache section statistics into the various buffer pools in the cache.
Buffer Pool Information ----------------------------------------------------------------------- IO Size Wash Size Run Size APF% LR/sec PR/sec Hit% APF-Eff% Usage% ------- ---------- ------- ------ ------- -------- ------ -------- ------ 4 Kb 3276 Kb 16.00 Mb 10.00 0.47 0.13 71.43 n/a 0.20 2 Kb 17200 Kb 84.00 Mb 10.00 40.63 22.43 44.79 n/a 3.37
APF-Eff% – number of pages asynchronous prefetch uses, and the total number of pages asynchronous prefetch brings in.
Usage% tracks whether a page brought into the buffer pool is referenced or not, providing the ratio of the pages referenced in the buffer pool to the run size of the buffer pool.
Object section – reports statistics on the objects occupying the cache at the end of the interval. Use the topN parameter to limit the size of this section. Objects display in the ascending order of PR/sec.
Object Statistics ----------------------------------------------------------------------- Object LR/sec PR/sec Hit% Obj_Cached% Cache_Occp% ------------------------------- ------- ------- ------ ----------- ----- empdb.dbo.t1 0.57 0.30 47.06 56.25 0.02 empdb.dbo.t2 0.30 0.30 0.00 56.25 0.02 empdb.dbo.t3 0.30 0.30 0.00 56.25 0.02 Object Obj Size Size in Cache ------------------------------- ----------- ------------ empdb.dbo.t1 32 Kb 18 Kb empdb.dbo.t2 32 Kb 18 Kb empdb.dbo.t3 32 Kb 18 Kb
Recommendations section – gives a set of recommendations, where applicable, based on the data collected in the sample interval:
The various recommendations are as follows: Usage% for 'default data cache' is low (< 5%) Usage% for 4k buffer pool in cache:default data cache is low (< 5%) Consider using Named Caches or creating more cache partitions for 'default data cache' or both Consider increasing the 'wash size' of the 2k pool for 'default data cache' Consider adding a large I/O pool for 'default data cache'
Legend – explains the various terms used in the output. Some of the terms from the output are explained here in greater detail.
