In a SAN based SQL server, where does the 64K allocation block size formatting need to occur?

Question

I understand sql data and log file drive must be formatted 64K allocation block size.

In a SAN environment, there are physical disks which are part of the SAN storage pool; and from the storage pool we create LUN; and then assign the LUN to the Hyper-V and we create disks out of the LUN. Then a disk is assigned to the SQL server VM. Volumes (drives) are then created from this disk for the data and log file.

Suppose I format the drives to be of 64K allocation block size, then does that really help? Or do I need to format the physical disks in the SAN?

Answer 1

I’m assuming you have in mind the cluster size, aka allocation unit size and the recommendations for SQL Server on Windows. This is an attribute of the filesystem, and thus doesn’t directly determine configuration or behavior of storage at the many layers of the disk IO stack below the filesystem.

Note the best practice of using a 64k cluster size is not an absolute: most laptop installs of SQL Server use a 4k cluster size for txlogs and data files due to the filesystem(s) they use and do perfectly fine (given the expectations of a laptop install of SQL Server).

The cluster size of a filesystem determines the minimum “size on disk” for a file in that filesystem, and the minimal incremental growth on disk that can occur to that file on the filesystem.

The filesystem is an organization of potentially many sequential “runs” of clusters with size from 1 to n clusters which make up the files on the filesystem. If there’s a filesystem of cluster size 4k with 1024 files that are each of “size” 512 bytes, the aggregate “size on disk” will be 4 mb. If the cluster size were instead 64k, the aggregate size would be 64 mb.

The cluster size does not directly limit the size of individual disk IOs for SQL Server or any other application. Just affects the organization and accounting within the filesystem - especially if there are many files in the filesystem which grow in a pattern that causes them to be interleaved with each other.

So, the “wasted space” is a consideration for filesystems with thousands of small files - flat files for import or xml files or such. But if there’s only 1 large file in the filesystem (this is my recommendation for high performance databases with the exception of tempdb), having the larger 64k cluster size allows more efficient metadata for the filesystem with no appreciable increase in “wasted space”.

It’s important to note that the relatively new 1 mb cluster size is gaining momentum.

On a brand-new system that expects extremely large data files I’d check it out. if all files in the filesystem grow in a multiple of 1 mb anyway, there will be no wasted space. (I recommend especially watching how the sparse files for SQL Server snapshot databases and the internal snapshots for checkdb/checktable interact with a 1 mb cluster size. the snapshot itself grows by 64kb extent at a time afaik, but i’m not sure if after each 1 mb cluster is added to the sparse file all of the 1 mb is eventually used by the sparse file or not.) And again, the system may benefit from the more streamlined accounting of 1 mb clusters compared to 64k clusters.