Friday, July 13, 2012

Don't Dis the Disk when creating a new partition or volume for SQL Server. Here's a How To on how not to...

Scott Mattie's Blog - HOW TO: Create a New Partition or Volume with SQL Server in mind

This article will show you how to create a new primary partition using Disk Management and Diskpart (preferred) in Windows from an empty unallocated partition or volume. This is in line with Disk Partition Alignment Best Practices for SQL Server article.

Partition Alignment in Windows Operating Systems

The way partition alignment works depends on the version of Windows being used and the version in which the partition alignment was created. The following sections describe how partition alignment works in Windows Server 2008, the Windows Vista® operating system, and Windows Server 2003 and earlier.

Windows Server 2008 and Windows Vista: New Partitions

In Windows Vista as well as Windows Server 2008, partition alignment is usually performed by default. The default for disks larger than 4 GB is 1 MB; the setting is configurable and is found in the registry at the following location:


However, if OEM setups are delivered (for example, with recovery partitions), even fresh installations of Windows Server 2008 having partitions with undesirable partition starting offsets have been observed.

Whatever the operating system, confirm that new partitions are properly aligned.

Windows Server 2008: Pre-existing Partitions

New partitions on Windows Server 2008 are likely to be aligned. Yet partitions created on earlier versions of Windows and become associated with Windows Server 2008 maintain the properties under which they were created. That is, in the absence of partition alignment being explicitly performed, these partitions are not aligned.

Windows Server 2003 and Earlier

Partitions created on versions of Windows up to and including Windows Server 2003 by default are not aligned. Partition alignment must be explicitly performed.

With that being said let’s get back on track to creating new volumes.

A primary partition is a type of partition created on a hard drive that can have an operating system installed on it. A primary partition functions as though it were a physically separate hard drive. By default on a basic MBR disk, you can create a maximum of 4 Primary partitions, or 3 Primary partitions and 1 Extended partition with up to 128 logical volumes in the extended partition on a single hard disk.

NOTE: You must be logged on in an administrator account for these commands to work.

If you disable the Disk Defragmenter service, then you will get the error below when you try to do anything in Disk Management. If you get this error, then make sure that the Disk Defragmenter service is set to only Manual.


Correctly setting up your disk is critical for SQL Server performance, yet it's something that many of us (including myself) don't really think about, let alone do. That's why I liked this HowTo, in that even a dev like me could understand it. :)

No comments: