Practical Use of Filegroups, Multi-data files, and other IO Tweaks

  • I'm curious to hear from folks that have decided to deploy (or I guess also decided not to deploy) various IO tweaks in their environment on a properly sized, multi-use (many databases, mixed load OLTP) SQL server.

    Here's why. For lots of reasons I won't go into...our DB servers are not in our virtual racks...nor are they on our SAN. They are still dedicated chassis with cherry picked IO systems. They sport a single RAID for all things data (meaning, not: OS, program files, swap files, etc. Just data) That RAID is usually around 30 x 15K RPM SAS drives with max battery backed RAID cache (1.5GB currently) and I jump around between RAID 5 and 6...with additional hot spares...point here is I can usually lose up to 3 drives before I'm in trouble).

    So, my IO approach has always been to over-provision. Buy more space than needed, by even more IO throughput than needed, buy a big cache card, and let the RAID & IO subsystem work out the details. One major reason for this is my DB servers are usually sporting 100+ databases with a mix of vendor apps, SharePoint, custom DB's APP's, all serving a combination of OLTP and big reporting loads.

    Breaking out log files per the typical definition of IO isolation seems senseless in that I would be separating out 100+ log files (thereby turning 100+ synchronous workloads into something inherently chaotic and therefore back to something non-sequential).

    mdf, ldf, and index isolation (by physical disks) always seems heavily laced with pro vs con. If I broke up my 30 drives into say, 3 sets of 10 drive arrays, trying to do my own IO isolation seems like it could just as often introduce as many problems as I solve (the thought being that I would occationally experience a bottleneck on the 10 drive array I would not have experienced on my original 30 drive array).

    So as to stop rambling I'll open the floor to other folks that may have a test environment and better opportunities for testing different IO isolation...

    Am wrong headed here? Are there folks out there with a similar environment that are breaking apart big arrays, engaging in custom IO islolation (using any SQL methods available) and finding it worth the trouble.

    Thanks.

Viewing 0 posts

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