Effects of caching on reads

If you are testing a query and checking the query’s I/O, and you execute the same query a second time, you may get surprising physical read values, especially if the query uses least recently used (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.

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 to see the full physical I/O needed for a query. You can clear an object’s pages from cache by:

For more information on testing and cache performance, see Chapter 5, “Memory Use and Performance” in Performance and Tuning Series: Basics.