Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Practical Use of Filegroups, Multi-data files, and other IO Tweaks Expand / Collapse
Posted Friday, July 5, 2013 11:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 4, 2015 1:27 PM
Points: 326, Visits: 181
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.


Post #1470825
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse