Why Do I Care?
Everything in SQL Server is stored on disk in 8K pages. The Microsoft recommended best practice is to format using a 64K allocation unit size. Processes such as the checkpoint and Lazy Writer try to write in extents when possible. Extents are a collection of 8 pages and since each page is 8K we get 64K. That is where the best practice recommendation originated. Please note that not everything in SQL Server uses this IO pattern. Best practices are a great place to start, but are by no means a one size fits all. This article is about how to find what allocation unit size you are currently using and not about all the possible IO patterns of SQL Server.
For those interested in the details of SQL IO patterns you should check out Bob Ward’s presentation on the subject.
Find Allocation Unit Size
To identify the allocation unit size for a volume, we can use the fsutil.exe utility. In the output you are looking for “Bytes Per Cluster” which is your allocation unit size. Here is an example to retrieve the information for the G:\ volume.
fsutil fsinfo ntfsInfo G:
How To Format
We can see above that my volume is not formatted at 64K. There are three different methods we can use to format this volume. The first is to use Disk Management in the OS, right click the desired volume, and select format. We will need to select 64K in the “allocation unit size” drop down menu.
The second method is Powershell. Here is an example.
Format-Volume -DriveLetter G -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$false -Force
The third method is format.exe. Here is an example.
Format G: /FS:NTFS /V:DATA /Q /A:64K
Resultant Allocation Unit Size
If we go back and use fsutil.exe to verify, we can see we are now formatted at 64K. This is a great starting point and don’t forget that you can define the size of your backups to match and speed them up. Blocksize is the backup parameter you will be looking for.
fsutil fsinfo ntfsInfo G:
You must be logged in to post a comment.