Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



Check Allocation Unit Size for Disks in a SQL Server System
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-04-28














"Windows systems write blocks of data to underlying storage – the size of these blocks is given various terms, for example – Block size, Allocation unit, Cluster size.

The important thing to consider is that this unit of allocation can have an impact on the performance of systems. By default Windows will format a disk with a standard 4KB block size. Microsoft Exchange server for example, recommends the unit size be 64KB. Microsoft recommend this for SQL server and virtual machines as well. By not setting the value correctly on an underlying disk we can generate performance problems for our system. Aligning block size with the data set being written can help to ensure efficient IO processing. If we consider SQL server which uses a 64KB extent (which is it’s basic unit, made up of eight, 8KB pages) we can most effectively format the disk with a 64KB block size. Assuming this is a locally attached disk we can then have Windows write the extent as one contiguous block rather than having to split it up into say 4KB default sized blocks. Imagine taking that 64KB extent and having to split that single IO into 16 smaller IO writes – not very efficient.

If we are writing to SAN storage we also want to make sure the block size (or whatever the vendor calls it) is also appropriate, again to try and avoid split IO writes and to ensure performance. It’s a big topic to discuss but I will end here and move on to how we can retrieve this information from our servers."

"We can make use of the fsutil amd DiskPart commands. Note that in fsutil the value is ‘Byte Per Cluster’ with a 64KB value while in DiskPart it is called ‘Allocation Unit Size’."

"I will use the DiskPart utility to list all of the volumes attached to this server, then I will select a volume and use the ‘filesystems’ command to display the required information. As mentioned the value is now described as ‘Allocation Unit Size’."

"As you can see Microsoft lack consistency in describing this attribute which is rather frustrating but such is the way of the IT world. It’s always important to check that you are formatting storage with the correct block size to avoid split IO and misalignment issues. I have seen some vendors (especially all flash array sellers) telling administrators not to worry because their system is ‘so fast’ any overhead or inefficiencies caused can be compensated for by the systems speed. Personally I hate this notion and fully believe everything should be optimised and deployed according to best practices to get the most out of the system."

Source: https://www.bytesizedalex.com/get-windows-ntfs-block-size/



"Please format the drive with allocation unit size 64 KB, Windows
Server 2008 (or later) will align the drive for you."

"SQL Server store data in pages of 8 KB, then group those pages on extents, which are created with 8 pages of 8 KB each extent. Those pages may belong to one object (uniform extents) or multiple objects (mixed objects). The extents (8 x 8 KB) are reason behind that recommendation of formatting partitions of 64 KB."

Source: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ca6fa502-0fc5-45aa-94c4-b33ced70c41e/why-sql-server-db-disks-format-with-64kb?forum=sqlsetupandupgrade




"A single I/O request coming into an array controller can turn into multiple I/Os if the request crosses one or more stripe unit boundaries. In all cases, similar principles are at work: Due to misalignment clusters of data are written across physical boundaries requiring unintended, unnecessary I/Os that result in performance degradation.

Beginning in Server 2008, the operating system attempts to align the disks out of the box. This means that there is a high probability that the disks will be created with an offset that will align with the underlying raid stripe units.

There are two correlations which show if the disk is configured optimally for I/O performance. The result of these calculations must result in a whole number for optimal performance.

Partition_Offset ÷ Stripe_Unit_Size

Stripe_Unit_Size ÷ File_Allocation_Unit_Size

To find the starting offset (partition_Offset) of your drives, you can run the following command in a Command Prompt:

wmic partition get BlockSize, StartingOffset, Name, Index

To get the stripe Unit size and the File Allocation Size, you can run the following for each drive:

fsutil fsinfo ntfsinfo c:

Windows does not have a reliable way to determine stripe unit sizes. These values can be obtained from vendor disk management software or from your SAN administrator. Dell will commonly use a 64KB stripe size as a default for its controllers, so this partition would be aligned assuming we have a 64KB stripe size. (1048576/65536 = 8) While having the drives aligned is important, the first of the 2 equations above being by far more critical than the second, we also want to adjust the file allocation unit size (Bytes per cluster) to coincide with the best practices as prescribed by Microsoft.

Microsoft recommends that the File allocation unit size (Bytes per cluster) be set to 64KB.

Microsoft states that an average response time of over 15 milliseconds indicates a problem that should be investigated."

https://www.virtual-dba.com/disk-configuration-optimized-sql-server/




Disk Partition Alignment Best Practices for SQL Server (older)

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd758814(v=sql.100)