Disk Partition Alignment (Sector Alignment) for SQL Server: Part 2: Adding HP EVA 8000 to Veritas Enterprise Administrator “Track Alignment Settings” Dialog
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
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
https://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 performance—and 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?
https://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
Predeployment I/O Best Practices
https://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
https://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
https://blogs.msdn.com/jimmymay
Performance is paramount: Asking users to wait is like asking them to leave.