THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Playing with Disk Alignment

Over a year ago I first read about disk and partition alignment on the blog of Jimmy May (blog/twitter).  I always wanted to look into this further, but there was always a major hurdle to doing this in my environment, almost every one of my production servers is a VM, and the storage for them comes from our enterprise SAN.  The complexity of the abstraction of the SAN configuration, where the LUN is carved from, the VFMS, VMDK, and finally Windows OS essentially made it so that trying to figure this out wasn’t worth the effort (well maybe it would have been but I never tried hard enough to know).  This year, Kendal Van Dyke(blog/twitter) did a pretty comprehensive series on the subject as well, that had my interest but once again I never did anything with the information.

Recently we had the need for a physical SQL Server due to needing more resources than we could put into a VM.  Performance was important, however, based on the usage characteristics we opted for a 6 disk RAID 5 for the data file for the database.  The database will be 80-90% read and 10-20% writes, with all writes occurring as a data load through ETL over night (this being roughly 7pm-7am).  As a part of setting up the server I decided to double check the partition alignment after having Windows Server 2008 R2 Enterprise installed.  Disk 0, a two disk RAID 1 for the OS only had a partition offset of 16K???  This disk was partitioned by HP SmartStart, and after all the best practices information out there on the subject, it apparently still doesn’t use a valid offset when it creates a partition.  Luckily it is east to fix pre-production, it just requires reinstalling the OS.  The disk arrays for the database files and log files were partitioned by Windows 2008 and therefore had a partition offset of 1024K.  All is good right?

I decided to run a series of SQLIO checks against the drives, checkout Brent Ozar’s(blog/twitter) article SAN Performance Tuning with SQLIO - SQLServerPedia, and I was surprised at how low the numbers that I was getting from the RAID 5 disk.  I knew I’d pay penalty for writes with RAID 5, I warn people about this all the time online, but my numbers still seemed to low to me.  They weren’t that bad, but they weren’t what I expected either, so I hit up Twitter and started plugging questions. 

I was slightly surprised to get an immediate response from Greg Low (blog/twitter), at least until I remembered that the middle of the night for me is daytime for him.  I also hit the web and did some searching for a IOPS calculator and happened upon an awesome online tool called Wmarow’s disk and disk array calculator.  A few questions of Greg, some calculations on the online tool and it seems that I am still falling short of the mark, though the mark was lower than I thought it would be.  It turns out that the drive was formatted with the default NTFS allocation unit size.  Reformat the drive using 64K allocation size and rerun the tests and the numbers looked better, and were more in line with what the IOPS calculator online said they should be.  I have to compile some information and make graphs but I’ll try and put another post up showing the differences between the different configurations and tests I ran.

For further information on this subject see Jimmy May’s excellent whitepaper on MSDN:

Disk Partition Alignment Best Practices for SQL Server

Published Thursday, November 19, 2009 8:58 PM by Jonathan Kehayias

Comments

 

Mike Walsh said:

Good to hear the impact of the discussions and settings. Looking forward to seeing the graphs, one more arrow to put in my quiver of justification for paying attention to this.

November 19, 2009 9:08 PM
 

ALZDBA said:

Very nice refs Jonathan !

Looking forward to your test findings !

November 20, 2009 2:47 AM
 

Dave Merida said:

Hi Jonathan,

I recently went through an exercise at my office to illustrate how important it is to perform disk partition alignment and setting the proper NTFS allocation size.  I even managed to squeeze marginal improvement out of a 2 disk, RAID 1 array to show my point.  Glad to see you were able to do it as well!

November 25, 2009 4:07 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement