Nearly any SQL topic presented at times in a slightly eclectic manner.

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

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

Holy....That's ridiculous....sheesh, and I'd be happy with just one extra filegroup. A filegroup for every table? That makes no sense at all. Unless they wanted to retain the ability to backup a single table the way you could in SQL 6.5...

My heart goes out to you for having to deal with such an inane environment....

then you wouldn't see much if any quantifiable change in performance using filegroups as I've suggested. The keyword being *performance* - there are still other reasons for filegroups I haven't touched on yet :)

A large number of disk? For mean that would mean at a minimum 10 disks in a single RAID5 array. Yes, it could be 7 or 8 at a minimum, but the key is that they are all participating in the same disk array. Large SANs have hundreds of disks.

But with at least 10 disks spinning away (and hopefully more), the benefit of using filegroups *for performance* (they're important for other reasons) just won't be visible if you take those 10 disks and make different arrays. In *most* cases: there are always exceptions, aren't there.

What I hope people consider is that filegroups can be used to improve performance given the right environment. And that filegroups exist in the first place... :)

December 20, 2007 7:02 AM

New Comments to this post are disabled

About James Luetkehoelter

I am passionate about what I do - which is DBA, development, IT and IT business consulting. If you don't know me, haven't met me or have never heard me speak, I'm a little on the eccentric side. One attendee recently described me as being "over the top". Yup, that about says it - because I only speak on topics that I'm passionate about.