I/O size

Hello,
Following is my understanding when sql needs anythings reads from disk it
request to Windows which do 64 k read even though sqlserver has only
requested single record' Am I right?
how sql handle the I/O request when it reads from RAM (data cache)?
Thanks.
--
FarhanI/O refers to an actual read from the I/O device. So if a data page is
in the SQL Server Data Cache, then there is no I/O.
So really, it is the other way 'round. During execution, data is
requested from the Storage Engine. The Storage Engine will check if the
required data page is in cache. If it is not, then the page is read from
disk (the I/O device) which will almost certainly cause I/O. It could
also trigger the storage engine into reading multiple consecutive pages
(Read Ahead) from disk and (temporarily) stored in the Data Cache.
Gert-Jan
Farhan wrote:
> Hello,
> Following is my understanding when sql needs anythings reads from disk it
> request to Windows which do 64 k read even though sqlserver has only
> requested single record' Am I right?
> how sql handle the I/O request when it reads from RAM (data cache)?
> Thanks.
> --
> Farhan|||Gert,
I am looking for the size of I/O id sqlserver misses the cache hit?
Here is what I got from search
"
IO size depends on what the operations is and we try to do a good job
of
balacing various facotrs to achieve optimal performance. The following
is
not a complete list but I hope it helps explain the situation:
Operation Random / Sequential Read / Write Size Range (per I/O)
========= =================== ============ ====================
OLTP - Log Sequential Write 512 bytes - 64KB
OLTP - Data Random Read/Write 8K
Bulk Insert Sequential Write 8~128 KB
Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
We use non-buffered IO on files so that means the windows operating
system
pass those directly to the disk subsystem. If you use RAID, then it
depends
on configuration parameters such as stripe size.
Wei Xiao
SQL Server Storage Engine Development
"
"Gert-Jan Strik" wrote:

> I/O refers to an actual read from the I/O device. So if a data page is
> in the SQL Server Data Cache, then there is no I/O.
> So really, it is the other way 'round. During execution, data is
> requested from the Storage Engine. The Storage Engine will check if the
> required data page is in cache. If it is not, then the page is read from
> disk (the I/O device) which will almost certainly cause I/O. It could
> also trigger the storage engine into reading multiple consecutive pages
> (Read Ahead) from disk and (temporarily) stored in the Data Cache.
> Gert-Jan
>
> Farhan wrote:
>|||I don't think there are exact numbers, because it will depend on the
situation, the logic is internal (to the Storage Engine) and might
change with every hotfix or service pack.
Why do you want to know the I/O size?
Because it is even more 'unpredictable' then the story below suggests,
because a data cache miss can trigger multiple Read Aheads. According to
Inside SQL Server 2000 (a book I can recommend)
" Up to 32 extents of read ahead are outstanding at a time.
Four extents (32 pages) at a time are read with a single 256-KB
scatter read. "
The book also describes the read ahead behavior with respect to multiple
files, and the enhancements of Enterprise Edition (versus Standard
Edition) such as the "Merry-Go-Round" optimization.
Gert-Jan
Farhan wrote:[vbcol=seagreen]
> Gert,
> I am looking for the size of I/O id sqlserver misses the cache hit?
> Here is what I got from search
> "
> IO size depends on what the operations is and we try to do a good job
> of
> balacing various facotrs to achieve optimal performance. The following
> is
> not a complete list but I hope it helps explain the situation:
> Operation Random / Sequential Read / Write Size Range (per I/O)
> ========= =================== ============ ====================
> OLTP - Log Sequential Write 512 bytes - 64KB
> OLTP - Data Random Read/Write 8K
> Bulk Insert Sequential Write 8~128 KB
> Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
> We use non-buffered IO on files so that means the windows operating
> system
> pass those directly to the disk subsystem. If you use RAID, then it
> depends
> on configuration parameters such as stripe size.
> --
> Wei Xiao
> SQL Server Storage Engine Development
> "
> "Gert-Jan Strik" wrote:
>