SQL Server 2005: Improving performance of Multiple Databases & LUNS
Cluster management, LUN, RAID 10, SQL Server 2005, SQL Server clustering, SQL Server performance, Storage in 2010
I am looking at ways to improve performance of our databases. We have 4 large databases (ranging between 50gb and 200gb each) on a single SQL Server 2005 Cluster. Currently they are split 2 on one LUN and 2 on another. I am looking at ways to improve the IO performance of the databases as they are quite IO intensive. I have been investigating options to create 6 LUN's and reduce the number of MDF files for each database from 8 to 6 and then spreading the MDF's across all 6 LUN volumes. Currently we have 16 disks for LUN1 in RAID 10 and 16 disks for LUN2 RAID 10. If I proceed with the changes I am investigating then we would have 6 LUNS with 6 disks each in RAID 10. 1 data file per a database will sit on each LUN. Should this improve performance? does anyone have experience in this area that they could share?
Software/Hardware used:
2 x Cluster Nodes, SAN, SQL Server 2005
Software/Hardware used:
2 x Cluster Nodes, SAN, SQL Server 2005
ASKED:
June 19, 2010 5:25 PM
UPDATED:
June 22, 2010 2:41 PM
Answer Wiki:
It depends. If one database needs more IO than the other then no, you'll just make things worse by giving that database less disks.
Usually you want to give the databases the most disks possible. Is your IO mostly reads or writes? If it's writes then stick with RAID 10, however if it is mostly reads (like 99% of databases are) then try switching to RAID 5 as you'll have more disks available for reading.
When using RAID 10 you only get access to the performance of 1/2 the disks. With RAID 5 you get access to all the disks (or all but one depending on the way the RAID 5 is done).
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks for your reply, all the databases are identical, they have different loads at different times and some are busier than others.
From what I’ve been reading, there are suggestions that splitting the *.mdf and *.ndf files across multiple volumes to “stripe” the data can help improve disk performance. As an example I was planning the following:
Let’s say I create 6 volumes with 6 disks each in RAID 10 called I:, J:, K:, L:, M: and N: and I have 4 databases: DB1, DB2, DB3, DB4.
DB1_1.mdf would sit on volume I:, DB1_2.ndf on volume J:, DB1_3.ndf on volume K:, DB1_4.ndf on volume L:, DB1_5.ndf on volume M:, DB1_6.ndf on volume N:.
DB2_1.mdf would sit on volume K:, DB2_2.ndf on volume L:, DB2_3.ndf on volume M:, DB2_4.ndf on volume M:, DB2_5.ndf on volume N:, DB2_6.ndf on volume I:.
DB3_1.mdf would sit on volume L:, DB3_2.ndf on volume M:, DB3_3.ndf on volume N:, DB3_4.ndf on volume I:, DB3_5.ndf on volume J:, DB3_6.ndf on volume K:.
DB4_1.mdf would sit on volume M:, DB4_2.ndf on volume N:, DB4_3.ndf on volume I:, DB4_4.ndf on volume J:, DB4_5.ndf on volume K:, DB4_6.ndf on volume L:.
Would the above configuration changes help to spread the IO load and give us an improvement in IO performance in comparison to the current configuration that we have below: (Both I: and J: in the current configuraiton use 16 disks each in RAID 10).
DB1_1.mdf, DB1_2.ndf, DB1_3.ndf, DB1_4.ndf, DB1_5.ndf, DB1_6.ndf all on volume I:
DB2_1.mdf, DB2_2.ndf, DB2_3.ndf, DB2_4.ndf, DB2_5.ndf, DB2_6.ndf all on volume I:
DB3_1.mdf, DB3_2.ndf, DB3_3.ndf, DB3_4.ndf, DB3_5.ndf, DB3_6.ndf all on volume J:
DB4_1.mdf, DB4_2.ndf, DB4_3.ndf, DB4_4.ndf, DB4_5.ndf, DB4_6.ndf all on volume J:
As for the RAID, I always believed that RAID 5 would save us space and give us the slightly worse read performance and a lot worse write performance than that of RAID 10 due to the parity overheads? and from everything I have read this appears to be the case.
Also, is it best to store the log files on seperate volumes for each of the databases? or would I see little benefit from that? currently all log files from all the databases are stored on a single RAID 10 volume with 10 disks (tempdb is also stored on its own volume).
Many Thanks