THE SQL Server Blog Spot on the Web

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

Linchi Shea

Checking out SQL Server via empirical data points

Finding Disk Partition Offsets Made Easy

There were discussions on disk misalignment on this site. See my previous post on “Performance Impact of Disk Misalignment”, and Kevin Kline’s blog on “How to Improve Application and Database Performance up to 40% in One Easy Step

 

But thanks to Jimmy May's PASS 2008 presentation on the I/O performance impact of disk partition misalignment, I have been hearing a lot about this topic yet again. Even though I thought the issue has long since been settled, it turns out that there is a huge room for improvement in practice.

 

Now, in practice, if you need to check whether your disk drives are properly aligned, it can be a labor intensive effort if you have to resort to diskpar.exe or diskpart.exe to get the offset of each disk drive in your environment unless you only have a few servers. If you do have a large number of servers, and want to scan the disk drives on your servers for their partition alignment, you may find the attached utility useful.

 

In the attached zip file, you’ll find an executable—find_partition_offset.exe—that takes a server name on the command line and gives you the offsets of the partitions on a given server, and a Perl script—find_partition_offset.pl—that is a wrapper around find_partition_offset.exe. The Perl script takes the name of a text file that has a list of server names, one on each line, and loops through these servers to report the partition offsets. You can execute the Perl script as follows:

 

cmd>perl find_partition_offset.pl servers.txt

 

For your convenience, I’ve included the C# source code for find_partition_offset.exe.

Published Monday, November 24, 2008 7:21 PM by Linchi Shea

Attachment(s): find_offset.zip

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

 

Jimmy May, Aspiring Geek said:

Thanks for the call out, Linchi.  It was great finally getting to meet you at PASS.

For those who are interested, my latest-&-greatest deck is here:

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

It has brand new information on perf results out in the field from folks such as Joe Sack, Nico Jansen, Matt Landers, & Jason McKittrick.  It also has new graphics updated to reflect RAID arrays.  Lastly, it has info on the differences in analysis of basic & dynamic disks.

Stay tuned for the white paper we're writing for SQL CAT, currently under review.

November 24, 2008 7:11 PM
 

James Luetkehoelter said:

Also nice finally meeting you Linchi - and good post. This is a very confusing issue to most.

November 24, 2008 9:14 PM
 

Linchi Shea said:

Nice to have finally met you both as well, Jimmy and James!

November 24, 2008 9:42 PM
 

Jacob said:

u r blog Is very  nice

November 25, 2008 8:55 AM
 

KKline said:

Great post, Linchi.  This is a really useful utility for the community!

-Kev

November 25, 2008 4:31 PM
 

Chris Wood said:

Linchi,

I have tried to run the exe and have someone looking at compiling the C# source using Visual Studio 2005. I was running the exe and it throws an error and my fellow worker gets some undefined references from compiling the C#. Can you tell me what I need to run the exe file please?

Thanks

Chris

November 27, 2008 5:38 PM
 

Linchi Shea said:

Chris;

I compiled the C# code with the following command line under .NET 2.0:

cmd>csc /debug+ find_partition_offset.cs

Since this is such a simple program, I didn't bother with error handling. But I don't have any problem using the exe. What error message are you getting? I wonder if you are running into some Windows permission problems (just a guess).

November 27, 2008 11:35 PM
 

Chris Wood said:

Linchi,

I have copied the exe to an attached drive of a server and am logged on with a local admin account. If I double click it I get this

An unhandled exception ('System.IndexOutOfRangeEception') occured in find_partition_offset.exe[10680]

Chris

November 28, 2008 11:24 AM
 

Gordon said:

For the powershell junkies out there...

$a = Get-WmiObject -class Win32_DiskPartition

foreach ($obj in $a)

   {if (($obj.StartingOffste / 1024) -ne 64)

     {Write-Host $obj.DiskIndex

      Write-Host $obj.Index

      Write-Host ($obj.StartingOffset / 1024)}}

November 29, 2008 11:17 PM
 

Jared said:

Thanks for the tool, Linchi.  I just ran it on a SAN-attached server, and am finding an offset of 1048576 (=1024x1024=2^20).Output follows:

C:\Program Files (x86)\Find Partition_Offset>find_partition_offset.exe WIN-OG1BS

WTVBN9

MachineName=WIN-OG1BSWTVBN9

Disk=0,Partition=0,Drive=C:,StartingOffset=1048576

Disk=1,Partition=0,Drive=D:,StartingOffset=1048576

Disk=2,Partition=0,Drive=E:,StartingOffset=1048576

Disk=3,Partition=0,Drive=F:,StartingOffset=1048576

Disk=4,Partition=0,Drive=G:,StartingOffset=1048576

C:\Program Files (x86)\Find Partition_Offset>

Does this seem unusual?  I guess I was expecting something more like 65024.

Notes: All of the Disks, including Disk0, are SAN-attached (yes, this server boots from SAN)  Server OS is Windows 2008 Standard x64.

March 18, 2009 6:40 PM
 

Keif Gwinn said:

On Vista and Server 2008 you can use the following command to check your starting offsets on a single machine.

wmic partition list full

April 28, 2009 5:48 AM
 

mark cleary said:

In one eminently readable line:

wmic partition get BlockSize, StartingOffset, Name, Index

but it helps to remember that 2^20 is 1048576.

In VBscript:

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

July 6, 2009 2:33 AM
 

mark cleary said:

OK, still one line but not readable. Tells you whether the offsset is a multiple of 1 MB by calculating the offset modulo 1MB. If the answer is 0,you are good to go.

FOR /F "usebackq tokens=2 delims==" %A IN (`wmic partition get startingoffset /Format:list`) DO set /a (%A-(%A/1048576)*1048576)

Can change 1048576 to another constant (say 32768 for 32KB or 65536 for 64 KB) if you know your controller cache line size or RAID stripe size.

You need another line to find out parition you are talking about.

wmic partition get name, index

July 6, 2009 3:55 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

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