Recommendations for the same database page size and file system cluster (sector) size

[former_SQLA_member1694881]

Hi All,

As far as I know, there is a recommendation that the size of the database page and the size of the cluster (sector) of the file system (for example, NTFS) where the database is located are the same. Thanks to this, when reading one database page, there will be only one access to one sector of the file system.

As I understand it, this recommendation is definitely relevant when it comes to HDD.

Questions:

1) Is this recommendation relevant if the database is located on an SSD ?

2) Is this recommendation relevant if the database is located inside a virtual machine somewhere in a data center ?

[VolkerBarth · Accepted solution]

Mark has answered that some months ago here including a link to his older, more detailed answer.

Note, I am absolutely no expert on that topic - but in my understanding even with SSDs, it's an disadvantage when page size is smaller than FS sector size: When it comes to writing, inevitably more data has to be re-written than necessary (i.e. a full sector, not only the modified database page), and that would increase the risk of SSD wear-out. Whether that is a real concern or not will depend on the particular SSDs and the frequency of repeated writes to the same pages...

[former_SQLA_member1694881 · Accepted solution]

Thanks for the link, I'll read it.

But what if it’s the other way around, that is, the size of the database page (for example, 16 kb)) is larger than the size of the cluster (sector) of the file system (for example, NTFS 4 kb)?

I am interested in this with the goal of reducing the load (read-write operations) on the hard drive.

[VolkerBarth · Accepted solution]

I think Mark has answered that as well in the linked response. For SSDs, I assume that a page size larger than the FS sector size won't do harm as SSDs will translate all file sectors to their internal (and randomly accessable) blocks, so it won't matter whether the file system needs more than one sector per database page... There's certainly lots of general information on file system sectors vs. SSD blocks.

But as stated, I that's not my area of expertise. 🙂