2

I'm about to install a SQL Server 2019 instance on a Windows 2019 machine and found out that there are new NTFS allocation unit sizes available - up to 2M.

I used to format partitions using 64k unit size (obviously), but I'm tempted to use the larger ones this time.

Any one has any thoughts or experience in this matter?

Improve this question
5
  • 1
    My thought is any multiple of 64K is appropriate for data files since that aligns with the SQL Server extent size. Whatever size you choose, it would be optimal for the initial size and growth for all files be an exact multiple of the AU size to avoid wasted space.
    – Dan Guzman
    Mar 8, 2020 at 15:01
  • I would say - test it. My hinge is that the larger the partition size, it is good since less overhead for metadata management. Since 2019 offers 2MB of allocation size, test it and compare the results. see this which backsup what I am trying to say. Dont forget turning Instant File initialization as well.
    – Kin Shah
    Mar 8, 2020 at 21:55
  • We used - DiskSpd Tool - for load tests with different workloads. You can get this free tool here
    – Kris
    Mar 9, 2020 at 14:03
  • We ran a comparison using DiskSpd between an allocation unit size of 64KB and 2MB, with various block sizes. This was on an Azure VM using standard storage. The differences were no more than 1.7% in any of the tests, usually smaller. On average the 2MB unit size was marginally faster, but only just. I'd note our Azure VM was only sized at 2 vCPU / 8GB RAM, so the I/O throughput seems to be capped at 32MB/s. Perhaps on a less restricted setup we would see more meaningful results.
    – Mike
    Apr 21, 2020 at 9:21
  • Guys, thanks for your contribution. I configured 2 MB unit size on my new server, which my old DW server (2012) had been supposed to be migrated to. Unfortunately due to the COVID situation we were forced to postpone the migration, so I can't provide any personal feedback on the matter. I'll get back to that once the virus problem is over.
    – Timbalero
    Apr 21, 2020 at 20:07

1 Answer 1

Reset to default
0

If it similar to how VMWare works: if you had mis-aligned blocks or larger block on VMWare datastore then when guest OS requested read of block from virtual disk, then VMWare will read whole assosiated data block(s). So if block in VMWare was twice (or more) larger, then you were often wasting your performance.

TLDR: I would not risk going for different allocation unit size if you dont need disk larger than 256TB.

Improve this answer

Your Answer

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

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