Read Ahead Read and Scan Count
By : Kasim Wirama, MCDBA If you turn STATISTICS IO on to get know access to underlying tables in a query, you have information logical read, physical read, read ahead read and scan count. Besides logical read and physical read, other interesting information are read ahead read and scan count. What information that they can give? Let take a look.
Read ahead reads indicates number of pages that are read into data cache using read ahead mechanism. Notice that if read ahead reads is high, probably the physical read is low and buffer cache hit ratio is high. In this situation, additional memory into SQL Server benefits for read ahead read. It is a good indication, but it is better that the data will be in cache from previous execution so no additional data pages requested for read ahead activity. Read ahead read is an asynchronous operation; it means that it doesn’t block the query to complete as long as the query has the pages when needed. Read ahead mechanism has relation to full or partial table scan. In the scan, the IAM table is accessed in disk order to determine which extents are belonged to underlying database object. If you put eight files in a filegroup, the read ahead reads will keeps those files busy instead of sequentially processing those files.
Scan count indicates the number of times a table is accessed. In nested loop, even though scan count of inner table is one but logical read of inner table is calculated as multiplication of number scan count of an outer table is accessed with number of logical read of corresponding inner table. For merge or hash join, the scan count is one, but memory for those joins are bigger than nested loop join. The amount of memory that is consumed can be inspected in column granted_query_memory in DMV sys.dm_exec_requests, but it is only current session, if you want to see all sessions, you inspect column memory_usage in DMV sys.dm_exec_sessions.