SQL Server IO size

Question

When sql server choose the I/O size for a single read/write (Data file random IO)?

8KB
64KB

or even larger

Answer 1

on SQL Server startup, with the buffer cache empty, all read IO is 64K, even for queries that access probable random pages. Once the buffer cache is full (target size reached?) it switches over to 8KB IO. Tables scans may generate 64K IO Backups may employ 1M IO (settable)
Try looking for a slide deck by Jimmy May, on partition alignment. This pertained to the old days when Windows would allocate partitions from the first available block, which was not 64K aligned, causing horrible problems on RAID arrays. This is nolonger an issue, but the deck mentions SQL IO patterns

Answer 2

Hi @sakuraime ,

When sql server choose the I/O size for a single read/write (Data file random IO)?8KB,64KB,or even larger

Yes. 8,64,128… all are possible.

Actually, this issue belongs to the category of hard drive stress testing.
For different types of disk read and write operations, the amount of data for each read and write (Transfer) will be different. As for the disk, the amount of reads and writes done per second is not only related to the number of reads and writes completed, but also has a lot to do with the amount of data read and written each time. Different SQL Server operations will be different of reads and writes. Fragmentary operations such as Index Seek may be more in units of 8 KB pages; while operations such as Read ahead and table scan are more in units of 64 KB areas, or even larger units.

Microsoft also provides a tool specifically for database hard disk stress testing. This tool is called SQLIO.exe. You can google how to install and use it.

More information:
https://support.microsoft.com/en-us/help/231619/how-to-use-the-sqliosim-utility-to-simulate-sql-server-activity-on-a-d
https://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/

BR,
Mia
If the reply is helped, please do “Accept Answer” .

Answer 3

Hi @sakuraime ,

Is the reply helpful?

BR,
Mia
If the reply is helped,please do "Accept Answer".--Mia

Answer 4

Hi @sakuraime ,

Is the reply helpful?

BR,
Mia
If the reply is helped,please do "Accept Answer".--Mia

Answer 5

Hi @sakuraime ,

Is the reply helpful?

BR,
Mia
If the reply is helped,please do "Accept Answer".--Mia