SQL Server Disk Configuration - Multiple Databases

  • Hi guys,

    I am right now in a project to completely change our SQL Server disk configuration because of very poor performance and lack of high availability. I'm a SQL Server database solutions developer with DBA knowledge, so sorry if I'm wrong with some hardware terms, our IT team should seek help but they are not the type to do so 😉

    Right now, we have one database per client, with regular transactions (let's say 70% read, 30% write) and 10+ clients, spread across 4 small SQL Servers under virtualization. Each database is between 10Gb and 80Gb, growing a lot and has lots of activity (because of poorly written applications, we are addressing this too). I found out IO was one of the problems, so I want to make sure it gets way better.

    So our IT team wants to regroup all these small servers in a big one, with lots of resources (easier to manager, costs less in licences, separate apps and SQL Server, room in datacenter etc.), with a duplicate server on standby. I suggested (or demanded) a disk array, separating Data files and Log files on different LUN or RAID, having lots of disks in a JBOD setup to increase throughput, my problem is, if I have 10, 15 or 20 Log files on one "drive", I'm afraid that during peak hours, we will get more random access and impact greatly the performance. What do you think? How should I plan right now the growth of our systems?

    Right now, merging all these client databases is really not an option, I was not really happy to see the problems of having so many clients, but at the same time, it's somewhat a good and lazy partitioning.

    Thanks

  • With reference to the log file part of your question, does your storage array have plenty of battery backed write cache, certified for use with SQL Server? If so, the need to separate log files onto different disks is less important, unless you have a very high transaction rate, in which case the built in throttling of the log manager may be more important to look at.

    Don't forget that even with a single log file on a disk, there will be times when the write-head has to move about - like during log backups and rollbacks of large transactions.

    I guess that as you are considering hosting multiple databases together that they are all OLTP? In which case using a large "shared pool" of disks for all the data files will probably be OK. And if Joe Chang is correct, you could even put tempdb with them. Now that's controversial!

    Other things to consider to keep I/O nice and quick

  • Partition alignment
  • Instant initialization
  • Exclude SQL files from anti-virus checking
  • Limit the number of virtual log files, but don't make them too big either[/url]
  • Enable page or row compression (Enterprise edition only)
  • And loads more. I put together as many things as I could think of into an article a few weeks ago, but there are so many variables I bet I missed a few! Here's the link.

  • Thanks a lot for your input, I'm sure these links will guide us to the right path and reassure me.

    I knew it did not make sense to have one Raid1 array for each client, I was not sure if I could try to make a few and load-balance our clients' work depending on their business hours.

    Thanks again,

    David

  • Viewing 3 posts - 1 through 2 (of 2 total)

    You must be logged in to reply to this topic. Login to reply