File Allocation Unit Size – A Myth



The overall meaning of “Allocation Unit Size” in brief is….

The default allocation unit size on a windows drive is 4KB. When we format the drive, we can set that to a larger size – again 64KB is recommended for SQL Server data, log, and tempdb files.
The allocation unit size is basically the smallest unit of storage that any individual file can take up. If I set the allocation unit size on a drive to 64KB and then create a tiny text file which only takes up 11 bytes, that one file will take up 64KB on disk.

We are supposed to configure it atleast 64K…as recommmended by microsoft…..

There are benefits while configuring to 64K, in terms of reading and writing anything.

Its more effective when we setup drives for copying/taking routine BACKUPS, require that much allocation unit to be configured and to be able to read/write backups quickly.

However, there is no impact to SQL server database, minimum it has to allocate total 8 pages (each 8kb in size, so 64kb) while an extent is allocated to a table.

Since, SQL database files already hold sufficient amount of extents/pages/space/VLF [that has its own blocks/B-tree structure]…correlating this setting with Database should NOT help.

Amit

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: