Given SQL Server's ability to adjust I/O block sizes based on operation, how does formatting the disk with 64K allocation unit help a read query?

Question

Following is a SQL Server I/O block size reference table. Given its ability to adjust the block size based on operation, how does formatting disk using 64KB vs 4KB allocation units help a read query?

For example, read ahead scans have block size of 128KB to 512KB, this means SQL Sever will fetch a 128-512KB block from disk. How does the disk format of 4KB vs 64KB affect the read query?

_{The above table is from What is SQL Server’s IO Block Size? by Argenis Fernandez}

I have benchmarked by creating two databases with one database on 4KB and the other on 64KB formatted drives. I ran a select query on a large table separately and don't see any significant difference.

Understanding SQL Server IO Size by Anthony Nocentino concludes:

SQL Server performs IOs in variable size based on the type of IO performed and that IO size is independent of NTFS Allocation Unit Size.

My thoughts:

I think that the 64K recommendation helps optimize reads because: Sql server inserts data into extents. An extent consists of 8*8KB pages. So when disk is formatted with 64KB allocation unit then those pages will sit next to each other on disk.

This helps read query maybe because when it will fetch Unit of size 128-512KB, is will possibly be a contiguous set of 64KB with 8*8KB extents sitting close to each other. Hence optimizing read query performance. However this maybe relevant and useful only when 1 drive is assigned to 1 db, and less relevant when multiple dbs share a drive and irrelevant when using a SAN based drive?

Answer 1

how does formatting the disk with 64K allocation unit help a read query?

Larger filesystem block size slightly increases the probability of the read-ahead operation retrieving relevant data from the disk.

when it will fetch Unit of size 128-512KB, is will possibly be a contiguous set of 64KB with 8*8KB extents sitting close to each other

There's absolutely no guarantee of that in real life, so in the worst case scenario (busy system, multiple concurrent workloads updating multiple tables and indexes, processes other than SQL Server writing to disk), if your filesystem is formatted using 64 KiB blocks, there is at least one in eight chance that your read-ahead operation fetches what it wants; on a 4 KiB block filesystem the probability drops proportionally, because your 512 KiB read block is more likely to contain many 4 KiB blocks of other files.

On the write-heavy system the reverse is true; you don't want to update a 64 KiB FS block every time you write an 8 KiB database block.

In other words, a contrived test like yours won't show any difference, while a real environment with an 80/20 ratio of reads to writes might indeed benefit from a larger FS block size.