2

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.

1 Answer 1

1

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.

3
  • Sean, thank you for the thoughtful feedback. EMC storage. Most prod DBs on xtremIO. Also VNX7600. We're revising SQL deploymnet standards. Trying to ensure following best practices. Preparing SE and EE templates for quick, consistent SQL deployments. EMC BP for XIO recommends 64KB AU size for data, log and TempDB. Also want to segregate files to aid in management, recovery. Proposed: - Install MS SQL to C:. Default AU size of 4KB. - System DBs on D:. AU 64KB. - Application DBs on E:, F:, etc. AU 64KB. - Transaction Logs on G:. AU 64KB. - TempDB on T:. AU 64KB.
    – gmoseley
    Feb 21, 2018 at 15:55
  • Sean, We have DR data center. Protect high profile apps with Zerto. VM snapshots of C: and D: quiesce DBs and will allow for quick VM recovery. Subsequent recovery of app DBs via SQL restore. Also using AAG for highest profile apps for HA. Thank you, Greg
    – gmoseley
    Feb 21, 2018 at 15:56
  • If they recommend 64k, then use 64k. You can't get a better answer than the storage vendor telling you :) @gmoseley Feb 21, 2018 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.