Optimal drive configuration for SQL Server 2008R2

Question

I have a fairly busy database server running SQL Server 2008 R2 that has the following setup:

• SATA RAID 1 (2 Drives) - OS / Programs
• SAS RAID 10 (4 Drives) - Sql Database Files (data and logs)
• SAS RAID 1 (2 Drives) - TempDB (data and logs)

Assuming I can't add additional drives into this server, have I made the best use of the configuration I have available? Or should I consider another scheme here where logs are isolated from the data files, for example?

Update:

For those that requested further hardware details:

• The SATA drives (used for the OS / Program partition) are: WD 7200 RPM 3 Gb/s 3.5 Inch SATA
• The SAS drives used in the other arrays are: Seagate 15K RPM 6 Gb/s 3.5 inch SAS
• The RAID controller used is an: LSI 9260-8i SAS/SATA 6 Gb 8 port

Update 2:

Based upon the feedback I've received, it looks like I have the following viable options to choose from - I will award the bounty to someone that can tell me which is likely to be the best in the environment that I've outlined:

1. Leave everything as is - I probably won't do much better
2. Move my 2 SAS RAID 1 drives into my existing RAID 10 array to have it composed of 6 disks in total
3. Move my log files onto the SAS RAID 1 and/or relocate TempDB (data or logs) back to the RAID 10

Answer 1

Variants of this question come up semi-regularly:

• SQL Server build
• Help me choose a RAID level combination for a SQL Server 2008 instance
• How should I configure these disks on a SQL Server for a BI configuration?

There are also occasional bun fights about the data/log separation "best practice".

• Placing Transaction Log on Separate Volume [solid state]?
• SQL Server - Separating data, log, and TempDB files on a SAN

Without more detailed analysis of what this server is doing, the same advice applies as given previously.

• RAID 1 for OS
• RAID 10 (6 disk) for data/logs/tempdb

There is rarely any point in a split with so few spindles available. A single array with a larger IOPs capacity will typically soak up the lumps and bumps of your workload better than 2 smaller arrays.

One variant that can be worth testing is putting tempdb on the OS drive. Only do so if you have a representative workload that you can replay repeatedly, to ensure a fair comparison of the configuration. If you go for this arrangement in production make sure tempdb growth is restricted so you don't inadvertently consume all free space on the OS drive.

Given that your OS drives are 7200RPM coasters, I'd be surprised if the tempdb on OS drive config bore any benefit.

Answer 2

It all depends on your workload, but with only 6 drives it does limit your options. If your workload is not heavily dependent on tempdb for things such as sorts, hash tables, and snapshot isolation, then you might be better off using the 6 SAS drives together in RAID 10. However, if you know or have the metrics to prove that tempdb is heavily utilized, then you should keep it separate as you have.

Answer 3

The actual answer depends on your needs but generally "put data and log on different arrays" is of higher importance than "put tempdb on its own array".

Given the number of drives you have available, my starting point would be:

1. Two drives, RAID 1 - Operating system, executables, pagefile.
2. Four drives, RAID 5 - All data files (alternatively, RAID 1+0)
3. Two drives, RAID 1 - All log files

What should do is use SQLIO to test the performance of different drive configurations.

Answer 4

It very much depends on what you mean by "very busy": different workload patterns (write heavy or not, bulk operations common or not, level of concurrent access, to name but three of the many variables) can have a drastic effect on the performance of any given spindle arrangement.

For a write heavy situation separating the logs from the data can make a significant difference as each write involves updating both the log and the data files, so involves a fair amount of extra head flipping if both sets of files are on the same set of spindles.

Without further reference to your workload (and the spec of those drives and any controller that is between them and the machine) I'd be inclined to go for three volumes: one for OS+programs and tempdb (data), one for main DB data, and the third for logs (both tempdb and main DBs).

Of course if your workloads are all very light on write operations then you shouldn't spend too much time worrying about keeping data and logs separate, as it'll make little performance difference and dedicating a whole volume for them would be quite wasteful of available space.

Answer 5

Depending on what you are using the DB for (OLTP vs warehousing) your config looks like a good general config. If you had more disks, you would have more options.

You could get better performance if you switched the disk for your TempDB to RAID 0 (stripe). It increases your risk for failure, but since TempDB only buffers data, you can't experience data loss. So most people consider that a reasonable trade-off. Just keep a spare around (or a hot-spare).

One thing you didn't mention, but could try (if you haven't already): Microsoft recommends splitting your TempDB up over several files (one per CPU). Of course, it is best if they are on separate disks, but simply having separate files helps. http://msdn.microsoft.com/en-us/library/ms175527(v=SQL.105).aspx