For queries that scan a table’s data pages or the leaf level of an unclustered index (covered queries), the Adaptive Server query processor chooses one of two cache replacement strategies: the fetch-and-discard, most-recently used (MRU) strategy or the least recently used (LRU) strategy.
See “Heaps, I/O, and cache strategies” on page 74 in Performance and Tuning Series: Physical Database Tuning.
The query processor may choose the MRU strategy for:
Any query that performs table scans
A range query that uses a clustered index
A covered query that scans the leaf level of a nonclustered index
An inner table in a nested-loop join, if the inner table is larger than the cache
The outer table of a nested-loop join, since it needs to be read only once
Both tables in a merge join.
To affect the cache strategy for objects:
Specify lru or mru in a select, update, or delete statement
Use sp_cachestrategy to disable or reenable the mru strategy
If you specify the MRU strategy, and a page is already in the data cache, the page is placed at the MRU end of the cache, rather than at the wash marker.
Specifying the cache strategy affects only data pages and the leaf pages of indexes. Root and intermediate pages always use the LRU strategy.
