Cache search, hit, and miss
information

This section displays the number hits and misses and the total
number of searches for this cache. Cache hits are roughly comparable
to the logical reads values reported by statistics io;
cache misses are roughly equivalent to physical reads. sp_sysmon always
reports values that are higher than those shown by statistics
io, since sp_sysmon also reports
the I/O for system tables, log pages, OAM pages and other
system overhead.

Interpreting
cache hit data requires an understanding of how the application uses
each cache. In caches that are created to hold specific objects
such as indexes or look up tables, cache hit ratios may reach 100%.
In caches used for random point queries on huge tables, cache hit
ratios may be quite low but still represent effective cache use.

This data can also help you to determine if adding more memory
would improve performance. For example, if “Cache Hits” is
high, adding memory probably would not help much.

Cache
hits

“Cache Hits” reports the number of times
that a needed page was found in the data cache. “% of
total” reports the percentage of cache hits compared to
the total number of cache searches.

Found in wash

The
number of times that the needed page was found in the wash section
of the cache. “% of total” reports the
percentage of times that the buffer was found in the wash area as
a percentage of the total number of hits. If the data indicate a large
percentage of cache hits found in the wash section, it may mean
the wash area is too big. It is not a problem for caches that are
read-only or that have a low number of writes.

A large wash section might lead to increased physical I/O
because Adaptive Server initiates a write on all dirty pages as they
cross the wash marker. If a page in the wash area is written to
disk, then updated a second time, I/O has been wasted.
Check to see whether a large number of buffers are being written at
the wash marker.

If queries on tables in the cache use “fetch-and-discard” strategy
for a non-APF I/O, the first cache hit for a page finds
it in the wash. The buffers is moved to the MRU end of the chain,
so a second cache hit soon after the first cache hit will find the
buffer still outside the wash area.

Cache misses

“Cache
Misses” reports the number of times that a needed page
was not found in the cache and had to be read from disk. “% of
total” is the percentage of times that the buffer was not
found in the cache as a percentage of the total searches.

Total cache searches

This row
summarizes cache search activity. Note that the “Found
in Wash” data is a subcategory of the “Cache Hits” number
and it is not used in the summary calculation.