Cache hit ratio for a single query

To see the cache hit ratio for a single query, use set statistics io on to see the number of logical and physical reads, and set showplan on to see the I/O size used by the query.

Figure 5-4: Formula to compute cache hit ratio

Image shows a formula: the value for cache hit ration equals logical reads minus the number of physical reads times the number of pages, all divided by the number of logical reads.

With statistics io, physical reads are reported in I/O-size units. If a query uses 16K I/O, it reads 8 pages with each I/O operation.

If statistics io reports 50 physical reads, it has read 400 pages. Use showplan to see the I/O size used by a query.