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 (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 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 MergeJoin.
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 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.
