SQL Server 2005: Improving performance of Multiple Databases & LUNS

15 pts.
Cluster management
SQL Server 2005
SQL Server clustering
SQL Server performance
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Bulldog98
    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
    15 pointsBadges:
  • The Most-Watched IT Questions: July 6, 2010 - ITKE Community Blog
    [...] 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 [...]
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

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

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: