20

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
Improve this question
2
  • Your 2nd update is asking exactly the same questions as your original question, so the original answer applies. "...which is likely to be the best in the environment that I've outlined"... you haven't shown us any analysis of the workload, just that it is "fairly busy", so again the same answer applies.
    – Mark Storey-Smith
    Mar 24, 2013 at 11:46
  • With High-Performance NVMe and other SSD's disks in the market, The RAID is no longer very important from the SQL server Disk Configuration Best Practices perspective. Disk Pools (Storage Spaces) are the way to go forward. You would, however, need to logically separate the volumes in order to better troubleshoot disk related performance problems.
    – Faces Of IT
    Jan 20, 2017 at 11:41

5 Answers 5

Reset to default
15
+50

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.

Improve this answer
7

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.

Improve this answer
2
  • Thanks for the answer, changing the config of the ranges is (unfortunately) not an option as this server is in production. I am curious about switching the tempdb back to the RAID 10 and putting all log files on the RAID 1 though - is this a smart option?
    – DanP
    Mar 20, 2013 at 20:52
  • 2
    Remember SQL Server needs to physically write all transactions to the log as part of the commit, so you want the fastest write performance possible within your config. RAID 10 offers better write performance than RAID 1, so I would leave the logs on the faster volume.
    – Patrick Keisler
    Mar 20, 2013 at 21:14
2

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.

Improve this answer
6
  • I would certainly describe our environment as a "heavy write" one - I will update my question with more detailed hardware specs on Monday.
    – DanP
    Mar 23, 2013 at 22:31
  • Any idea about the number of drives that OP has? I'm thinking more of the Raid 1 that he has for the log files. That doesn't sound like a great option for write purposes, and AFAIK log files are not read limited, but write..
    – Marian
    Mar 23, 2013 at 23:03
  • I have added additional specs about the hardware of the machine.
    – DanP
    Mar 23, 2013 at 23:16
  • 1
    This misunderstanding may be at the root of many of the data/log separation rants, so apologies but I'm going to call it out. "...as each write involves updating both the log and the data files" - no, it does not. Writes to data pages occur on checkpoint, not on every modification.
    – Mark Storey-Smith
    Mar 24, 2013 at 1:01
  • 1
    @DavidSpillett True, there will always be cases where the split is beneficial. The key is that you tested and validated that the configuration was beneficial for that particular workload.
    – Mark Storey-Smith
    Mar 25, 2013 at 13:29
2

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.

Improve this answer
15
  • This definitely represents the "other side" of the advice I've read. I wish there was a definitive method to determine which of the options would be better without resorting to "trying it out" in a production environment.
    – DanP
    Mar 20, 2013 at 23:45
  • 2
    @DanP Well, I personally would have gone with Mark's advice and chose a RAID10 with the rest of the drives (except OS). Log files are write intensive and need better performance than what RAID 1 has to offer, as far as I think of it. But I'm not a hardware expert. Just my 2 cents.
    – Marian
    Mar 23, 2013 at 23:05
  • 2
    I should mention that my opinion comes only from the experience of a past (kind of) failed migration to a better server, but with worse performance. We never tested a real load on this new server, we never had a chance, the server was not ready on time. Turns out the server testing BEFORE migrating to production should be MANDATORY. Sorry for the emphasis. So my mantra for all migrations/upgrades is: TEST, TEST, TEST,... freaking test as much as possible.
    – Marian
    Mar 23, 2013 at 23:37
  • 1
    Not sure whether to start with RAID 5 or SQLIOSIM... let's go with the later as the former speaks for itself. SQLIOSIM is a correctness and stress test tool, it is not a performance or load testing tool. It has absolutely no place in comparing the performance of config-X vs config-Y for workload-Z. All that would tell you is how well config-X performs against config-Y in a SQLIOSIM run. If you want to compare the performance for workload-Z, test workload-Z.
    – Mark Storey-Smith
    Mar 24, 2013 at 0:57
  • 1
    @GreenstoneWalker "RAID1 is faster for writing than RAID1+0" is nonsense. Where did this idea come from?
    – Mark Storey-Smith
    Mar 25, 2013 at 1:46
-1

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

Improve this answer
4
  • 4
    Tempdb is used for many things, but don't treat it as a throwaway database and place it on a RAID 0. Keep in mind if that RAID 0 volume fails, SQL Server will not be able to start up.
    – Patrick Keisler
    Mar 20, 2013 at 19:50
  • I have indeed created one temp db per core as suggested, but thanks for raising that point as well :)
    – DanP
    Mar 20, 2013 at 20:53
  • 1
    Well these suggestions are not very good and are not appropriate for every environment. First one is mentioned by @PatrickKeisler earlier. The second is a myth that Paul Randal debunked some time ago. The article is this one: A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core. So MS documentation can be wrong, don't take it by heart.
    – Marian
    Mar 23, 2013 at 22:28
  • 2
    "since TempDB only buffers data, you can't experience data loss"... and I'm sure the users of the system will appreciate that during the X hours of downtime they suffer while a) ops dig around in the basement for a replacement disk or b) a DBA tries to explain to the helpdesk how to move tempdb, from his mobile phone.
    – Mark Storey-Smith
    Mar 24, 2013 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.