Optimal Disk Setup for OLTP SQL Server

Question

We have a high transaction (lots of reads and writes) database server (running SQL 2005) that is currently set up with a RAID 1 OS partition (C:) and a RAID 5 data/log/tempdb partition (D:). The C: has 2 drives and the D: has 4 drives. The server has around 300 databases ranging from 10MB to 2GB in size.

I have been reading up on best practices for partioning the disks, but would like some opinions on our setup since we are so limited in the number of disks.

It seems like RAID 10 is popular, but I dont think we could use it with only 6 total disks to work with.

Thanks.

Update

I went with 3 RAID 1 Partitions (2 disks each)

• Partition 1: OS, TempDB, Backups
• Partition 2: Logs
• Partition 3: Data

Answer 1

A common "best" way to set up RAID volumes with only 6 spindles is:

• Volume 1, operating system and SQL transaction log. 2 disks in mirror (RAID1) with BBWC. Write caching of the transaction log is very important for good performance, as the SQL engine waits for log writes to complete, and caching the writes reduces write latency significantly. (BBWC means Battery Backed Write Cache, i.e. a controller with write caching turned on and a battery backup unit attached to the controller.)
• Volume 2, remaining Database files. 4 disks in RAID 10 (or RAID1+0, or RAID 0+1).

The placement of the OS really doesn't matter so much. When the system is up and running, the OS should gradually enter a steady state where the OS is only occasionally touching the disks. If you need to put the OS on the 4-disk RAID 0+1 array, then don't worry about that.

Additionally, you must ensure that partition boundaries (and hence filesystem blocks) are aligned with RAID stripe boundaries.

The above setup may or may not work for you -- you will lose some capacity when you say goodbye to RAID5. With modern disk drives you probably have disk space enough (I/O per second often becomes a bottleneck before capacity). With older drives, maybe capacity will be a limiting factor.

Kendal Van Dyke has written a good overview of different RAID levels, partition alignments, and their performance for a SQL server workload.

Answer 2

How about set all 6 disks in one RAID 10 and set one partition for the OS and another (1 or 2) for data ? Given that you would avoid heavy usage of swap by add plenty of RAM into the machine ?

Answer 3

If you have room on your C: volume consider moving your very active databases' transaction log files and / or your TempDB data and log files to it. RAID 1 is better then RAID 5 for writes.

Invest in as much memory as possible to reduced the amount of disk IO (memory's currently cheap). This will improve your reads. If you're on a 32 bit environment remember that you'll have to configure AWE to address memory above 4GBs.

Answer 4

With the limited number of drives, it's impossible to get the setup that's optimal. That would be separate disk sets for:

• System
• Data
• Logs
• TempDB

If you can afford the space hit of losing one disk (you've lost 1 out of the 4 in RAID 5 space wise for the parity, you'd lose a 2nd in RAID 10 space wise), then reconfiguring the drives would likely increase the performance somewhat, if the bottleneck is the disk.

Are you seeing specific issues? Or are you just looking to optimize the setup before there are any?

Answer 5

In addition to the advice K Brian Kelley gave also look into partition alignment. If you're using Windows 2008 you shouldn't have to worry as partitions are automatically aligned for you but on Windows 2003 you'll need to do this manually. Read this whitepaper to understand the who/what/why you should do this. You can get up to 30-40% performance boost by just aligning your partitions.

http://msdn.microsoft.com/en-us/library/dd758814.aspx