For queries that scan a table’s data pages or the leaf level of a nonclustered index (covered queries), the Adaptive Server optimizer chooses one of two cache replacement strategies: the fetch-and-discard (MRU) strategy or the LRU strategy.
See “Overview of cache strategies” on page 174 in the book Performance and Tuning: Basics for more information about these strategies.
The optimizer may choose the fetch-and-discard (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
You can affect the cache strategy for objects:
By specifying lru or mru in a select, update, or delete statement
By using sp_cachestrategy to disable or reenable mru strategy
If you specify 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.
