Filegroups Part I-a: Dividing for Performance (a partial rant)

James Luetkehoelter — Tue, 18 Dec 2007 13:15:00 GMT

In my previous post on filegroups, I tried to make the case that using filegroups (please everyone tell me you use other filegroups than PRIMARY...) can improve database performance. I received a number of comments challenging that notion, which personally I find wonderful - disagreement and real arguing ( no Monty Python "This isn't an argument, it's just contradiction" "No it isn't!" "Yes it is!" "No it isn't" ad infinitum) are how we arrive at real answers, or at least a clear understanding of the issue.

I suggested that having a separate group for your tables (and please tell me they are clustered indexes and not heaps....) and you non-clustered indexes can have an improvement on performance if they are placed on separate I/O paths. There are other techniques I didn't get into, like dividing tables that are frequently joined on separate filegroups, etc. Even without different I/O paths (and I'm not necessarily demanding a different channel, but a separate LUN with separate array), this can at times make a difference, in particular with SQL 2005's CPU to I/O affinity. Or separating something like transaction logs on a single mirrored drive since they have sequential access only (unless the worst happens).

Most of the responses I got suggested that it was better to just take all of your disks and make the largest array possible. I ABSOLUTELY 100% AGREE. *If* you have a large amount of disks. this also usually means a SAN is involved. I would do the same thing in that position - filegroups won't by me nearly as much as having a large number of fast disks on on LUN.

The readers of these blogs, and the majority of the SQL Server installs out there don't necessarily have the luxury of a SAN, or may be given only a small slice of its capabilities (or worse yet, have no true storage administrator that understands the system). Many make do with the disks provided with the server, or perhaps an external cabinet or Shared Attached Storage. I work with many of these clients, and I can tell you first hand using filegroups has made a visible difference.

Here's the rant portion of this post: I won't post any sort of benchmarking data. Why? Because a benchmark only applies to the system it was run on. Yes, it can demonstrate that there is a quantifiable difference between certain approaches, but it doesn't mean that same quantifiable difference will occur in a different environment. I'm not saying that there won't be a difference, and may there will be the same quantifiable difference. What I'm saying is that with the difference in platforms, quantifiable data doesn't *necessarily* equate to the same difference on a different platform.

Remember, many of the SQL Server professionals we are trying to help with these posts don't have SANS, or knowledgable storage admins, or, worst of all, a budget to purchase anything. So please, if you're in that group, consider filegroups for performance benefits. You might be surprised at what you see...

James Luetkehoelter : Performance

Filegroups Part I-a: Dividing for Performance (a partial rant)