Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ed6612
Starting Member
5 Posts |
 Posted - 2007-07-26 : 18:08:17
|
| Hi,I have the following scenario and was wondering what would be the optimal way to distribute database/log files for better performance.The server contains 8 SCSI drives configured as follows:C: - RAID1 - 2 drives (own HW RAID controller)--> Winsows Server 2003 OS + pagefileD: - RAID10 - 6 drives (own HW RAID controller)--> SQL 2000 + system DBs (master, model, msdb, tempdb) + 12gb DB w/ SIMPLE model + 1.5gb DB w/ FULL modelReconfiguring the storage is not an option, the best I can do is move database or log files between C: and D:I was considering moving LOG files and TempDB to C:. That would put them on a separate spindle but since D: at RAID10 is a better performer not sure if it is a good move.Any advise is greatly appreciatedThank you,-Ed |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-26 : 18:12:57
|
| Database files should never be put on the system partition for production machines.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
marbos
Starting Member
7 Posts |
Posted - 2007-08-16 : 16:40:25
|
| Ok, here's my two cents....Since you have a RAID-10 set with 6 drives, you won't get the full benefit of multi-file I/O distribution in a RAID configuration until you have at least 3 files in a filegroup. If you don't have them set already, implement multi-file filegroups for your user databases with at least three per database. The next thing to consider is how many physical processors you have in your machine. If you have, for instance, 4 cpu's, create multiple files for TempDB (1 for each CPU as recommended by Microsoft) and this will be about the best that you can do given your DASD configuration.Hope this helps. |
|
|
|
ed6612
Starting Member
5 Posts |
Posted - 2007-08-22 : 14:08:53
|
| Thank you for your post Marbos, I am, however, uncertain how splitting the database into multiple filegroups will improve performance in this case since a RAID 10 constitutes a single spindle and the RAID hardware automatically stripes the data among the physical disks regardless of how the files are partitioned. The following MS article seems to support this view:[url]http://msdn2.microsoft.com/en-us/library/aa178410(SQL.80).aspx[/url]Regarding TempDB, the server has 2 hyperthreaded Xeon processors, do you happen to have a reference to the MS article making the recommendation for splitting the TempDB?Thanks-Ed |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-22 : 22:24:40
|
| Create two files in the fil group may get performance gain even in user dbs, since os can generate two threads to access those files in parallel. |
|
|
|
|
|
|
|
|
Database placement