March 21, 2008 at 9:51 am
Hey all,
At my last job I was totally spoiled by having a crazy expensive SAN with everything properly configured. I never really had the privilege of configuring the disks for SQL Server. I've been reading quite a bit lately about the optimal disk layouts, and I seem to continually hear difference of opinions. Curious to as what you all deem the best setup for data files, tlogs, tempdb on either an OLTP or an OLAP system, as well as a hybrid and why.
Thanks all.
March 21, 2008 at 10:20 am
Go with Raid 10 or 1 for everything. Avoid R5 for performance reasons.
If you can separate out the OS from SQL data/logs, do that. If you can separate out logs from data, that helps as well. I'd drop backups on the OS drive, but be sure you have lots of space.
March 21, 2008 at 10:27 am
Isn't R5 better for reads, just horrible for writes? Which brings up the question, what is the best bet for a warehouse? DW's usually only see writes in the off hours and nothing but reads throughout the day.
March 21, 2008 at 11:59 am
Also, is it truly best to separate your non-clustered indexes onto a separate file group, on a different drive?
March 21, 2008 at 12:14 pm
My humble recomendation is ...
C: OS + SQL Binaries
D: Data Files - RAID 1 or 10 (like Steve Suggested)
E: Temp DB & Log Files - RAID 0
F: Index Files - RAID 1 or 10 (like Steve Suggested)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 21, 2008 at 4:10 pm
Also
If you have enough drives, seperate your TEMPDB to a drive by itself, preferably raid 0 (it's rebuilt every time you start SQL server).
Raid 5 can be used in a warehouse if you have a set tiime where data is loaded and the system is not in heavy use. but again, if you have the space, go raid 10.
I normally keep my SQL tables (Primary File Group) on the C drive, but alo create a USER file group with the data files spread across differnt drive letters. Again, it comes down to the number of drives available.
To suggest an optimal setting, we would need the number of drives and space available. Without that information you will just get generalizations
Hope this helps
Marvin Dillard
Senior Consultant
Claraview Inc
March 21, 2008 at 4:47 pm
R5 is worse for both under load.
March 21, 2008 at 6:44 pm
Steve
I definately agree with you. Raid 5 is a bad choice, If an organization wants to persue a data warehouse then they must know that the hardware must be sufficient to do so. The only place I have raid 5 is in the sandbox environment where speed does not matter and I'm working with very small datasets for proof of concept. In the dev/test/production environments it's definately raid 10 but I was in on the ground floor and had significant input to the hardware.
I don't know if I would want to work anywhere that the proper investment is not made in hardware as it would make me look bad, no matter how tuned the database is kept.
Hopefully this member has a chance to provide some input
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply