Sql-server – 64k AU size – master, model, msdb

configurationinstallationsql server

Should master, model and msdb reside on drives with 64k AU size? Is it necessary? Recommended?

All of our SQL servers are on VMware. If necessary, I would like to be able to recover a VM from a snapshot that restores OS, SQL executables and system databases. Then restore application databases from SQL backups.

I'm trying to determine if I should install SQL server to C: (4k AU) and place the system databases on D: (64k AU). And application databases on E:, F:, etc. (64k AU). Log files on G: (64k AU). TempDB on T: (64k AU). Would snap C and D.

Best Answer

Should master, model and msdb reside on drives with 64k AU size? Is it necessary? Recommended?

In general, make a standard and use it. To directly answer your question, if all other filesystems are going to be formatted for 64KB, then why not stick to it - though, regardless, I'd go with the storage vendor's recommendation. Let's look per DB:

  • Master: In general it doesn't have much "extra" data put in it and shouldn't really be growing or heavily transactional. Allocating 64k or 4k isn't going to make much of a difference.
  • Model: There should be no transactional activity happening in this database, in general, by default. There might be some additional objects added but this should be similar to master where it's largely static.
  • MSDB: This database is actually used more than both of the previous database. It holds things such as Jobs, Mail, Backup Information, etc. This database could grow to be very large when not properly looked after.

Without going into a large amount of technical detail, you should choose whatever the storage behind the virtualization layer likes as it's preferred or most optimized transfer size, that's what I'd use for my allocation unit size. This is because it matches what the storage layer likes the most and will be the most optimized.

If you want to get a little more technical, uniform extents are 8 contiguous pages which just happens to be 64k. This happens to nicely line up with a 64k allocation unit size. The largest possible log block can be 60k, which again fits nicely... but generally is much less, which results in space waste, and could not be the optimized size that the storage layer likes.

The allocation unit size is just the smallest possible fragment given to a file. Thus an AU of 64k means a file, holding just a single bit would still have an on disk size of 64k. Normally, larger AU sizes result in lower on disk fragmentation which may or may not matter, depending on the storage array's physical storage medium. In an example of this, SSDs and NVME hold internal translation tables for pages (not the same as DB pages) for wear leveling and write amplification. In this case, there is no idea of where the physical data will be saved, and thus may or may not be physically sequential.

Thus, again, I'd go with the value specified by the storage layer vendor. If you don't happen to know what that is, 4k and 64k are both well supported (though larger AU sizes may not support certain NTFS technologies, which shouldn't be used with SQL Server anyway - but for completeness I added it).

Really, what will make the largest difference is whether the storage layer has mixed 512 and 512e/h storage representations (which is the physical block size). This will cause more issues that allocation unit size of the filesystem.

If necessary, I would like to be able to recover a VM from a snapshot that restores OS, SQL executables and system databases. Then restore application databases from SQL backups.

In a highly unrelated note, I'd double and triple check that this meets your disaster recovery or business continuity requirements/SLAs. Allocation Unit sizes should not impact this.

Related Question