Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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 '13 at 11:46

6 Answers 6

up vote 10 down vote accepted
+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.

share|improve this answer

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.

share|improve this answer
    
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 '13 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 '13 at 21:14

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.

share|improve this answer
    
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 '13 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 '13 at 23:03
    
I have added additional specs about the hardware of the machine. –  DanP Mar 23 '13 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 '13 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 '13 at 13:29

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.

share|improve this answer
    
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 '13 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 '13 at 23:05
1  
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 '13 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 '13 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 '13 at 1:46

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

share|improve this answer
3  
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 '13 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 '13 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 '13 at 22:28
1  
"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 '13 at 1:27

SATA RAID 1 (2 Drives) - OS / Programs

Your choice is correct for this. RAID 1 = mirroring or duplicating your data and there by getting good data redundancy in case of a single hard disk failure. Also, RAID 1 offers much faster perofrmance than RAID 5 as there is no overhead of caclulating Parity data. RAID 1 is an ideal choice (since you already have RAID 1 in place) as on a dedicated SQL Server, the OS swap file should be rarely used by SQL Server and SQL Server binaries are always read, but just reading SQL Server binaries does not cause any noticable overhead on the server.

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

You can refer to some excellent explanation by Paul Randal and SQLChicken here

share|improve this answer
1  
This is the rote "best practice" regurgitation that is so pervasive. You're linking an answer from Paul Randal where the question doesn't even mention the number of disks involved. –  Mark Storey-Smith Mar 24 '13 at 0:49
    
The reference that I have given, explains in clearly and to the point of what the OP is asking. HTH ! –  Kin Mar 24 '13 at 16:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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