If you are testing a query and checking its I/O, and you execute the same query a second time, you may get surprising physical read values, especially if the query uses LRU replacement strategy.
The first execution reports a high number of physical reads; the second execution reports 0 physical reads.
The first time you execute the query, all the data pages are read into cache and remain there until other server processes flush them from the cache. Depending on the cache strategy used for the query, the pages may remain in cache for a longer or shorter period of time.
If the query uses the fetch-and-discard (MRU) cache strategy, the pages are read into the cache at the wash marker.
In small or very active caches, pages read into the cache at the wash marker are flushed quickly.
If the query uses LRU cache strategy to read the pages in at the top of the MRU end of the page chain, the pages remain in cache for longer periods of time.
During actual use on a production system, a query can be expected to find some of the required pages already in the cache, from earlier access by other users, while other pages need to be read from disk. Higher levels of indexes, in particular, tend to be frequently used, and tend to remain in the cache.
If you have a table or index bound to a cache that is large enough to hold all the pages, no physical I/O takes place once the object has been read into cache.
However, during query tuning on a development system with few users, you may want to clear the pages used for the query from cache in order to see the full physical I/O needed for a query. You can clear an object’s pages from cache by:
Changing the cache binding for the object:
If a table or index is bound to a cache, unbind it, and rebind it.
If a table or index is not bound to a cache, bind it to any cache available, then unbind it.
You must have at least one user-defined cache to use this option.
If you do not have any user-defined caches, you can execute a sufficient number of queries on other tables, so that the objects of interest are flushed from cache. If the cache is very large, this can be time-consuming.
The only other alternative is rebooting the server.
For more information on testing and cache performance, see “Testing data cache performance” on page 218 Performance and Tuning: Basics.
