SQL Server Read/Write Request Size from the Disk IO Subsystem

16 Nov 2007 5:09 AM

Cqlboy

How does SQL Server determine what request size to make to the disk IO
subsystem? If you monitor in Perfmon the Avg Bytes Per Read/Write you'll see
that this can very between ~8K (min page size in SQL Server) up to ~300
KBytes during backups. But what about everything in between?

16 Nov 2007 5:19 AM

Linchi Shea

For data page writes (checkpoints or lazy writes), it depends on how many
pages are contiguous. If there are two contiguous pages, the I/O request
would be 16K. So it's not a constant.

Linchi

Show quoteHide quote

"Cqlboy" wrote:

> How does SQL Server determine what request size to make to the disk IO
> subsystem? If you monitor in Perfmon the Avg Bytes Per Read/Write you'll see
> that this can very between ~8K (min page size in SQL Server) up to ~300
> KBytes during backups. But what about everything in between?

16 Nov 2007 6:01 PM

Cqlboy

I've read before that SQL Server's read-ahead behaves similiarly. But what
dictates the request size? If SQL Server detected 8 contigious pages to read
would it request 64K for one read? What if it was 12 contigous pages? Is
the minimum Kbytes per request equal to 8Kbytes, which is SQL Servers minimum
page boundary? I've defined our Windows cluster size to be 64Kbytes for the
TempDB. All read/write requests are dominated in favor of 64Kbytes. This is
easily seen in Perfmon as well as Diskmon. But why? This understanding is
helpful determine precisely how I should layout my files/filegroups on our
SAN. Thanks. -Cqlboy

Show quoteHide quote

"Linchi Shea" wrote:

> For data page writes (checkpoints or lazy writes), it depends on how many
> pages are contiguous. If there are two contiguous pages, the I/O request
> would be 16K. So it's not a constant.
>
> Linchi
>
> "Cqlboy" wrote:
>
> > How does SQL Server determine what request size to make to the disk IO
> > subsystem? If you monitor in Perfmon the Avg Bytes Per Read/Write you'll see
> > that this can very between ~8K (min page size in SQL Server) up to ~300
> > KBytes during backups. But what about everything in between?

16 Nov 2007 10:39 PM

Linchi Shea

> I've read before that SQL Server's read-ahead behaves similiarly.

For read-ahead, I don't believe it's similar in that it doesn't need to test
for contiguous pages. Bob Dorr has much more detailed info on I/O request
sizes in
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

Linchi

Show quoteHide quote

"Cqlboy" wrote:

> I've read before that SQL Server's read-ahead behaves similiarly. But what
> dictates the request size? If SQL Server detected 8 contigious pages to read
> would it request 64K for one read? What if it was 12 contigous pages? Is
> the minimum Kbytes per request equal to 8Kbytes, which is SQL Servers minimum
> page boundary? I've defined our Windows cluster size to be 64Kbytes for the
> TempDB. All read/write requests are dominated in favor of 64Kbytes. This is
> easily seen in Perfmon as well as Diskmon. But why? This understanding is
> helpful determine precisely how I should layout my files/filegroups on our
> SAN. Thanks. -Cqlboy
>
> "Linchi Shea" wrote:
>
> > For data page writes (checkpoints or lazy writes), it depends on how many
> > pages are contiguous. If there are two contiguous pages, the I/O request
> > would be 16K. So it's not a constant.
> >
> > Linchi
> >
> > "Cqlboy" wrote:
> >
> > > How does SQL Server determine what request size to make to the disk IO
> > > subsystem? If you monitor in Perfmon the Avg Bytes Per Read/Write you'll see
> > > that this can very between ~8K (min page size in SQL Server) up to ~300
> > > KBytes during backups. But what about everything in between?

Thread options