Disk Partitioning Offset

  • Article

What is this?

Following is an excerpt from SQL Server Best Practices Article published on Technet.

https://technet.microsoft.com/en-us/library/cc966412.aspx 

{

Volume alignment, commonly referred to as sector alignment, should be performed on the file system (NTFS) whenever a volume is created on a RAID device. Failure to do so can lead to significant performance degradation; these are most commonly the result of partition misalignment with stripe unit boundaries. This can also lead to hardware cache misalignment, resulting in inefficient utilization of the array cache. For more information on this, see Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000.

Volume alignment on NTFS volumes can be performed by using diskpar.exe or DiskPart.exe. There is no advantage of using one over the other. However, DiskPart.exe is the preferred method since it is now included as part of Microsoft Windows® and the functionality will continue to be enhanced. Diskpar.exe is a Windows 2000 Resource Kit tool and is most commonly used in versions of Windows prior to Windows 2003 SP1. DiskPart.exe is a disk configuration utility that is built into Windows; in Windows 2003 version SP1 or greater DiskPart.exe contains an ALIGN option that can be used to align volumes.

When aligning the file system keep in mind the following: 

  • Ideally, the offset used to align the volume should be supplied by the OEM of your particular storage array.

  • If the OEM does not provide an offset, the only way to determine the optimal offset is through testing.

  • A 64-KB offset (128 sectors) is a common value that works on many storage arrays and has been used with success in the SQL Server test labs (at the time of writing this paper). Vista™ and Windows Longhorn Server remove the need to manually establish correct alignment and use 1024 KB (2048 sectors) as a starting offset, which will probably work well for almost any array.

  • Diskpar.exe works only on MBR partitions. DiskPart.exe supports both MBR and GPT partitions. Alignment of dynamic volumes is not currently supported as of Windows 2003 SP1.

  • The value of the offset for diskpar.exe is expressed in sectors (512 bytes) and differs from the ALIGN option of DiskPart.exe, which uses a value defined in KB. For example “create partition primary align=64” creates a partition using a 64-KB offset.

To determine the current starting offset of a volume (or partition), you can either use Diskpar.exe with the –i option or WMI (as demonstrated in the following script) if Diskpar.exe is not available.

 strComputer = "." 
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2") 
Set colItems = objWMIService.ExecQuery( _     "SELECT * FROM Win32_DiskPartition",,48) 
For Each objItem in colItems     Wscript.Echo "Disk: " & objItem.DiskIndex & "  Partition: " & objItem.Index & "  StartingOffset: " & objItem.StartingOffset/1024 & "KB"     Wscript.Echo 
Next

For more information on DiskPart.exe, see DiskPart on Microsoft TechNet, specifically the "create partition primary" section.

}

 

I have been setting up this disk partition offset at all of my customer’s wherever a new SQL box or a new SAN deployment was planned out. As this is a task that could only be done while formatting hard drives.

I was following this at a customer place where some new clustered disk(s) were to introduced. the only challenge out there was that this customer was still running their systems on Windows 2000 Advanced Server.  (I know it’s a bit outdated, but still running their mission critical database system) and I never used DiskPart.EXE on windows 2000.

So, I downloaded the DiskPart.EXE from Windows 2000 Resource Kit and ran the usual command.

CREATE PRIMARY PARTITION ALIGN=64

and I could not believe on the result. The tool diskPart.exe did not liked my syntax for this command. and I could not do this task on windows 2000.

I went through a lo of reading and then came to know that  I should use DiskPar.EXE (no typo – It’s DiskPar.exe - Without the ‘t’ ).

then I began my quest to download the tool. After a long search, I managed to get the tool.

you could get it from My SkyDrive folder here

The usage is simple.

 

DiskPar.Exe [-?] [ -i | -s ] DriveNumber

Where:

-? displays command-line help.

-i DriveNumber   :queries drive layout and partition information.

-s DriveNumber  :sets new partition information.

you would get the Drive number in the Disk Management GUI.

 

also note that you would need to restart two services for the disks to be used again as needed.

See Technet  for more information

 

Hope this helps.