3

SSDs tend to have a natural 4K sector size as far as I know, but would it be beneficial to format Windows cluster size at 64K? This is good practice for spinning rust, but is it relevant on SSDs?

1

Just came across this 2-year old link: https://blogs.msdn.microsoft.com/saponsqlserver/2014/10/01/message-misaligned-log-ios-which-required-falling-back-to-synchronous-io-in-sql-server-error-log/

It implies that 64K NTFS cluster size is still recommended for SSDs

To improve this answer it would be ideal to hear from real-life experience with latest generation SSDs (FusionIO or SATA-controlled). Maybe 256K is even better for columnstores on SSDs!

  • I know this is old, but it isn't implied, Jimmy is explicit about it saying, "The performance impact of misalignment is not as apparent on SSD relative to spinning media. Yet partition alignment is required for optimal performance." – AlwaysLearning Sep 27 at 0:42
1

Yes, 64KB is still the current recommendation as it has to do with partition alignment. To quote Jimmy May from the the SQL CAT team:

Partition alignment remains a best practice for all versions of Windows Server as well as SQL Server, including SQL Server 2012 & SQL Server 2014. No exceptions. Period.

As part of the Best Practice guide referenced in that article, the following line is quite explicit:

The file allocation unit size (cluster size) recommended for SQL Server is 64 KB

As for a SSD vendor's perspective, I point you to Argenis Fernandez's blog post about this very topic. Pure even recommends 64KB for NTFS block size, so I think it's safe to assume this is a pretty universal truth.

  • Nice one, the Pure link mentions the /L formatting parameter, which I hadn't come across before – John Alan Dec 30 '16 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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