What can data cache contain besides pages from physical disk?

Clash Royale CLAN TAG#URR8PPP

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

While setting statistics io option ON, I see this result

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?

Thanks.

sql-server cache statistics-io

share|improve this question

asked 1 hour ago

Eleonora Grigoryan

35311

add a comment | 

up vote
1
down vote

favorite

While setting statistics io option ON, I see this result

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?

Thanks.

sql-server cache statistics-io

share|improve this question

asked 1 hour ago

Eleonora Grigoryan

35311

add a comment | 

up vote
1
down vote

favorite

up vote
1
down vote

favorite

While setting statistics io option ON, I see this result

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?

Thanks.

sql-server cache statistics-io

share|improve this question

asked 1 hour ago

Eleonora Grigoryan

35311

While setting statistics io option ON, I see this result

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?

Thanks.

sql-server cache statistics-io

sql-server cache statistics-io

share|improve this question

asked 1 hour ago

Eleonora Grigoryan

35311

share|improve this question

asked 1 hour ago

Eleonora Grigoryan

35311

share|improve this question

share|improve this question

asked 1 hour ago

Eleonora Grigoryan

35311

asked 1 hour ago

Eleonora Grigoryan

35311

asked 1 hour ago

Eleonora Grigoryan

35311

35311

add a comment | 

add a comment | 

1 Answer
1

active

oldest

votes

up vote
2
down vote



accepted

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.

share|improve this answer

edited 38 mins ago

answered 1 hour ago

Shanky

13.3k31939

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks for the great explanation !!
  – Eleonora Grigoryan
  59 mins ago
  

  
  
  
  


• 
  
  
  

  
  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).
  – hot2use
  55 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @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
  52 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  When are read ahead reads issued? - Paul White on TechNet (semi-official)
  – hot2use
  49 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  I got the information from old technet link and added it into the answer
  – Shanky
  44 mins ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219610%2fwhat-can-data-cache-contain-besides-pages-from-physical-disk%23new-answer', 'question_page');

);

Post as a guest

Name Email

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
2
down vote



accepted

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.

share|improve this answer

edited 38 mins ago

answered 1 hour ago

Shanky

13.3k31939

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks for the great explanation !!
  – Eleonora Grigoryan
  59 mins ago
  

  
  
  
  


• 
  
  
  

  
  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).
  – hot2use
  55 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @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
  52 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  When are read ahead reads issued? - Paul White on TechNet (semi-official)
  – hot2use
  49 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  I got the information from old technet link and added it into the answer
  – Shanky
  44 mins ago
  

  
  
  
  

add a comment | 

up vote
2
down vote



accepted

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.

share|improve this answer

edited 38 mins ago

answered 1 hour ago

Shanky

13.3k31939

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks for the great explanation !!
  – Eleonora Grigoryan
  59 mins ago
  

  
  
  
  


• 
  
  
  

  
  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).
  – hot2use
  55 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @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
  52 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  When are read ahead reads issued? - Paul White on TechNet (semi-official)
  – hot2use
  49 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  I got the information from old technet link and added it into the answer
  – Shanky
  44 mins ago
  

  
  
  
  

add a comment | 

up vote
2
down vote



accepted

up vote
2
down vote



accepted

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.

share|improve this answer

edited 38 mins ago

answered 1 hour ago

Shanky

13.3k31939

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.

share|improve this answer

edited 38 mins ago

answered 1 hour ago

Shanky

13.3k31939

share|improve this answer

share|improve this answer

edited 38 mins ago

edited 38 mins ago

edited 38 mins ago

answered 1 hour ago

Shanky

13.3k31939

answered 1 hour ago

Shanky

13.3k31939

answered 1 hour ago

Shanky

13.3k31939

13.3k31939

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks for the great explanation !!
  – Eleonora Grigoryan
  59 mins ago
  

  
  
  
  


• 
  
  
  

  
  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).
  – hot2use
  55 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @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
  52 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  When are read ahead reads issued? - Paul White on TechNet (semi-official)
  – hot2use
  49 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  I got the information from old technet link and added it into the answer
  – Shanky
  44 mins ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks for the great explanation !!
  – Eleonora Grigoryan
  59 mins ago
  

  
  
  
  


• 
  
  
  

  
  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).
  – hot2use
  55 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  @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
  52 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  When are read ahead reads issued? - Paul White on TechNet (semi-official)
  – hot2use
  49 mins ago
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  I got the information from old technet link and added it into the answer
  – Shanky
  44 mins ago
  

  
  
  
  

Thanks for the great explanation !!
– Eleonora Grigoryan
59 mins ago

Thanks for the great explanation !!
– Eleonora Grigoryan
59 mins ago

2

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).
– hot2use
55 mins ago

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).
– hot2use
55 mins ago

@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
52 mins ago

@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
52 mins ago

When are read ahead reads issued? - Paul White on TechNet (semi-official)
– hot2use
49 mins ago

When are read ahead reads issued? - Paul White on TechNet (semi-official)
– hot2use
49 mins ago

1

1

I got the information from old technet link and added it into the answer
– Shanky
44 mins ago

I got the information from old technet link and added it into the answer
– Shanky
44 mins ago

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219610%2fwhat-can-data-cache-contain-besides-pages-from-physical-disk%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email