While setting statistics io option ON, I see this result

enter image description here

physical_reads are 0 and read-ahead reads are 276 which means that 276 pages of that table which are on the disk drive were in data cache, but the whole number of reads from data cache is 794 as indicated in logical reads. So what is kept in data cache besides table pages from disk drive?



There are couple of things here so let me explain

Logical reads: A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache.

Read aheads: I would suggest you to read Reading Pages in SQL Server

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.

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.

Now to your question

physical_reads are 0 and read-ahead reads are 276 which means that 276 pages of that table which are on the disk drive were in data cache,

No that does not means just the 276 pages it can be much more than 276 actually it is always more than that. Read aheads read *more than required pages *and bring that pages into memory this is done for performance optimization to make sure all the pages are in cache and physical I/O can be avoided. A single read may bring upto 64 pages and this would be seen as one read.From Old BOL document Read-ahead requests are generally 128 pages for each group but can be as many as 1,024 pages when running Microsoft SQL Server Enterprise Edition.

From this Old Technet Article, for enterprise edition Read Ahead can be up to 1024 KB.

Logical reads are counted for single pages while read aheads are counted as one for chunk of pages.

  • 2
    Enterprise Editions support up to 1024 kB of read-ahead data. This has gone lost in the translation of the documentation from SQL Server 2008 (BOL) to SQL Server 201x (Microsoft Docs). – John aka hot2use Oct 9 '18 at 7:17
  • @hot2use If you have any valid source feel free to add it the answer and thanks for pointing out. I was under impression that this has increased but could not find any official doc. – Shanky Oct 9 '18 at 7:20
  • When are read ahead reads issued? - Paul White on TechNet (semi-official) – John aka hot2use Oct 9 '18 at 7:24
  • 1
    I got the information from old technet link and added it into the answer – Shanky Oct 9 '18 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.