5

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?

4

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:

The file allocation unit size (cluster size) recommended for SQL Server is 64 KB; this is reflected in Figure 4.

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.

2

The 64K block size comes from the fact that SQL Server does its I/O in units called "Extents", which are each 8 pages - and a page is 8K so the basic unit of I/O is 8 X 8K = 64K.

Paul Randal explains here.

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.