There are many articles on what storage blocks size should be used for sql server e.g. Disk Partition Alignment Best Practices for SQL Server. The right blocks size should improve the performance of a sql server database. I’m looking for a recommendations and methods to identify which storage blocks size is appropriated for a database. Is there a guide on how to identify an appropriated block size?
Sql-server – Choosing the right storage block size for sql server
size;sql serverstorage
Related Question
- Sql-server – How to identify unused databases on the sql server
- Sql-server – Configuring physical storage for a Microsoft SQL Server installation
- Sql-server – Storage pool configuration for SQL Server
- Sql-server – Best practices for SQL Server block size while formatting disks for data and log files
- Sql-server – SQL Server storage – evaluate the number and types of read write operations for the existing deployment
Best Answer
The method is the one used in the article that you linked in your question: test it. You can capture a representative workload for your application/database and replay that workload on servers that have different cluster sizes. What you will find out is that SQL Sever works best with 64KB clusters, because of the way it reads data from disk (read-ahead).
However, you don't have to do that: setting a 64KB cluster size is an established best practice, as clearly stated in the article that you refer to. From the article:
If you decided to test the performance under different cluster sizes, you would probably discover that the difference is negligible, especially on high-end storage systems.