SQL Server 2005 (Standard) Partition Allocation Unit Size


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
Who is Participating?
St3veMaxConnect With a Mentor Commented:
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:


BigSchmuhConnect With a Mentor Commented:
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.
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).
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

BigSchmuhConnect With a Mentor Commented:
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
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.
Craig_hannaganAuthor Commented:
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

BigSchmuhConnect With a Mentor Commented:
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 !"
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.