real-world disk configurations

Question

real-world disk configurations

Kev Riley

SSCrazy Eights

Points: 9075
More actions
May 29, 2008 at 6:10 am

#186159

We’ve all seen the articles that talk about the BEST way to spec and configure hardware to get the best performance out of SQL Server, you know the ones, they talk about
Splittting binaries from data files
Splitting data files from log files from dump files
Splitting data from indexes
Splitting tempdb from user databases
Splitting system databases from user databases
Splitting read only user databases from read/write user databases
Splitting tempdb into multiple filegroups
RAID 5 vs RAID 1
etc, etc
All of which are all very well and good…. if you have a SAN with 50+ physical disks!!! But what about those real-world situations where you are lumped with a commodity 4U Dell server with a max capacity of 8 or even 6 disks? (nothing against Dell servers by the way, have used them extensively for databases over the last 9 years). Typically these servers use disks at about 150Gb– 300Gb in size, and 15krpm speeds – so we’re not talking huge datacentre-type stuff here, more line-of-business apps or small/medium ecommerce websites.
So here it is – an open question to the forum (what am I getting myself in to!!!) – What do you consider to be the most important rules to follow when deciding on how to setup your disks? What are the top 10 golden rules? Always split data from logs, or more important to split tempdb from user databases?
For a starter, here’s what I tend to do on 8 disk systems:
Create 4 RAID1 volumes (using h/w RAID NEVER s/w RAID)
1st disk partitioned C:\ (about 20-30GB) for OS & D:\ (rest of disk space) for a general file dumping area
2nd disk E:\ SQL data
3rd disk F:\ SQL logs
4th disk G:\ SQL dumps/backups
I know the real answer is ‘it depends’ – I’m just after some general recommendations. Think of it this way – if you only had 2 disks, how would you configure them?
If I get enough feedback, I’ll collate it all into an article later.
TIA,
Kev

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andy sql SSCrazy Eights Points: 9481 More actions · Answer 1

Thanks for kicking this off, a good topic for real-world situations.

My first comment is on your standard 8-disk setup - have you considered the capabilities of your hardware RAID card(s)? They vary massively in their abilities, from fundamental design (SAS, Serial Attached SCSI, versus traditional SCSI) to the quantity and speed of onboard memory.

If all of your drives are handled by a single RAID controller, then you may find that a RAID 5 spanned across multiple disks is a faster solution. Emphasis, of course, on "may"...

If I only had 2 disks, I would have to create a RAID 1 mirror; any other solution is risking data loss through failure of a single hard drive. Once I have a single mirror, the only configuration I can tweak is the layout and sizing of the partitions (which won't really affect performance in any way, as they are all sharing the same drives).

Same goes for 3 disks - RAID 5 configuration, giving you more space to play with, but very little opportunity to tweak performance.

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 2

With only 8 disks on a single RAID controller I'd do the following:

RAID1 OS/swap

RAID1 User Databases

RAID1 TempDB

RAID1 Logs

and backup across the network to a UNC target over a gigabit link. For most workloads I've encountered the biggest win is seperating TempDB I/O from the rest, so I'd do that first (as above). As we add more disks my next choices (in order) would be:

1: change User Databases to RAID10 and add more spindles

2: change TempDB to RAID10 and add more spindles

Only if even more disks are available would I start thinking about index/data seperation or increasing the Logs spindle count. Special cases for those would be workloads with an extemely "hot" central index or indexes, or workloads with massive amounts of writes respectively.

Regards,

Jacob

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 3

Thanks for the replies so far - this is exactly the kind of info I am looking to get.

Kev

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 4

8 disks is a lot, and I'd lean towards the 4 R1 volumes, but how busy is the database? If it's not stressed that much you might never see a benefit from logs being separate from data.

I might be tempted to do R1 or even R5 for the OS/pagefile, binaries, and maybe backup files. Don't forget backups. Shouldn't be on the same array as the data, kind of defeats the purposes.

Perhaps we should look at this another way. You have these items:

- OS and binaries

- Pagefile

- Backup files

- Data files

- Log files

- tempdb data

I've not worried a lot about tempdb log. Could be with tempdb or logs.

So if I have 1 array, there's no choice.

If I have 2 arrays, I'd move data files first. Everything else on first array.

If I get a third array, I'd look at how busy things are. It might make more sense to move tempdb than logs.

If I get the fourth array, typically I've move the other (tempdb or logs) that I didn't move with 3 arrays.

If I'm under some memory pressure, and I have a cheap boss, it might make sense to move the pagefile right before or after data files.

Andy sql SSCrazy Eights Points: 9481 More actions · Answer 5

Nice succinct answer, thanks Steve.

Just one additional question - at what point woud you consider splitting the data into secondary data files (and filegroups), and moving these filegroups onto separate partitions?

Andy