SQL Server 2005 (Standard) Partition Allocation Unit Size

Posted on 2009-04-16
Last Modified: 2013-11-14

I have two new LUNS on a Fibre Channel EMC CX4-120 SAN.

The Operating System for SQL 2005 Server is Windows Server 2003 SP2 (64 bit). The LUNs are configured as:

1 x 500 GB (Raid 5) LUN presented to the SQL 2005 Server for sql database data files.
1 x 460 GB (Raid 10) LUN presented to the SQL 2005 Server for sql database log files.

I will use the diskpart command and set the alignment offset for both of the LUNs to 64. My question is when I format the two LUNs with Windows should I use a particular NTFS allocation unit size? I was thinking of an allocation unit size of 4KB but I am getting mixed views from different people on the Internet, most are suggesting 64kb? I have done lots of research on this but am getting confused.

Could someone with some solid knowledge in this area please provide me with their opinion.

Thanks Craig
Question by:Craig_hannagan
    LVL 13

    Accepted Solution

    I believe 64K is better as it's down to how SQL Server stores data in Extent's. An Extent is 8K and there are usually 8 being stored in a single 64K block. When you read in this data to SQL; an Extent is the smallest it can read; so IMO no real benefit in going smaller than a 64k block.

    Take a look at the following link:

    LVL 18

    Assisted Solution

    I would have try to go with Win2008 because of :
    -its 1MB default alignment
    -its 1 to 8MB io (compared to the 64KB NTFS maximum in XP/Win2003)

    However, the "best" size although depends on your LUN stripe size and its usage.

    Data and Index are mainly using random io with more sequential io on index.
    If your raid 5 data LUN is using 64KB block size (=the size on each drive), then a NTFS using 64KB cluster size is perfect because when SQL issues an io, it concerns exactly 1 drive instead of 2 or more. Basically, I will try to use 64KB block on my LUN...and otherwise try to create NTFS cluster size of the exact same size that this block size.
    I hope you have a large write back cache backed by battery to support the raid 5 write problem (read old parity/compute parity/write new parity and data).

    Did you try to use raw device which allows to avoid the use of a filesystem letting SqlServer decides its own use of the LUN ?
    ==> That is the fastest access mode whatever the case. Backuping the db will require the use of the standard SqlServer Backup tools because you can not backup the raw device as is...but this is the only trouble you got...and you can define LUN >2TB without any partitioning problem

    Logs are sequentially written and rarely read.
    RAID 1/10 is the only raid level compatible with that write usage.
    If your raid 10 LUN is using 64KB block size, then a 64KB cluster size will although be perfect.
    LVL 55

    Expert Comment

    according to EMC you do not have to bother about alignment on a CX4, they supposedly have some trick that does it for you. There again it's hardly takes any time using diskpart so you might as well do anyway.

    64k for the stripe element size and cluster size is standard for SQL as already mentioned, a smaller cluster size wouldn't affect the IO but I think it would need slightly more windows resources (RAM).
    LVL 18

    Assisted Solution

    MS SQL Server 2005 - Physical Database Storage Design  document §"Sector Size vs. Stripe Size" p.16

    The stripe size refers to the unit of data that is written and accessed from a disk in a RAID system. This is a configurable value that is set when designing the storage array system. A smaller stripe size allows data to be distributed to more disks and increase I/O parallelism. Note that the stripe size of a single SQL Server extent (64 KB) is the lower limit. For the same data, a larger stripe size means the data can be stored on fewer disks and decrease the I/O distribution and the degree of parallelism. We recommend a 64 KB or 256 KB stripe size for most workloads. When the workload includes table and index range scans on tables that are larger than 100 MB, a stripe size of 256 KB allows for more efficient read-ahead.

    So you should:
    -Check that you have a minimum stripe of 64KB on your LUN
    -Check if you have the usage of a dedicated LUN with a larger 256KB stripe
    -(More in the MS doc) Use raw device if you are expecting more than a 20000 iops usage and 64KB NTFS cluster size otherwise
    LVL 55

    Expert Comment

    Like I said it's the storage box's stripe size that matters, the cluster size simply means there are more sector numbers in the list at one stage of the pipeline if it is set low.

    Author Comment

    Thanks for your all your info. This has allowed me to get a better understanding of the situation. I will set the alignment offset of the LUNs (within windows) to 64KB and also format the LUNs using an allocation unit size of 64KB.

    One thing I did forget to ask was both of the LUNs orginally mentioned above are in different Raid Groups. Those Raid Groups also contain other VMFS LUNs used for our VMware Virtual machines.

    When I align the raw disk LUNs within windows will that affect the other VMFS LUNs in the same Raid Groups?

    Cheers Craig

    LVL 18

    Assisted Solution

    No, local alignment won't affect other LUNs.

    One of my customer had this same kind of "raid group sharing" management...
    ==> He used to have huge performance problem due to resource sharing conflicts !

    For example, he had 2 LUN on the same raid group, one for a night disk to disk backup, the other for some DB2 data.
    ==> Everything looks fine...up to the day where he tries to restore from the disk to disk backup during the day ! He had such a slowdown in the DB2 db that he almost crashed the SIEBEL platforms with 800 call centers agents using it ! And worse, nobody was really aware at production of this resource sharing : it took 2 hours to understand why he had a performance problem without any recent production rollout !

    That is often a problem NOT to use the free space on those drives which should be exclusively used by one app due to its IOPS level...
    ==> "I need more drives !" followed by "You have free space here !"
    LVL 55

    Expert Comment

    Not aligning your partition does have an indirect effect on other LUNs on the other LUNs of course, you'll be causing more I/O on the disk group than you need to. Similarly if the VMDK files and their contents are misaligned that will have an affect on your I/O, you might want to get your VMware guys to check

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Problem description :  Some external hard disks / USB flash drives do not show actual space as mentioned in the factory settings. This is a common problem when you use an 8 GB USB drive to make it bootable to install a firmware/ driver on a serv…
    The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
    This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
    This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now