Data warehouse SQL config

Question

Data warehouse SQL config

Chris-121516

SSC Enthusiast

Points: 152
More actions
November 24, 2008 at 7:36 am

#196805

Hi all,
I am implementing a data warehouse using SQL 2005/2008. I am torn over how to best implement the files for the warehouse and tempdb in order to leverage the best performance. The hardware (still TBC) is likely to be a quad proc quad core system 12 Gb Ram with SAN storage (most likely as a cluster) The SAN is shared and managed by a provider and they have stated they can give me 2 LUNs (sized to my liking). Each LUN is RAID 5 over 4 physical disks:
LUN1 will be on a fibre channel
LUN2 will be a SATA channel
This is non negotiable.
Aside from than the usual recommendation of 1 file per proc for tempdb and .25 files per proc for user files (1 file per 4 procs) I need assistance in where to locate the files. My current thoughts re database files are to:
1) Use LUN2 for user files inc backups and split LUN1 in 2 use half for the warehouse and the half for tempdb but as they would be the same physical disks I dont think we will leverage best perf.
2) hold backups and user files locally and use LUN1 for tempdb LUN2 for warehouse database files
3) hold TEMPDb locally LUN1 for warehouse db files LUN2 for user files and backups
cheers all :crazy:

Viewing 3 posts - 1 through 2 (of 2 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

First, for DR, you want to separate the data from the backups.

Doesn't make sense to separate either LUN into mutliple partitions as they're going to the same physical disks, you're not gaining anything and limiting flexibility if one partition fills up. Keep them the same.

Is tempdb or logging taking more work from the warehouse. I'd guess tempdb, but if this is mostly read only, I'd probably put data on one LUN and logs/tempdb on the 2nd.

Backups local or on the log LUN.

Chris-121516 SSC Enthusiast Points: 152 More actions · Answer 2

Thanks - you are quote right about the DR, and have confirmed my own thoughts re: splitting the LUN.

As for the warehouse I imagine tempdb will see more action than the logs.

I like your ideas re: storage locations however my only other questions here are:

1) would you hold the tempdb on the faster fibre channel LUN or the SATA channel LUN?

2) am i likely to see a huge difference in perf for tempdb on RAID5 rather than if it resided on a single internal disk or would you expect it to be about the same?