THE SQL Server Blog Spot on the Web

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

Kevin Kline

How to Improve Application and Database Performance up to 40% in One Easy Step

For some reason, the concept of disk alignment partitioning is not widely known in the SQL Server world and yet it can easily yield between 30-40% better performance in most database applications and much higher performance improvements for ETL applications.  (Also for some reason, Exchange people seem to know about this much better than SQL Server people do.)  You can see imperical evidence of this performance hit at Linchi Shea's blog here.

Now before you begin thinking that this has something to do with SANs or with SQL Server partitioning (added in SQL Server 2005), think again.  This is actually a server OS problem that applies to all of Microsoft's pre-Windows Server 2008 server OS'es, plus Windows XP, on both 32- and 64-bit architectures.  An oversimplified explanation is that these server OS'es like to write data in 64k chunks onto disks with 64k sectors.  However, the OS'es create their very first chunk at only 63k in size.  That means every subsequent chuck writes at least 1k to the previous sector, resulting in every read and write going to two sectors and resulting in two I/Os per operation instead of one I/O.

If you've never fixed the problem then your databases running on Windows Server 2000 or Windows Server 2003 are giving up 30-40% in performance without any idea that it's happening.  On the other hand, fixing the problem isn't difficult.  You can fix this problem by issuing a simple command - DISKPAR.EXE for Windows Server 2000 or DISKPART.EXE for Windows Server 2003.  (I've only scratched the surface here.  To fully understand the problem as well as see syntax for these commands, read Predeployment I/O Best Practices and this Microsoft Knowledge Base article.  DISKPART.EXE is also explained here on TechNet.)  When you realign your disks with DISKPAR or DISKPART, the disks will be wiped clean.  So you need to fully backup your databases first.  Once the disks are realigned, you can restore your databases to them.  Arrays configured under Windows Server 2000/2003 will remain misaligned even if you later upgrade the OS to Windows Server 2008.  You'll have to manually rebuild them using Windows Server 2008 or realign them with DISKPART to get the disks running with the correct sector alignment.

I encourage you to look at your disk subsystem very closely to make sure you're not missing out on this important performance improvement opportunity.  You can use the DISKPART -i command to see what the current sector offset is or you can use the WMI object written by Bob Duffy located here.

So get in there and realign all of those misaligned disks!

Enjoy,

-Kevin

 

Published Wednesday, October 08, 2008 10:33 PM by KKline
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jerryhung said:

I remembered seeing this recently (probably on SQL Server Central) talking about Disk Alignment 32K/64K

Will be the first thing I try when I re-build a server

The "disk wiped clean" part does sound... scary

October 9, 2008 10:40 AM
 

jchang said:

Kevin: bug Jimmy May to make his ppt on this public. Also, stress that the expectation is (small block random) IO Performance gain of 30-40%, not necessarily system level gain, depending on whether you are IO bound in a transactional app. The problem stems from the OS automatically using the first 63 sectors (of 512 bytes) for 31.5K for metadata. Most RAID controller default to 64K stripe size. In theory, this should lead to a 25% gain in 8K IO (2 out of every 8 acessess for 8K pages within a 64K extent requires multiple IO). So I suspect the remaining gain is from wiping your disk partition clean, effectively a full file system defrag.

October 9, 2008 10:57 AM
 

Adam Machanic said:

The "diskpart -i" syntax you mention doesn't seem to work in either 2003 or 2008 (I don't have a 2000 instance to test on).  I think you need to actually go in and do a script to get the offset information:

diskpart

> list disk

> select disk [n]

> list partition

October 9, 2008 1:46 PM
 

Scott R. said:

Kevin,

Thanks for raising awareness on this important but often overlooked issue.

A couple of related issues:

-  I’m not aware of a DiskPart -i option.  When I run DiskPart /? to find the options available, it shows /s and /? as the only options.

-  When you use the DiskPart sub-command List Partition (after you point it to a specific disk using the Select Disk sub-command), it shows the disk volume alignment value rounded to the nearest KB (1,024 bytes).  This behavior causes an unaligned disk volume (63 sectors – 32,256 bytes – 31.5 KB) to show as 32 KB, giving a false sense of volume alignment status.  I choose to use other tools to confirm disk volume alignment status, and to use DiskPart to create an aligned volume and spot-check the result (but not as a definitive volume alignment confirmation).

-  To accurately report on disk volume alignment status, I prefer to use the Sysinternals tool DiskExt (http://technet.microsoft.com/en-us/sysinternals/bb896648.aspx), which reports the offset of disk volumes (a specific volume or all volumes) as bytes – with no rounding!  At past IT sites that have used non-Microsoft volume managers (such as Symantec / Veritas Volume Manager), traditional methods may not accurately show the disk volume offset in all cases (as Symantec / Veritas has recommended other methods to align disk volumes).  Even in these cases, I had good experiences with DiskExt.

-  Most references to disk volume alignment suggest consulting with the storage hardware vendor to determine the preferred volume alignment offset, with the majority of cases being 32 KB or 64 KB but some higher.  All published volume alignment values I have seen to date are binary multiples (32 KB, 64 KB, 128 KB, etc.).  The vendor-recommended volume alignment value may be fixed for a given storage hardware model, or may vary depending on other storage configuration values (such as stripe size, etc.).  I have observed that the vendor-recommended values are often related to the storage cache architecture and the size of individual cache requests (which are often based on binary multiple sized caches and stripe sizes).  Joe touched on this in his reply above.

Prior to Windows Vista and Windows Server 2008, I chose to use a larger binary multiple volume alignment value of 1 MB (1,024 KB or 1,048,576 bytes) to cover all cases, on the basis that an aligned disk volume using a larger binary multiple alignment value is also aligned for all smaller binary multiples.  This offered a more vendor- and model-independent volume alignment process to follow (hopefully with fewer errors).  Little did I know that Microsoft would choose the same 1 MB disk alignment value as the default for Windows Vista and Windows Server 2008 for creating new disk volumes.

-  I have experienced some mixed results with disk volume alignment, and have heard of similar experiences from others.  I still believe it is a worthwhile best practice, but there may be other factors which may block the stated benefits of disk volume alignment.  Although I have no empirical proof of specific factors, I offer a short list of possible sources:

  *  The documented process aligns the disk volume, but it may or may not align individual disk files within that volume.  That is a function of the file system – NTFS in most cases.  It would be worthwhile to understand how NTFS places the overall free space for new files (aligned or not), how it allocates disk extents for files (with respect to file level alignment), and whether the various storage management structures within the NTFS file system (MFT, free space / in use bitmaps, etc.) are aligned or not.

  *  NTFS allocation unit size and its possible contribution to file level alignment.  I suspect that the default NTFS allocation unit size of 4 KB may negate any volume level alignment (even if overall NTFS free space is aligned), as files may only be aligned to the nearest 4 KB.  Similarly, I suspect that a 64 KB NTFS allocation unit size may work favorably for many situations (assuming that the start of all free space within the volume is similarly aligned), but possibly not all situations (storage subsystems requiring alignment at larger levels than 64 KB).  Note that I say “suspect” rather than “know”, because these are issues I don’t know for certain.

  *  NTFS files can be of any length, but their allocated disk storage is always to the nearest allocation unit size.  I am not aware of any mechanisms in place to “round-up” a file size to a desired file alignment value (assuming that the free space on an empty NTFS volume is aligned as desired).

  *  Disk volumes are sized in 1 MB binary multiple increments (I believe).  This assures that subsequent volume(s) on a single physical disk (or virtual disk in a RAID situation) containing multiple volumes are at least aligned as well as the first volume (up to 1 MB alignment).  Since most disk volumes are a single volume using all of the capacity of a given physical / virtual disk, this may not be a big factor, but is worth noting for those that may use such multi-volume physical / virtual disks.

  *  Sub-allocation of disk space within a storage subsystem to LUNs / virtual disks for use by a given server may have specific behaviors with respect to alignment that are different from other vendors and models.  Too many vendors, models, and variables to cover here.  As they say – ask your vendor!

  *  Fragmented disk files have multiple starting offsets (one for each fragment - each of which may or may not be aligned), which may also contribute to unaligned behavior on aligned disk volumes.  Another vote for keeping disk files as defragmented as possible (especially large database files).

I hope to gain a better understanding of these NTFS internal characteristics over time, and possibly get plugged into subject matter experts that “live and breathe” this stuff (and may be able to positively influence changes in the future).  Microsoft has made great strides in Windows Vista / Server 2008 with automatic 1 MB volume alignment.  I am hopeful they will pay similar attention to file level alignment in future versions or service packs (if they haven’t already).

Here’s to the day when the disk alignment processes (volume and file level) are automatic and don’t need to be thought about!  But until that day: I echo your advice to follow the disk volume alignment best practices.

Scott R.

October 9, 2008 1:56 PM
 

Jason said:

Shhhh, this is supposed to be the consultant's secret weapon. :)

October 11, 2008 12:17 AM
 

aspiringgeek said:

@various: diskpar -i works; diskpart (with a "t") is not valid.  The command I prefer is from PFE Robert Smiths article, KB 929491

wmic partition get BlockSize, StartingOffset, Name, Index

@Adam: diskpart reports in KB, not bytes, & is therefore not sufficiently granular (it rounds up the default 32,256 (31.5KB) to 32KB.  That's why I like the wmic command.

@Scott: Interesting commentary!  The work we've done so far has shown unequvical improvements, with the exception of a sequential read with 256KB block size which might be considered within experimental error.  See http://joesack.com/WordPress/?p=190

@Joe Chang: I *finally* posted the deck.  It's one of a series of posts I'm going to do on disk partition alignment.  I cover a lot of topics, including additional experimental details & real life results--including a major telecom firms decision to re-build multiple TBs of data across their enterprise.  

In spite of Windows Server 2008 out-of-the-box alignment for new partitions, disk partition alignment remains a relevant technology.  Disk partition alignment will remain relevant until Windows Server 2003 is retired & existing partitions are re-built.

Disk Partition Alignment for SQL Server: Part 1: Slide Deck

http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx

Be aware a lot of things change with regard to dynamic disks!  I invite you to subscribe to my blog where I'll be posting new info as it comes to my attention.

October 15, 2008 6:48 PM
 

aspiringgeek said:

PS  I hope this doesn't sound like shameless self-shilling, but I'm speaking on this topic next month at PASS.  All y'all come!

In the meantime, I invite any exciting new insights:

jimmymay at microsoft dot com

aspiringgeek at live dot com

October 15, 2008 7:12 PM
 

Linchi Shea said:

There were discussions on disk misalignment on this site. See my previous post on “ Performance Impact

November 24, 2008 7:01 PM
 

KKline said:

Fantastic comments, everyone.  Thanks so much for adding to this important conversation.

-Kev

December 14, 2008 6:54 PM
 

J.ross said:

Hi Kevin,

Have you tried Paragon Alignment Tool which is recently released: https://www.paragon-software.com/business/partition-alignment/.

April 29, 2010 8:06 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

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