set statistics io on

[xperre van wilrijk]

Hello,

I'm a little bit confused about the following message I get

Table 'aTBLTest1'. Scan count 1, logical reads 52, physical reads 0,
read-ahead reads 3009. (SQL2008R2)

I assumed read-ahead should be 0 if physical reads is 0, but this
message tells me that's not true?

Any idea?
Thx
Peter.

[Jeroen Mostert]

Post by xperre van wilrijk
Hello,
I'm a little bit confused about the following message I get
Table 'aTBLTest1'. Scan count 1, logical reads 52, physical reads 0,
read-ahead reads 3009. (SQL2008R2)
I assumed read-ahead should be 0 if physical reads is 0, but this
message tells me that's not true?

No. "Physical reads" is basically "non read-ahead physical reads". In other
words, read-ahead reads don't count towars physical reads (although they do
hit the disk).

[xperre van wilrijk]

Thx Mr Jeroen,

but this still stays confusing to me.

I assume 1 read is 1 page (8k).
I assume read-ahead is physical read, actually reading pages on disk
that I don't need for the query, but that might be needed later on ...
meaning SQL decides to read some extra pages into RAM, since disk
access/physical read is required to execute the query. So I assume :
since disk access is required for the query, it's good to get some
additional pages in RAM, since the performance penalty is higher if a
request for these pages should follow soon.

So, if I run a query that needs 1 page and that page is in RAM, so I
don't need any physical read, then physical reads = 0, why then should
SQL Server decide to do disk access (performance penalty) for pages
that I don't need to get results for this query. I should say SQL
Server is stupid here, but I guess it's more probable that I don't
completly understand the concept ;-)

Thx
Peter.

[Jeroen Mostert]

Post by xperre van wilrijk
Thx Mr Jeroen,

That's my first name, so you can drop the "mister". :-)

Post by xperre van wilrijk
but this still stays confusing to me.
I assume 1 read is 1 page (8k).

Physical reads, yes. Read-ahead reads, I think so, but I'm not sure.

Post by xperre van wilrijk
I assume read-ahead is physical read, actually reading pages on disk
that I don't need for the query, but that might be needed later on ...
meaning SQL decides to read some extra pages into RAM, since disk
since disk access is required for the query, it's good to get some
additional pages in RAM, since the performance penalty is higher if a
request for these pages should follow soon.

That's exactly the reasoning behind read-ahead reads. A slight correction
though: the pages that are read *are* used for the query, not because they
might be useful later on for an unrelated query. It's possible that the
read-ahead read gets more pages than are actually needed to satisfy the
query, but then that's a coincidence.

Post by xperre van wilrijk
So, if I run a query that needs 1 page and that page is in RAM, so I
don't need any physical read, then physical reads = 0, why then should
SQL Server decide to do disk access (performance penalty) for pages
that I don't need to get results for this query. I should say SQL
Server is stupid here, but I guess it's more probable that I don't
completly understand the concept ;-)

If you have a query where physical reads = 0 but read-ahead reads <> 0, it
simply means that all pages were read using read-ahead reads. If a query
needed only one page and that page is in RAM, you would see physical reads =
read-ahead reads = 0. SQL Server will not issue physical or read-ahead reads
if all pages to be read are in memory.

[xperre van wilrijk]

Thx a lot! I think I understand now.

I conclude "Read-ahead reads" and "physical reads" are both physical
reads, but using an another alghoritm/system! So, it should have less
confusing for me if MS developers would have written .... "regular
physical reads #", "read ahead physical reads #". You can have one
without the other and you can have both when executing a query.

In this link I read now ...
http://technet.microsoft.com/library/Cc966464 ... read-ahead occur
with 64-KB I/Os, which provide higher disk throughput potential for
the disk subsystem than 8-KB I/Os do. When a large amount of data must
be retrieved from SQL Server, read-ahead is the best way to do it.
Read Ahead Manager benefits from the simpler and more efficient Index
Allocation Map (IAM) storage structure.

