SQL Server / 64KB Allocation Unit Size

Posted by in SQL Server, SQL Tips and Tricks on Dec 8th, 2017 | 12 comments

MS SQL Server Best Practices Article suggests as a SQL Server configuration best practice is to format your data, logs, and tempdb file drives with a 64KB allocation unit size.

NTFS Allocation Unit Size
When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb. Be aware however, that using allocation unit sizes greater than 4 KB results in the inability to use NTFS compression on the volume. SQL Server, although it is not recommended that you use this, does support read-only data on compressed volumes.
Source
https://technet.microsoft.com/en-us/library/cc966412.aspx

What does that mean?

That practically means that no matter how small file you have on the drive formatted with allocation unit size to 64KB that file will take up whole 64KB on disk.

Example

C drive is formatted with 4 KB allocation unit size (4KB is default cluster size and it recommended for operating system and file share drives.)

 

And we have a file called 1.txt with size of 1.17KB

 

You can see that size on disk will be equal to the allocation unit size, no matter if file it is actually smaller than that.

Lets copy the same file to the D drive which has allocation unit size 64KB

 

File properties window now look like this

 

Size on disk for the same file will be now equal to 64KB which is the allocation unit size for that drive.

64KB allocation unit size for a drive does not look as best idea if you have lots of really small files but our SQL Server data and log files are much bigger than that.

There are really good whitepapers with a recommendation to keep allocation unit size for SQL Server data and log drives at 64KB. 64KB presents one full extent or 8 * 8KB pages in SQL Server.

 

To determine file allocation unit size for your drive you can run following command and for all drives

fsutil fsinfo ntfsinfo c:
fsutil fsinfo ntfsinfo d:

On Windows 2008 and newer versions you can use GUI to format drive (64K allocation unit) and for previous version OS you can use DISKPART to perform partition alignment and assign file allocation unit size.

 

 

 

Similar Posts:

12 Responses to “ “SQL Server / 64KB Allocation Unit Size”

  1. Elmo Neigh says:
    March 1, 2018 at 9:37 pm

    Really Appreciate this post, is there any way I can get an email sent to me every time you make a fresh update?

    Reply
  2. antonio says:
    September 4, 2018 at 10:30 am

    a shorter command to get name, label and blocksie for all volumes is from
    cmd> wmic volume get name, label, blocksize

    Reply
    • Barac says:
      September 4, 2018 at 10:39 am

      I agree Antonio, it can be used for obtaining partition starting offsets of Windows basic disks as well
      wmic partition get BlockSize, StartingOffset, Name, Index

      Reply
  3. antonio says:
    September 4, 2018 at 12:34 pm

    a shorter command to get volume details: from cmd
    wmic volume get name, label, blocksize
    output:
    BlockSize Label Name
    4096 C:\
    4096 Temporary Storage D:\
    4096 tempdb E:\
    65536 DATI F:\

    Reply
  4. Kristian Aasen says:
    November 16, 2018 at 7:25 am

    Hi Zoran
    FOr Windows Server 2019 there is an option to go further and do 2M AU.
    Any thoughts?

    Reply
  5. Daniel Adeniji says:
    April 16, 2019 at 5:46 pm

    Minor Revisions
    =============

    Original
    ——–

    You can see that size on disk will be equal to the allocation unit size, no matter if file it is actually smaller that that.

    Revise
    ——

    You can see that size on disk will be equal to the allocation unit size, no matter if file it is actually smaller that ( than ) that.

    ===============================================

    Original
    ——–

    To determent file allocation unit size for your drive you can run following command and for all drives

    Revise
    ——

    To determent ( determine ) file allocation unit size for your drive you can run following command and for all drives

    ===============================================

    Reply
  6. Krishna says:
    June 22, 2020 at 2:36 pm

    Is it recommended for Binary files and system databases drive as well to have 64k format?

    Reply
    • Barac says:
      June 22, 2020 at 9:45 pm

      Hi Krishna,
      Sys DBs are usually small so there is no real benefit from the 64 KB AU.
      SQL binaries should be on the default 4KB.
      Cheers

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *