Sql-server – Optimal drive configuration for SQL Server 2008R2

hardwaresql server

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

Best Answer

Variants of this question come up semi-regularly:

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

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.

Related Question