Amazons study on disk allocation unit size and the impact on SQL Server Performance

[AgentSmith]

This article is a fantastic read. What it basically says is that Amazon tested the newer versions of SQL with the various allocation unit sizes and there was no significant increase in performance by switching allocation unit sizes in their environment.

About 2 years ago I made the recommendation to a customer running in a virtual environment to format their disks to 64KB to optimize their SQL Server performance and found out from their storage team that the allocation units defined within the virtual machine had no bearing on performance because some modern day storage arrays including theirs are intelligent enough to properly optimize the data on the backend. After researching the hardware I learned it was an accurate statement.

https://aws.amazon.com/blogs/modernizing-with-aws/do-aws-customers-benefit-from-64kb-block-size-for-sql-server/

[COOL]

Thanks for sharing, it looks like they are saying that the reason 64kb isn’t better for block size is because modern sql server barely ever requires loading more than one 8kb page, whereas old sql server versions loaded 64kb extents.

So do storage technologies make a difference here? I’m not so sure unless sql server behaves differently (loads 64kb extent if you use X old storage and 8kb extent if you use Y new storage) though that seems unlikely.

[AgentSmith]

Yes sir, it would appear so. This article which Amazon references is another fantastic read. We have an ongoing discussion on performance at my work and this is a topic of discussion.

https://www.sentryone.com/blog/sql-server-storage-best-practices-choosing-storage-options