bytes per i/o

In SQL server 2000 it recommends configuring 64 kb stripes when setting
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?
I'm not sure what recommendation you've been reading (I normally leave
the striping configuration up to the RAID controller; the RAID vendor
usually has the best idea about best practices for their RAID sets).
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only 1
byte on the page has changed, SQL Server has to write the entire 8K page
to disk (and also create one or more log records for the modification).
Similarly, when you read a single integer value, for example, from 1 row
in a table, even though that may only account for 1, 2, 4 or 8 bytes,
depending on the datatype, the entire page is read from disk and loaded
into memory (if it's not already cached). So each I/O ought to
represent 8K.
*mike hodgson*
http://sqlnerd.blogspot.com
tbone wrote:

>In SQL server 2000 it recommends configuring 64 kb stripes when setting
>up raid arrays. So technically each I/O is 64 kb. But when I am
>monitoring the I/O writes and I/O write bytes the numbers don't add up
>(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
>I/O write bytes) Is this most likely because each write doesn't fill up
>the 64 kb stripe? If so why not? Probably something to do with data
>pages being 8kb each and only filling up each page half way or
>something?
>
>
|||SQL Server does more than just 8K I/Os. For transaction log writes, a write
could be as small as a sector (i.e. 256 bytes) or sa large as 64K. For
checkpoints, a write may be much larger than 8K (from 8K to 64K). For log
backups, you may yet see a different I/O size. For bulk inserts, a write can
be up to 128K. In addition, DBCC DBREINDEX and restore are often not 8K
writes.
On most systems, because of a multiplicity of activities, you can hardly
expect to see a single I/O size in terms of write bytes/sec, and the counter
values taken at the drive can fluctuate wildly. If you want to observe the
block sizes of SQL Server I/Os, you would need to carefully control the
environment and try to make sure only one type of SQL Server I/Os is taking
place, ideally on an isolated drive.
Linchi
"Mike Hodgson" wrote:

> I'm not sure what recommendation you've been reading (I normally leave
> the striping configuration up to the RAID controller; the RAID vendor
> usually has the best idea about best practices for their RAID sets).
> However, when SQL Server performs a single read or a single write it is
> always 1 single, whole, 8K page that is read or written. Even if only 1
> byte on the page has changed, SQL Server has to write the entire 8K page
> to disk (and also create one or more log records for the modification).
> Similarly, when you read a single integer value, for example, from 1 row
> in a table, even though that may only account for 1, 2, 4 or 8 bytes,
> depending on the datatype, the entire page is read from disk and loaded
> into memory (if it's not already cached). So each I/O ought to
> represent 8K.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> tbone wrote:
>