Disk Alignment

Question

Post reply

Disk Alignment

pdevadiga

Old Hand

Points: 358
More actions
September 26, 2009 at 2:18 am

#205082

Hi,
This is about Disk Alignment for SQL Server.....does anyone have an idea as to how to implement this.
There is a whitepaper called "Disk Partition Alignment Best Practices for SQL Server" by Jimmy May & Denny Lee.
Its worth a read but I couldn't get anywhere near:w00t:.
It says this is a default feature in Windows 2008 but still needs to be looked into.
This paper documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance;
it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation;
and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of preexisting partitions.
Can anyone throw light on this subject & if implemented how to go about it?
Regards,
Prashant

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

vincent.delabre SSC Rookie Points: 45 More actions · Answer 1

Hello,

Windows 2008 Server create partitions with a 1MB offset, so that alignment is OK, even for very large stripes.

If your partition were created without using Windows 2008, then you will have to reformat.

Note that if you are using an HP EVA SAN, then it's not really necessary anyway.

Regards,

Vincent

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

>> Note that if you are using an HP EVA SAN, then it's not really necessary anyway.

Vincent, can you please offer proof of that assertion?

To OP, Jimmy also has a powerpoint deck based on that white paper that gives explicit instructions and code examples. Search web for it. diskpart is the tool you need to use before win2k8 to effect partition alignment, but note that it must be done before installing OS and when you create additional partitions. Also note that there are other things you need to do such as 64K NTFS format cluster sizes.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

pdevadiga Old Hand Points: 358 More actions · Answer 3

Thanx Ten Centuries & everybody for thier replies...

Now it is pretty clear to me

Regards,

Prashant

gavin.smith Old Hand Points: 376 More actions · Answer 4

I have been implementing this on our database servers running Windows Server 2003 for about a year now.

There is documentation here: http://support.microsoft.com/kb/929491

All I do is: Partition the disk with 1024K offset, and format with 64K allocation size for SQL Server 2005.

Start --> Run, type in “cmd”

At the command prompt type in “diskpart” to run the disk partitioning utility

“Select disk X”; where x is the number of the disk in Computer management

e.g.

C:\>diskpart

DISKPART>list disk

(Disks with un-partitioned space will have the available space listed under “free”)

Disk ### Status Size Free Dyn Gpt

-------- ---------- ------- ------- --- ---

Disk 0 Online 75 GB 0 B

Disk 1 Online 75 GB 75 GB

DISKPART>select disk 1

DISKPART>create partition primary align=1024

DISKPART>assign letter=E

DISKPART>Exit

Then I format the disk from the command line:

e.g.

format E: /fs:ntfs /v:SQL_Data /A:64k

I believe that you only need to align one partition on the disk, and all subsequent partitions will be aligned.

You can't align the partition that windows is installed on, so I don't bother with that. I only align partitions that will hold SQL database files and Transaction logs.

I have benchmarked the performance using SQLIO on Dell PowerEdge servers and on a Dell PowerVault MD3000 disk array, and there is a noticeable benefit from doing this.

Aligning to 1024K was better than aligning to 64K, and aligning to 64K was better than not aligning the partitions at all.

You should carry out your own testing to ensure that this works for your environment.