- Is the TOTAL number of physical reads a query did = [physical reads] + [read-ahead reads]?
- Why sometimes we have [physical reads]=0 while the [read-ahead reads]>0? what does that mean?
- How many pages SQL Server reads in one physical read? ... if it is one to one, then
- Why when "select * from aTable", the number of [physical reads] and/or [read-ahead reads] does not equal the table's number of pages? although the cache was cleared before executing the query.
- why sometimes [logical reads] is greater than [physical reads] + [read-ahead reads], and even greater than the total number of pages of the table?
Add a comment
|
1 Answer
Is the TOTAL number of physical reads a query did = [physical readsc] + [read-ahead reads]?
No. These are calculated separately.
physical reads: Number of pages read from disk-Running thread go to runnable state and wait for the IO to finish.
read-ahead reads:
The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.There are two kinds of read-ahead: one for data pages and one for index pages.
Why sometimes we have [physical reads]=0 while the [read-ahead reads]>0? what does that mean?
As mentioned before read ahead pages are not included in physical read. This means all pages required are already in cache. Either the pages already existed or placed in cache by read ahead mechanism.
How many pages SQL Server reads in one physical read? ... if it is one to one, then
It is not one to one. It will vary. See reference section below.
Why when "select * from aTable", the number of [physical reads] and/or [read-ahead reads] does not equal the table's number of pages? although the cache was cleared before executing the query.
As explained a physical read and read ahead can read more than one page at a time.
The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache. If any of the pages in the range are already present in the buffer cache, the corresponding page from the read will be discarded when the read completes. The range of pages may also be "trimmed" from either end if the corresponding pages are already present in the cache.
why sometimes [logical reads] is greater than [physical reads] + [read-ahead reads]
Because your pages are already in buffer so reads in this case means logical read and you do not need (or you need less than) physical read + read ahead read.
and even greater than the total number of pages of the table?
It is possible to read same page more than once while reading from cache. Think about seek operation on an index. If you have to do 10 seeks, you will always do 10 logical read on the root page and possibly 10 logical reads on the each intermediate level.
Reference:
answered Nov 18, 2017 at 12:14
-
3Just want to add that SQL Server will perform read-aheads of up to 4MB in SQL 2016 and later. I learned this with an XE trace of file_read_completed events. Commented Nov 18, 2017 at 13:34