SQL Velocity

Windows Disk Alignment

A quasi authoritative guide to improving the performance of Microsoft SQL Server running on EMC Symmetrix arrays

Overview

While attending the SQL PASS Summit in Seattle this last November, http://www.sqlpass.org/summit/na2010/, I was asked an interesting question. A DBA approached me after attending a session featuring Jimmy May from the Microsoft SQL CAT Team, http://blogs.msdn.com/b/jimmymay/. The DBA knew that he should align his Windows 2003 partitions, but his Storage Administrator stated that the Symmetrix array alleviated the need to perform partition alignment.

His question: "How do I convince the SAN and Windows administrators to align my partitions and improve performance?"

My first thought was shock. I have been dealing with performance issues due to partition alignment since the days of Windows 2000. As I was thinking about how to answer the question I realized that a non-windows administrator could easily come to the conclusion that an advanced disk array would fix anything bad the operating system threw at it.

The simple answer to this loaded question:

• When you create a new partition in Windows Server 2008, the operating system aligns the partition for you.
• If you upgrade Windows to Server 2008 (or R2), or move a partition from an earlier version of Windows, you need to partition align it.
• If you create a new partition using Windows 2000, or Windows 2003, you need to partition align it.

The Problem

Now for the good part; "Mr. Smarty pants blogger, why the heck do I need to do that?" That gentle reader is why we are here today!

There are a ton of great documents from both EMC and Microsoft about the topic of alignment, so I shall not elaborate on the topic, rather; I am going to go over a quick summation and give you the links.

A quick overview of the Symmetrix system;

• Symmetrix uses a Fixed Block Architecture (FBA) when it presents storage to Microsoft Windows. For today's discussion the important notes about FBA are:
  • DMX-2 and earlier systems use a 32K track
  • DMX-3 and later, including V-Max systems, use a 64K track

Windows versions prior to 2008 assume that the physical hard disk uses a 63K track when it presents storage to windows. Windows creates a 63K hidden sector that holds all the information needed to read that partition (including things like the Master Boot Record, and the Disk Signature).

This creates windows NTFS file structures that are not aligned to the underlying V-Max FBA tracks. Assuming a 4K standard allocation unit for the Windows partition, every 16^th 4K I/O will create an extra I/O. This can turn into a huge performance hit when Microsoft SQL server reads 64K extents. Each 64K I/O will cause the storage system to generate an extra I/O request.

The Storage administrator's belief that cache would fix this is not correct because the simple fact is; extra I/O is, well, extra I/O. Each extra I/O sent to the array consumes some amount of compute power, bandwidth, and memory to process the I/O. A properly sized Symmetrix array may not show any performance degradation, but why add extra unnecessary overhead? Taken at scale, many Windows systems, that are all unaligned, will create massive performance degradation.

A Windows Volume that is aligned to the 64K Symmetrix track will not generate the extra I/O. This, ladies and gentlemen, can result in a real cost savings. Jimmy completed a recent Blog entry directly tying business cost to misalignment at scale: http://blogs.msdn.com/b/jimmymay/archive/2009/05/08/disk-partition-alignment-sector-alignment-make-the-case-with-this-template.aspx.

The Solution

For those folks that are not interested in full enlightenment from these great papers listed at the end of the post, use the diskpart.exe cliff notes below to align a new partition:

Note: You should really read these papers, If you break something because you did not read up on the subject, it is not my fault. Really – read them. People worked really hard on them. I WORKED REALLY HARD ON ONE OF THEM!

WARNING: Aligning a Windows Partition is data DESTRUCTIVE. If you have a Windows 2000 or 2003 mis-aligned disk you need to migrate onto an aligned disk. Running Dikspart.exe or Diskpar.exe on a disk with existing data WILL DESTRY THE DATA!

Note: Diskpart.exe is available in Windows Server 2003 SP1 – prior to SP1 Diskpar.exe is used. See the EMC white papers at the end of the post for more information.

This code (taken from Jimmy's white paper) is a huge time saver:

Diskpart

list disk

select disk <DiskNumber>

create partition primary align=<Offset_in_KB>

assign letter=<DriveLetter>

format fs=ntfs unit=64K label="<label>" nowait

First use either the Power Path GUI, or Powermt.exe to map devices from the Symmetrix array to a Windows Disk number:

powermt.exe display dev=all

This command shows that Windows disk 3 is really Symmetrix Device 0057 from the 1111 V-Max array:

Pseudo name=harddisk3

Symmetrix ID=000111111111

Logical device ID=0057

Looking in Diskpart.exe we find, create, offset, and format device 0057 as F$ with an alignment offset of 1024 (that is 1024 blocks or, 64K) and an NTFS allocation unit of 64K (useful for great SQL Server data file performance – don't use this for SQL Server Analysis services volumes. For Analysis Services 4K is a better allocation unit):

C:\>diskpart

Microsoft DiskPart version 6.0.6001

Copyright (C) 1999-2007 Microsoft Corporation.

On computer: MYTESTBOX

DISKPART> list disk

Disk ### Status Size Free Dyn GPT

-------- ---------- ------- ------- --- ---

Disk 0 Online 186 GB 0 B

Disk 1 Online 100 GB 0 B

Disk 2 Online 120 GB 0 B

Disk 3 Online 150 GB 150 GB

DISKPART> select disk 3

Disk 3 is now the selected disk.

DISKPART> create partition primary align=1024

DiskPart succeeded in creating the specified partition.

DISKPART> assign letter=F

DiskPart successfully assigned the drive letter or mount point.

DISKPART> Format FS=ntfs unit=64K label="F$_Data1" nowait

If you need to run this on a lot of disks at the same time you can use the diskpart.exe scripting engine:

Diskpart.exe /s <script>

A working example of a single volume Diskpart.exe script file (this script assumes you are going to format a SQL server data file volume with a 64K NTFS allocation unit):

select disk 3

create partition primary align=1024

assign letter=F

format fs=ntfs unit=64K label=" F$_Data1" nowait

Resources

Please read the following white papers for more information:

• If you are an EMC customer you can search Powerlink.EMC.Com (requires a customer or partner logon to access content):
  • Part number: 300-004-075; Aligning GPT Basic and Dynamic Disks for Microsoft Windows 2003
  • White paper: Using diskpar and diskpart to Align Partitions on Windows Basic and Dynamic Disks
• Anyone can download the Windows server on Symmetrix white paper from (Pages 25 through Page 27 cover alignment):
  • http://www.emc.com/collateral/hardware/white-papers/h6665-symtrx-microsoft-windows-server-wp.pdf
• SQL CAT White Paper on Windows Disk Alignment for SQL Server (note the great technical reviewer <cough>).
  • http://sqlcat.com/whitepapers/archive/2009/05/11/disk-partition-alignment-best-practices-for-sql-server.aspx
  • http://msdn.microsoft.com/en-us/library/dd758814.aspx

Scalable Shared Database Part 5

Performance

This post is an extension of the performance section of: http://www.emc.com/collateral/hardware/white-papers/h8129-performance-scaling-sql-wp.pdf. Writing a white paper requires some brevity lest it turn into unreadable minutia. Thank goodness for blogs.

Scalable shared database presents a really interesting lesson in I/O performance. As performance demands increase the DB architect has numerous tools available in the performance toolkit. Humans like to file things into boxes, Database workloads are no exception. We like to separate database systems into one of two categories:

• OLTP
• Data warehouse.

An OLTP workload is understood to create a random I/O pattern. Data Warehouse workloads are understood to be sequential. Life has a way of crushing pre-conceived notions. Allowing a group of analysts to query an OLTP server can cause a normally random workload to become a combined random and sequential workload. This increase in workload, especially a different type of workload, can overwhelm the basic OLTP functions of the database.

The most basic way to ensure that the OLTP system can continue to function as it was originally designed is to split the reporting functions onto a separate dedicated reporting server. Over the years I often used Log shipping to keep this reporting server in sync with the master OLTP database server. Unfortunately this required us to take the Reporting server offline whenever updates were applied.

The analysts do not like their reports to go offline. Using scalable shared database, we can use the replication functions of the storage array to keep reporting servers in sync, and load balancing technology to obscure refresh operations from the reporting system users.

Note: When speaking of scalable shared database performance requirements remember that by allowing the use of a single transaction server, and many reporting servers, we are drastically increasing the performance load placed on the storage array.

As described in my earlier post, http://sqlvelocity.typepad.com/blog/2010/08/windows-performance-counters-for-windows.html, the servers and storage array form a closed performance system with measurable performance characteristics. Little's law gives us an algorithm to predict the performance of a closed system. A single database server can exert three different types of I/O patterns:

• Random
• Sequential
• Large Block Random (I have also called this excessive head seeking).

The majority of deployed storage uses spinning disks. This presents an interesting problem. Spinning disks react differently to each of the major performance regimens. Understanding this performance is the key to designing a storage system that will achieve given performance requirements.

Modeling Disk Performance

To simply calculate Random performance, divide the rotational speed of the drive by 60 seconds. A 10,000 RPM drive rotates 166 times every second. A 15,000 RPM drive rotates 250 times a second. Because the drive head takes some time to move between disk tracks then we should also account for the time this movement (or seek) takes. Luckily this can be tested and both the average and maximum seek time are provided by drive manufactures.

Modeling disk performance becomes viable with the proper algorithm. The following example will help to calculate the random read performance of a spinning hard disk.

• Start by looking up the average seek time of a given disk drive model.
• Calculate the Maximum rotational latency (MRS) by dividing 60,000 (milliseconds per minute) by the drives rotational speed (revolutions per minute); 60,000/RPM.
• To calculate the number of I/O's per second (IOPS) divide 1000 by the drives Average read seek time (ARST) plus the Maximum rotational latency divided by 2.
• 1000/(ARST+(MRS/2))

For example a 10K RPM drive with a 7 millisecond seek time will produce 100 I/O's Per second;

• 1000/(7 + (6/2)) = 100 IOPS

A 15K drive with an 4 ms seek time will produce 166 I/O's Per Second;

• 1000/(4 + (4/2)) = 166 IOPS

Finally a 7200 RPM 146GB SATA drive with an 11 ms seek time will produce 66 I/O's per second.

• 1000/(11 + (8.3/2) = 66 IOPS

To keep the math really easy for my models I estimate that a 10K drive will produce 100 I/O's per second and a 15K Drive will produce 150 IOPS. This is an extremely conservative calculation that assumes that only one I/O is being issued at a time. As more I/O requests are issued performance will increase at the cost of latency. A good rule of thumb is to issue between 1 and 4 I/O's per second to each physical disk.

To increase Random I/O then we need to add more physical disks. If one 10K drive will provide 100 I/O's per second and we need 8,000 I/O's per second then 80 physical disks will be needed. If 80 disks worth of space are not needed then the space is not going to be used. Space will need to be traded for performance.

Sequential disk performance is much faster than random drive performance. Instead of being restricted by the time it takes to move the head between sectors, sequential performance is limited by the system interconnects between the disk and the server.

If we take a look at the 7200RPM 146GB SATA drive listed in the Random performance section above it will only perform 66 random IOPS. When tested sequentially it will perform 1311 IOPS. This is almost 20 times more performance from the same device. When tested with 64KB reads this 7200RPM drive can only generate 4 Mb per second when accessed randomly. A sequential access pattern will generate 81MB per second.

The only way to increase random performance is to aggregate disks. When a single drive is able to perform faster than 10 randomly accessed disks it is possible to decrease the number of disks in a system.

Scalable shared database performance

Planning a scalable shared database deployment will result in two distinct I/O performance pools. The primary database system, called the factory, will generate the first I/O profile. The reporting servers will generate the second I/O performance profile. As described earlier, the factory server will create a random, a sequential, or a large block random performance profile. Estimating this workload is tricky as the exact workload is often a variance of the three workload types, and it often changes over time. I like to plan for the worst case; large block random I/O. Unfortunately, large block random operations perform far worse than a standard random workload. Take this chart as an example:

The experiment compared a standard random workload with many sequential I/O's. As more work is generated the random workload performance increases. Compare this with many sequential workloads forming a now large block random workload. As the workloads increase the large block random workload continues to decrease overall performance. Quickly the random performance will eclipse that of the large block random workloads.

As stated in the paper; http://www.emc.com/collateral/hardware/white-papers/h8129-performance-scaling-sql-wp.pdf the best solution to this performance profile is to use Enterprise Flash Drives (EFD's) utilizing SLC NAND Flash technology (Head over to Wikipedia for an excellent description of flash technology: http://en.wikipedia.org/wiki/Flash_memory). If you are not able to utilize flash technology then plan for the absolute worst case scenario.

Using the equation: 1000/(ARST+(MRS/2)) replace Average read seek time with Maximum read seek time (you can find Maximum read seek time from the drive manufacturer – it is also called a Full stroke access time).

If we look at a Seagate 15K 450GB FC drive it has an average seek time of 3.4ms and a full stroke seek time of 6.6ms.

1000/(4 + (3.4/2)) = 174 IOPS Random Read

1000/(4 + (6.6/2)) = 136 IOPS Maximum Seek Reads

As you can see the performance threshold for a 15K drive will be closer to that of a 10K drive. Plan appropriately when configuring storage for the shared read only volumes. Remember that more than one server will be sending I/O to the shared volume, so it needs to have performance that can handle the load generated by all of the servers combined.

That wraps up the series on Scalable Shared Data Base. Please Post questions.

-Michael