SQL block size

Getting the most performance out of SQL server is always important when using DAX. As a result I constantly read the SQL server articles looking to ensure everything has been considered.

SQL server reads and writes its data and log files in 64K blocks, so using a 64K block size for the hard drive format seems to make sense. But what effect will this have?

I have a local SQL server, virtual, with a virtual SAN. So I created two drives, one formatted with the standard 4K block and one with 64K block. I then ran a series of SQLIO test to see the read and write performance for both random and sequential performance against both drives.

Random Reads       64K block was 1.47% faster
Random Writes       64K block was 0.18% faster
Sequential Reads   64K block was 0.72% faster
Sequential Writes   64K block was 0.04% slower

The numbers are fairly similar, but in general the 64K block formatted drive was faster. Looking at other articles that have tested different SAN’s and different configurations, the results were much clearer in favour of 64K blocks.

The results of this is clear, before configuring the final SQL server, a diagnostic should be run on the hardware to determine the best block size to use to format with for the data and log drives. Should consider 4K, 32K and 64K blocks and map the IO results against each to determine the best for the hardware being used, before completing the SQL server installation.

Even with the result above this would improve performance, even through the difference is only small, every little helps…