Cache Search, Hit, and Miss Information

Number of 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 that you understand how applications use each cache. In caches that are created to hold specific objects such as indexes or lookup 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.

Cache hits and misses 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

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

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 indicates a large percentage of cache hits found in the wash section, it may mean the wash area is too big. However, a large number of hits 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, and is 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.

See “Buffer Wash Behavior” for more information.

If queries on tables in the cache use a “fetch-and-discard” strategy for a non-APF I/O, the first cache hit for a page finds it in the wash. The buffer 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.

See “Cache Strategy” for information.

You can change the wash size. If you make the wash size smaller, run sp_sysmon again under fully loaded conditions and check the output for “Grabbed Dirty” values greater than 0

See “Cache Turnover”.


Cache Misses

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

Summarizes cache search activity. The “Found in Wash” data is a subcategory of the “Cache Hits” number and is not used in the summary calculation.