Share via

SQL Server IO size

sakuraime 2,321 Reputation points
2020-08-24T03:11:30.007+00:00

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

8KB
64KB

or even larger

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,187 questions
0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Chang, Joe 111 Reputation points
    2020-08-30T19:59:24.703+00:00

    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

    1 person found this answer helpful.
  2. m 4,271 Reputation points
    2020-08-24T07:32:05.373+00:00

    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” .

    0 comments
  3. m 4,271 Reputation points
    2020-08-25T01:24:41.543+00:00

    Hi @sakuraime ,

    Is the reply helpful?

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

    0 comments
  4. m 4,271 Reputation points
    2020-08-26T01:29:29.813+00:00

    Hi @sakuraime ,

    Is the reply helpful?

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

    0 comments
  5. m 4,271 Reputation points
    2020-08-28T01:34:04.61+00:00

    Hi @sakuraime ,

    Is the reply helpful?

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

    0 comments