SQL Server with Mr. Denny

Jun 23 2008   11:02AM GMT

How much performance are you loosing by not aligning your drives?

Denny Cherry Denny Cherry Profile: Denny Cherry

Myself and many other people have been saying for quite a while now that you need to align your disks before putting data on them.  I know have some information on how you can figure out just how much potential performance you are loosing by not aligning.

 Before we can begin to figure this out we need to know what the average work load for the disk is going to be.  In the SQL Server world this is easy.  SQL Server does everything in 8k pages within 64k extents.  Each time it needs to read from the disk it reads the 64k extent from the disk and each time it writes to the disk it writes the 64k extent.  So our data size is 64k.

We take this number and divide by 64.  So in our case 64/64 = 1.  1 as a percentage is 100%, so 100% of our data reads and writes are requiring two physical reads or writes.

If you are in the exchange world everything is done in 8k reads and writes.  So in this case 8/64 = 0.125 or 12.5% of the reads and writes are requiring two physical reads or writes.

Now for SQL Server just because we are doubling the number of operations doesn’t mean that by fixing this you will double your disk speed.  What it means is that if your disks are running at 100% utilization you can probably reduce your disk load by 50%.  But if your disk utilization is 30% your disk activity won’t be any faster as your disks are not running at capacity.  Will you see a performance improvement, yes.  Will it be as high as if your disk was at 100%, no.  Should you still fix the alignment problem?  Yes.

To fix the problem isn’t easy.  You have to remove all the data from the disk, and delete the partition, then recreate the partition using the DISKPAR.EXE (Windows 2000) or DISKPART.EXE (Windows 2003/2008) with the ALIGN=64 setting.  To remove the data from the disk you will either need to migrate to a new disk within the server, or backup the database, fix the alignment then restore the database.

Denny

6  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Shel
    interesting stuff, Denny. This is my third week at a new job. Is there a way to identify how our servers are configured?
    0 pointsBadges:
    report
  • Denny Cherry
    You use the diskpart command to see the partition offset. Open a command prompt and select the disk you want to see. Then type "list partition" and look at the offset. You are looking for a 64k offset, the default is 32k.
    66,115 pointsBadges:
    report
  • JohnMCouch
    That is very interesting. I have a couple of questions though in reference to this. 1: The operating system is defaulted to 32KB because of the waste that would occur for storing data that is smaller than 64KB. Is that correct? 2: Based on answer above, is it my understanding that only the drives where you would store SQL Server Database Data/Log Files should be aligned?
    5 pointsBadges:
    report
  • JohnMCouch
    I read a bit from this Microsoft article in relation to aligning the partitions but I don't fully understand where to get these values or determine I am aligned correctly. It states in the article that in order to determine when alignment occurs, you need the below formula: ((Partition offset) * (Disk sector size)) / (Stripe unit size) Partition Offset is found in Diskpart as you show above. How do you determine Disk Sector Size and Stripe Unit Size? http://support.microsoft.com/kb/929491 Thanks in advance for your help.
    5 pointsBadges:
    report
  • Denny Cherry
    John, Sorry it took me so long to get back to you. Its been a busy couple of weeks. 1. The OS defaults to 32KB because when the fat file system was defined space was an issue and every 32KB counted. These days that lost 32KB isn't a problem, but it causes the alignment problem which then causes performance problems. 2. Actually all disks which store data which is frequently accessed should be aligned. This includes database servers, file servers, exchange servers, etc. The Disk Sector Size and Stripe Unit size will be gotten from the storage it self. When working with SQL Server its best to set these to 64KB as SQL Server does everything in 64KB chunks.
    66,115 pointsBadges:
    report
  • Denny Cherry
    John, I forgot to mention if you happen to be going to PDC in Los Angeles this October, we are having a [A href="http://www.socalcodecamp.com"]Code Camp[/A] the weekend before. At the code camp I'll be giving a session on [A href="http://www.socalcodecamp.com/session.aspx?sid=76cc1740-42b5-4298-9bd4-856eb9872294"]Storage for the DBA[/A] which you may find interesting. Denny
    66,115 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: