How to get better I/O performance with RAID for SQL SERVER
As SQL server ( any database server ) uses High Disk IOs we should align the Disks.
Would you like to know how to increase your servers I/O performance by up to 40%!? Well if so, then Disk alignment is one of Windows best kept secrets (or worse depending on how you see it.) If you’re running Windows Server 2000, 2003 or Windows XP with RAID, you are probably not getting the throughput you could if you had correctly aligned disks.
If you’re not familiar with the term disk alignment or sector alignment, in layman’s terms it goes like this. Windows like to write data to disk in 64k chucks. However Windows Server 2000, 2003 and Windows XP all incorrectly begin writing data at the 63rd sector. This means the first 1k of the chuck is written into one sector, and the remaining 63k in the next, and so on. The consequence of this behavior means that for every read and write, two sectors must be accessed from disk instead of one. This basically doubles your disk I/O.
Microsoft Windows Server 2000, Windows Server 2003 and Windows XP are all plagued with this problem. Any new volumes created on these operating systems (if not set correctly with diskpart – more on that in a minute) will be aligned incorrectly. Microsoft has addressed this issue in Windows Server 2008 and Window Vista, but upgrading an existing installation will not correct a disk that was originally formatted on one of the affected platforms.
To verify that an existing partition is aligned, perform the calculation that is described in the “More Information” section.
To align a disk partition on a RAID that has a 2,048-sector offset, follow these steps:
At a command prompt, type diskpart, and then press ENTER.
Type the following commands at the DISKPART prompt, and then press ENTER:
Note You receive output that resembles the following:
Disk ### Status Size Free Dyn Gpt
——– ———- ——- ——- — —
Disk 0 Online 37 GB 8033 KB
Disk 1 Online 17 GB 8033 KB
Disk 2 Online 17 GB 0 B
Disk 3 Online 17 GB 148 MB *
Disk 4 Online 17 GB 8 MB *
Disk 5 Online 17 GB 8 MB *
Disk 6 Online 17 GB 8 MB *
Disk 7 Online 17 GB 8 MB *
Disk 8 Online 17 GB 435 KB *
Disk 9 Online 17 GB 8 MB *
Disk 10 Online 17 GB 8033 KB
The list disk command provides summary information about each disk that is installed on the computer. The disk that has the asterisk (*) mark has the current focus. Only fixed disks and removable disks are listed. Fixed disks include integrated device electronics [IDE] and SCSI disks. Removable disks include 1394 and USB disks.
Use the select disk command to set the focus to the disk that has the specified Microsoft Windows NT disk number. If you do not specify a disk number, the command displays the current disk that is in focus.create partition primary align=1024
When you type this command, you may receive a message that resembles the following:
DiskPart succeeded in creating the specified partition.
The align= number parameter is typically used together with hardware RAID Logical Unit Numbers (LUNs) to improve performance when the logical units are not cylinder aligned. This parameter aligns a primary partition that is not cylinder aligned at the beginning of a disk and then rounds the offset to the closest alignment boundary.
number is the number of kilobytes (KB) from the beginning of the disk to the closest alignment boundary. The command fails if the primary partition is not at the beginning of the disk. If you use the command together with the offset =number option, the offset is within the first usable cylinder on the disk.
Type exit, and then press ENTER.
Click Start, click Run, type diskmgmt.msc, and then click OK.
In the Disk Management Microsoft Management Console (MMC) snap-in, locate the newly created partition, and then assign it a drive letter.
Use the NTFS file system to format the new partition, and then assign a cluster size