For queries that scan a table's data pages or the leaf level of a nonclustered index (covered queries), the SQL Server optimizer chooses one of two cache replacement strategies: the fetch-and-discard (MRU) strategy or the LRU strategy. See "Overview of Cache Strategies" for more information about these strategies.
The optimizer usually chooses fetch-and-discard (MRU) strategy for:
Any query that 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 join, if the inner table is larger than the cache
The outer table of a join, since it needs to be read only once
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 re-enable 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 only affects data pages and the leaf pages of indexes. Root and intermediate pages always use the LRU strategy.
You can use lru or mru (fetch-and-discard) in a select, delete, or update command to specify the I/O size for the query:
select select_list
from table_name
(index index_name prefetch size [lru|mru])
[, table_name ...]
where ...
delete from table_name (index index_name
prefetch size [lru|mru]) ...
update table_name set col_name = value
from table_name (index index_name
prefetch size [lru|mru]) ...
This query adds the LRU replacement strategy to the 16K I/O specification:
select au_lname, au_fname, phone
from authors (index au_names prefetch 16 lru)
For more discussion of specifying a prefetch size, see "Specifying I/O Size in a Query".