February 8, 2010 at 2:33 pm
Hi
I've recently moved company and have inherited a number of servers running SQL Server 2005 standard edition. Most of the servers have a similar setup (in terms of the disk subsystem); this being 5 disks on RAID5 which are seen by the OS as one logical disk. This one logical disk is usually partitioned, and on some servers the database files and log files reside on a different partition.
My question is; what would be the best configuration for such a setup in terms of performance, IE - is there any benefit in placing the data and log files on different partitions? Im thinking theres probably no performance benefit as it is still the same disk head - but just wanted to know what more experienced DBA's would suggest?
Thanks in advance
February 10, 2010 at 1:03 am
I'm pretty sure entire books have been written on this topic. Generally speaking, you want to place your data files on a different disk subsystem than your log files. There are many factors to consider:
* Are your databases read-only?
* Do you have a large number of transaction?
* RAID5 is OK for reads but write performance is not very good
* How many channels does your RAID controller have? Can you place one set of disks on one channel and another set on another channel?
You can use a hybrid setup where data files are on a RAID 5 and the transaction logs are on another partition setup as RAID 1 or 10 for better read/write performance.
I would encourage you to do some analysis of your current systems, see what kind of I/O traffic you are seeing and where it is (is it in the data file?, log files?, tempdb?) then proceed to optimize from there.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply