Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference between multiple primary and secondary files..

Hi all..!

If I want to split an SQL DB into several physical files (as its 500GB
disk ran out of space, won't even run shrinks any more, and we bought
another 500GB disk to add to the PC)
then what is the difference between:
Adding another File to the primary group which will reside on the new
group;
Adding another file in another group.
We do not want to set any db objects (Tables, indexes)
to a secondary file, as this will involve lengthy data moving
operations. We would like the DB to continue working from where it is
utilizing the added space in a contigous (striped) manner.

Will striping occur in both cases? as I understand striping it means
that our stuck SQL Server will awake back to life as it will now have
500GB more data for its DB, even though we haven't set any of its
objects (tables, indexes) to explicitly use the secondary NDF file on
the new disk?
or will it only utilize the new space if we set some objects to reside
on that NDF?

for example if we run large queries which crash now (due to lack of
space) when we add the second drive will they start to work as the
process will grow striped from the full drive to the new drive, even if
all the queries' source tables are all still set to the old drive?

Thanks for any replies?(developmental2@.walla.com) writes:
> If I want to split an SQL DB into several physical files (as its 500GB
> disk ran out of space, won't even run shrinks any more, and we bought
> another 500GB disk to add to the PC)
> then what is the difference between:
> Adding another File to the primary group which will reside on the new
> group;
> Adding another file in another group.

If you add another filegroup, you need to move objects, as objects
below to a filegroup. Since you don't want to that, you should add
a secondary file to the primary filegroup.

I don't have much experience of secondary files myself, but I would
expect SQL Server start to spill over the new file, as soon as it is
available.

If you want to have certainty, it could be a good idea to set up a
small-size test, before you go ahead with the big database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Diagnosing Memory Problems?

I am consistantly getting Physical Memory Usage High errors. My Buffer Cache
is running at 99% and the Server Memory is at 87%. Any thoughts on what's c
ausing this?I am consistantly getting Physical Memory Usage High errors. My Buffer Cache
is running at 99% and the Server Memory is at 87%. Any thoughts on what's c
ausing this?