NTFS Allocation Unit Size on SharePoint SQL Server Machine

Typically in a production SharePoint farm you will have SQL Server installed on a separate physical hardware. The NTFS allocation unit size is an important parameter for the performance of the SQL Server machine. All file systems used by Windows organize your hard disk based on cluster size (allocation unit size). The cluster size represents the smallest amount of disk space that can be used to hold a file. When file sizes are not an even multiple of the cluster size it requires up to the next multiple of the cluster size to hold the complete file. NTFS file compression earlier was possible on drives that had a cluster size of 4 kilobytes (KB). So normally you still see the cluster size of 4 KB.

SQL Server uses extents to store data. Extents are groups of eight 8 KB pages that are physically contiguous to each other for a total of 64 KB. On a SQL Server machine the cluster size should be 64K. By changing the cluster size, the chances of I/O's that span multiple NTFS allocations are reduced. Otherwise there could be a split in the data allocation and hence multiple I/O's required to retrieve the SQL data.

The performance improvement can be as much as 30 percent when using 64 KB allocation units than 4 KB allocation units. You can save a 30% performance penalty by choosing the correct formatting option for your SQL Server machine.

Run chkdsk<drive> to get the allocation unit. On my machine which is a Windows Server 2012 the result is as in the following.


To make it 64K format use the command: Format <drive> /Q /FS:NTFS /A:64K /V:Volume /Y

The format command won't use clusters larger than 4 KB unless the user specifically overrides the default settings. You can do this by using the /A: switch together with the Format command or by specifying a larger cluster size in the Format dialog box in Windows Explorer.