Thursday, October 18, 2012

Sql Server 2008–Optimal BlockSize for drives

Did you know that the optimal block size for Sql Server data and log drives is 64Kb? (sometimes it can be 32kb)

An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. In many cases, this is the same size for Analysis Services data or log files, but there are times where 32 KB provides better performance. To determine the right size, you will need to do performance testing with your workload comparing the two different block sizes.

To determine the block size of your drives, run the following command:

fsutil fsinfo ntfsinfo b:

Where b: is the drive.

The value you are looking for is “Bytes Per Cluster” and its reported in bytes.

Read about it at: http://msdn.microsoft.com/en-us/library/dd758814(v=SQL.100).aspx

No comments: