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?

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).

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your response...

Discuss This Question: 2 &nbspReplies

There was an error processing your information. Please try again later.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

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

[...] Bulldog98 asked a great question for last month’s Storage in 2010 about improving the performance of multiple databases & LUNs in SQL Server 2005. Back up Mr. Denny and see if you have anything to add to the [...]

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy