Jimmy May's Blog

SQL Server Performance, Best Practices, & Productivity

Disk Partition Alignment (Sector Alignment) for SQL Server: Part 2: Adding HP EVA 8000 to Veritas Enterprise Administrator “Track Alignment Settings” Dialog

Disk Partition Alignment (Sector Alignment) for SQL Server: Part 2: Adding HP EVA 8000 to Veritas Enterprise Administrator “Track Alignment Settings” Dialog

  • Comments 4

 

Background

In a previous post I documented the importance of disk partition alignment as a foundation for optimal disk I/O performance.  In the lab & in the field we’ve commonly found performance enhancements of 30% - 40% by a variety of metrics. 

 

Though Windows Server 2008 provides for 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 & Veritas Enterprise Administrator

Veritas confirms that disk partition alignment is a best practice.

Veritas Storage Foundatio 5.0 for Windows Best Practices for Storage Management

http://eval.symantec.com/mktginfo/enterprise/white_papers/ent-whitepaper_vsfw_5.0_best_practices_for_storage_mgmt_02-2007.en-us.pdf

pp. 5, 20, 2007

 

Disk Partition Alignment & HP EVA SANs

There is a great deal of confusion with regard to disk partition alignment & HP EVAs.  For example, HP states that starting with the 5.xxx versions of StorageWorks Enterprise Virtual Array (EVA) XCS Controller Software, the need for partition alignment is eliminated, adding that explicit alignment “neither enhances nor detracts from EVA sequential performance”.

HP StorageWorks 4x00/6x00/8x00 Enterprise Virtual Array Configuration Best Practices

http://h71028.www7.hp.com/ERC/downloads/4AA0-2787ENW.pdf

p. 27, August 2007.)

 

HP’s claims are intriguing, but corroborating data is lacking or in dispute.  For example, the HP citation explicitly refers to sequential performance yet ignores random performanceand SQL Server OLTP systems rely on optimized random performance.

 

My Recommendation

From my perspective, the jury is out with regard to disk partition alignment & HP EVAs.  HP has posted an interesting paper on the efficiency of the EVA cache:

EVA Cache – Why so Efficient?

http://h71028.www7.hp.com/ERC/downloads/4AA1-7945ENW.pdf

September 19, 2007

 

Yet based on my experience & evidence, I advocate erring on the side of alignment, especially since implementation is so simple.  I hope to perform definitive experiments & will post the results.

 

The Problem: Track Alignment Dialog of the Veritas Enterprise Administrator UI

Though Veritas documents the importance of disk partition alignment, the HP EVA 8000 is missing from the Track Alignment dialog of the Veritas Enterprise Administrator UI.

 

The Fix

At my recommendation, a customer, Brent Dowling from the state of South Dakota contacted Symantec / Veritas support.  Through persistence & cleverness he found a fix which worked:

 

The Solution/Workaround

Veritas Official Fix

We do not have a track alignment entry for this array at this time; however, it would be possible to manually create this by creating the following Registry key and values:

[HKEY_LOCAL_MACHINE\SOFTWARE\Veritas\VxSvc\CurrentVersion\VolumeManager\TrackAlignment\HP\HSV210*]

"Enable"=dword:00000001

"Setting"=dword:00000080

"Model"="HP StorageWorks EVA8000"

HSV210* will be the wildcard used to detect the array, and this will cover the HSV210 Product ID entry for the customer's array. We use the wildcard at the end as it is not uncommon for arrays to append additional characters to the end.

Customer Unofficial Fix

My customer realized that in his case the solution required removing the asterisk from the reg key, i.e.,

[HKEY_LOCAL_MACHINE\SOFTWARE\Veritas\VxSvc\CurrentVersion\VolumeManager\TrackAlignment\HP\HSV210]

Note:  When creating the dword, be sure to select hexadecimal when you insert the value 80; this corresponds to the decimal value 128. 

 

According to my customer’s experience & experiments, there is no difference in performance between an starting partition offset of 64KB or 128KB.

 

How it Works

Veritas Enterprise Manager is a powerful tool, enabling among other things the skilled admin to sling bits around with aplomb.  It’s frankly pretty impressive.

 

Veritas does its magic by converting Windows basic disks to dynamic disks.  There are pros-&-cons to this conversion, & Veritas does a nice job of leveraging the pros.

 

Disk partition alignment is a best practice.  By definition, a Windows volume cannot be aligned.  However, a partition can & should be aligned.  The volume will inherit the partition offset of the underlying partition.  When the track alignment bit is enabled, Veritas automagically creates a partition , aligns it, & transmogrifies the partition to a dynamic volume.

 

Weasel Clause

I am neither endorsing nor criticizing any product cited here.  My mission as stated in my first post is to be of service to the masses who are in search of the elusive sqlservr.exe /faster switch.

 

Your mileage may vary.  Whenever deploying disk I/O subsystems, it is important to validate the configuration.

 

References

Disk Partition Alignment (Sector 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

 

Predeployment I/O Best Practices

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

SQL Server Best Practices Article

By: Mike Ruthruff

Contributors: Michael Thomassy, Prem Mehra

Technical Reviewers: Robert Dorr, Stuart Ozer, Juergen Thomas, Lindsey Allen, Burzin Patel, Bill Emmert, Bruce Worthington, Sanjay Mishra

 

Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000

http://support.microsoft.com/kb/929491

By: Robert Smith

 

 

Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.:
Assessment Consulting & Engineering Services

http://blogs.msdn.com/jimmymay
Performance is paramount: Asking users to wait is like asking them to leave. 

 

Leave a Comment
  • Please add 4 and 8 and type the answer here:
  • Post
Page 1 of 1 (4 items)