One strange thing I see now in my performance tests ...
I have a table with 5.000.000 rows with a clustered index on a
identity column.
-- *1
select top 100 * from table order by id asc most of the time results
in 0 physical reads and 3000 to 5000 read ahead reads.
-- *2
select * from table where id between 28000 and 38000 most of the time
results in 1 physical read and about 3333 read ahead reads.
-- *3
select * from table where id between 2500000 and 2500100 most of the
time results in 1 pysical read and about 33 read ahead reads
--

Since I have about 3 rows per page ...
*3 needs 33 + 1 page (does not read more than needed)

*2 needs 3333 + 1 page (does not read more than needed)

*1 starts at first row of the clustered index ... doesn't need much
traveling to know the location of the first page and then reads much
more than required, since I just need 34 pages and it reads +3000
pages ... meaning it gets about +10.000 rows in RAM of which I only
need 100 for my query.

That seems a lot to me ... reading 24Mb while I just needed about
250Kb being read to have the required results.
This means SQL kicks out +2970 other pages out of memory. OK,
possibly I'll need the pages being read-ahead faster than then the
ones being kicked out ... but this also means 24Mb of (not required)
traffic between our SAN - switches - Virtual host to virtual guest.
Probably okay for a physical server with local disks, but I wonder
whether this is beneficial (regarding performance) in our highly
virtualized infrastructure ...

Kind regards
Peter

[Jeroen Mostert]

Post by xperre van wilrijk
I conclude "Read-ahead reads" and "physical reads" are both physical
reads, but using an another alghoritm/system! So, it should have less
confusing for me if MS developers would have written .... "regular
physical reads #", "read ahead physical reads #". You can have one
without the other and you can have both when executing a query.

Yes.

Post by xperre van wilrijk
In this link I read now ...
http://technet.microsoft.com/library/Cc966464 ... read-ahead occur
with 64-KB I/Os

That page deals with SQL Server 7.0, which is ancient. A more up-to-date
page is at
http://msdn.microsoft.com/en-us/library/ms191475%28v=sql.105%29.aspx, which
says SQL Server can read up to 512 KB at a time. This number is likely to
change with releases.

Post by xperre van wilrijk
One strange thing I see now in my performance tests ...
I have a table with 5.000.000 rows with a clustered index on a
identity column.
-- *1
select top 100 * from table order by id asc most of the time results
in 0 physical reads and 3000 to 5000 read ahead reads.
-- *2
select * from table where id between 28000 and 38000 most of the time
results in 1 physical read and about 3333 read ahead reads.
-- *3
select * from table where id between 2500000 and 2500100 most of the
time results in 1 pysical read and about 33 read ahead reads
--
Since I have about 3 rows per page ...

You have very big rows. This may cause SQL Server to overestimate the number
of pages actually needed. What does the execution plan say about "estimated
row size"?

Post by xperre van wilrijk
*3 needs 33 + 1 page (does not read more than needed)
*2 needs 3333 + 1 page (does not read more than needed)
*1 starts at first row of the clustered index ... doesn't need much
traveling to know the location of the first page and then reads much
more than required, since I just need 34 pages and it reads +3000
pages ... meaning it gets about +10.000 rows in RAM of which I only
need 100 for my query.
That seems a lot to me ... reading 24Mb while I just needed about
250Kb being read to have the required results.
This means SQL kicks out +2970 other pages out of memory.

Only if the buffer pool is actually full. And even so, those 2970 pages are
chosen to represent data unlikely to be accessed soon.

Post by xperre van wilrijk
OK, possibly I'll need the pages being read-ahead faster than then the
ones being kicked out ... but this also means 24Mb of (not required)
traffic between our SAN - switches - Virtual host to virtual guest.
Probably okay for a physical server with local disks, but I wonder
whether this is beneficial (regarding performance) in our highly
virtualized infrastructure ...

If your queries are only ever reading 100 rows, the overhead shouldn't
matter anyway. If, on the other hand, that infrastructure isn't just there
to look pretty but to actually serve up data, it's unlikely that you get
unwarranted overhead. That said, I'm not an expert in this area; I classify
hardware as "fast enough for my purposes" and "not fast enough for my
purposes" (and I don't have to order it either :-)).