Disk Sector Alignment

Published 09 October 07 07:30 PM | Christian

I’ve just finished 3 chapters for an upcoming Wrox book called Professional SQL Server 2005 Performance Tuning: http://www.amazon.co.uk/Professional-Server-2005-Performance-Tuning/dp/0470176393/ref=sr_1_11/202-3946698-6401428?ie=UTF8&s=books&qid=1191700832&sr=8-11

I joined the author team late so my picture’s not on the cover pic yet.  I know its vain but I can’t wait J

Anyway, what I wanted to post on was a section I wrote on Disk Sector Alignment.  It seems to be one of those things that you've either been doing for years or you've never heard of.  When I left Microsoft some of the guys were trying to get something published “officially” but I don’t know how much interest they managed to get.  I've been meaning to blog on it for ages...

Sector Alignment

Sector Alignment is a little known performance optimization tip that documented for Exchange here: http://technet.microsoft.com/en-us/library/aa998219.aspx (but not very well described) and is equally valid for SQL Server.  There are a few blog postings that try to measure the performance difference for SQL Server but why it can cause a problem is not detailed very well. It should be performed on any NTFS partition that is created on a RAID array to “avoid misalignment with stripe units and caching which can cause performance degradation”. Let’s look in a bit more detail about what that actually means.

First of all, some disk terminology. What you’re interested in for the purpose of explaining sector alignment are sectors, tracks and blocks.

*        A sector is the smallest unit of storage space on disk and is typically 512 bytes.

*        A track is the circular path of sectors that sit around a single circumference of the disk. There are 63 sectors per track numbered 1-63 on each track.

*        A block is how you usually refer to sectors to make things easier for you. You start at the beginning of the disk and increment until the end of the disk. Blocks start from 0.

Partitions always start at the first sector on a track, so for the first partition that means Track 2 Sector 1 (because the MBR is on Track 1), which equates to Block number 63 (blocks start from 0). Misalignment occurs because the storage vendors define tracks differently. On an EMC Symmetrix, for example, a track is considered to be 64 blocks and the cache is also based on this.

A Symmetrix DMX RAID5 array uses a stripe size of 4 tracks, which totals 256 blocks. 64KB is the largest single write that Windows can make, so using the default partition location means that the first 64Kb write will be fine but the second will straddle 2 “stripes” causing both to be updated.

If you align the partition to 128 blocks (or 64KB as each block is 512bytes) then you don’t cross a track boundary and therefore issue the minimum number of I/Os.

There are 2 methods of aligning your partition and you’ll be pleased to know that it’s quite a simple process. Diskpar is a tool that’s been available for a while to enable you to align partitions but as of Windows 2003 SP1 the preferred method is Diskpart (note the extra ‘t’). The main difference between the two methods is that you specify the alignment value in blocks using Diskpar and in KB using Diskpart.

 

Regards,

 

Christian

http://coeo.com

 

 

 
Filed under:

Comments

# Impedance Mismatch said on October 10, 2007 06:53 AM:

Qualcun'altro altre a me si è accorto che c'è davvero poca letteratura in giro sulla configurazione

# GrumpyOldDBA said on October 10, 2007 09:02 AM:

I think what annoys me most about all this is that the SAN vendors must know about this but as far as I know most never pass this on or apply it, otherwise we wouldn't need to be reading your post!

I'm glad it's making into a book, then they'll be a point of reference to point the san keepers/vendors to when trying to get the performance on the san sorted.

# Christian said on October 10, 2007 08:45 PM:

I forgot to mention actually that Vista and Windows Server 2008 automatically align partitions to 1024KB to accomodate pretty much any RAID configuration so you might want to take that approach as well.  It essentially means that you'll be aligned for any stripe/cache size up to 1024KB.

The book will have a couple of diagrams to help illustrate the problem, I appreciate that its quite difficult to picture it from what I've written.

# TrackBack said on November 12, 2007 09:03 AM:
# TrackBack said on December 25, 2007 12:18 PM:
# HumbleDBA said on January 16, 2008 10:09 AM:

Just found your blog...a little gold mine for the DBA.

I've come across this too, and funnily enough, when speaking to the EMC SAN Vendor they knew about this but didn't implement initially. We were in a high perfomance environment where every ounce of performance was needed. We got changes made to align the Sectors. We certainly saw some quite significant improvements too (some approx 20% - I have a doc somewhere, which I'll try and dig out, which suggests improvements of up to 30% may be achieved in some environments).

Great stuff, Christian. And I look forward to the book.

# Christian said on January 16, 2008 01:03 PM:

Thanks for your comments.  Post a link if you find those documents :)

# Justin Langford's Blog said on January 25, 2008 12:28 PM:

I've worked with Christian Bolton for a number of years - and known him a few years longer! Since establishing

# Joe said on February 29, 2008 01:15 PM:

We are using HP EVA 4100.

We tried asking HP representatives about the right value for sector alignment. At first they didn't know what we're talking about, but then they gave us the number 64...

I'm not sure if this is the right number for this system.

My question is, can we just use 1024 and be sure that it will work well?

As I understand from your post, it will only consume 1Mb of storage for each partition, but will give us the certinty that we are perfectly aligned. Am I correct here?

Are there any problems with setting a bigger number than the actual number?

Thanks,

Joe.

# Christian said on March 1, 2008 10:32 PM:

Hi Joe,

I can tell you for certain that it will only consume 1MB of the first partition because you only need to align the first one.  If you think of all the blocks on all the partitions on your disk unravelled into one long piece of string.  Sector alignment is kind of like chopping off the fraid bit at the beginning so you have one contiguous perfect piece.  You don't need to cut out another piece every time a new partition is defined along the length.

As to 1024KB being a catch-all value that is certainly my understanding but I don't claim to be a storage expert.  However, if you install Windows Server 2008 it would automatically align the first partition to 1024KB anyway for no other reason than to stop you having to do it which adds some feasibility to my understanding.

There's a nice storage whitepaper here: http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx that discusses related stuff but there's nothing in there that'll make your choice any easier unfortunately.

Cheers,

Christian

# Christian Bolton's SQL Server Blog said on January 12, 2009 11:21 AM:

Modifying the “HBA Queue Depth” is a performance tuning tip for servers that are connected to Storage

# disk sector alignment | keyongtech said on January 22, 2009 03:56 AM:

Pingback from  disk sector alignment | keyongtech