Learn how to a build a cloud-first strategyRegister Now


SQL Server 2005 (Standard) Partition Allocation Unit Size

Posted on 2009-04-16
Medium Priority
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

St3veMax earned 600 total points
ID: 24157035
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

BigSchmuh earned 1400 total points
ID: 24157311
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 56

Expert Comment

ID: 24159953
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).
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 18

Assisted Solution

BigSchmuh earned 1400 total points
ID: 24160437
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 56

Expert Comment

ID: 24161072
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

ID: 24163658
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

BigSchmuh earned 1400 total points
ID: 24165701
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 56

Expert Comment

ID: 24165797
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 www.vmware.com/pdf/esx3_partition_align.pdf

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Each year, investment in cloud platforms grows more than 20% (https://www.immun.io/hubfs/Immunio_2016/Content/Marketing/Cloud-Security-Report-2016.pdf?submissionGuid=a8d80a00-6fee-4b85-81db-a4e28f681762) as an increasing number of companies begin to…
Is your phone running out of space to hold pictures?  This article will show you quick tips on how to solve this problem.
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 tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

810 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