February 26, 2004 at 5:04 am
We are currently in the process of designing a hardware replacement for a highly transacational SQL Server that contains 5 main databases. I am in the process of Spec-ing up the h/w, particularly the Disk configuration. I aim to run the data files on a raid 5 or raid 10 array - dependant on costs - and the logs either together on a raid 1 array, or each log on its own RAID 1 array, again depending on costs. Around 70% of all the transactions on the server use TEMPdb, so Im looking for the best location for this. Do I split the Data and Log files of Tempdb onto their own arrays, or use one array for them both, and if so what level of RAID would be advised..? Any advice would be helpful. Just to confirm my views before ordering if nothing else.
February 26, 2004 at 10:17 am
If you can split tempdb data and log that's ok, but I don't think it will be a huge impact since this is in simple recovery and only large sorts or temp tables will hit the log hard.
I'd put them on their own RAID 10 array if you could or 5 if you can't. If you make heavy use of this db, I'd separate it before I separated out the logs from the other 5 dbs.
Ideal:
dev0 - OS, SQL OS, pagefile
dev1 - SQL tempdb log
dev2 - SQL tempdb data
dev3 - SQL db1 data
dev4 - SQL db1 log
dev5 - SQL db2 data
dev6 - SQL db2 log
dev7 - SQL db3 data
dev8 - SQL db3 log
dev9 - SQL db4 data
dev10 - SQL db4 log
dev11 - sqldb5 data
dev12 - sqldb5 log
dev 13 - sql backups
If you can't afford that, here's the base I'd at least get.
dev0 - OS, SQL OD=S, Pagefile
dev1 - SQL tempdb (data + log)
dev1 - SQL logs (all dbs)
dev2 - SQL Data (all dbs)
dev3 - SQL Backups
February 27, 2004 at 4:51 am
Thanks Steve - you confirm pretty much as I thought about TEMPDB. As for the RAID levels I guess it's now up to me to convince the purse strings to be loosened and RAID 10 implemented! Thanks again.
February 27, 2004 at 6:50 am
I'm pretty inline with what Steve says. Few things you need consider also includes: How many controllers you plan to have? Are you plan with SCSI vs Fiber ? What's the rpm on the disks ? Amount of cache on the controller ?
My experience is H/W group sometime treat DB server as regular file server, which is completely wrong thing to do.
February 27, 2004 at 10:54 am
Agreed. The only additional performance enhancement would be to create a separate array (or arrays, if you go the route of having separate arrays for each database) for indexes. You can also gain some benefit if your application(s) make heavy use of tempdb: split tempdb into 10 separate files (most likely on the same array--although I suppose if money was falling from the sky, each on a separate array); for example, if tempdb needs to be 10GB, create 10 1GB files instead of one 10GB file.
February 27, 2004 at 12:00 pm
One catch with Eric's recommendation. 10 1GB is great, but you may need enable flag -T1118 and disable auto grow on each file. If you don't, SQL server always use the largest data file. Unfotunately, there is a bug with T1118, you can't restore DB when this flag is set.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply