THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

James Luetkehoelter

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

Published Tuesday, December 18, 2007 8:15 AM by James Luetkehoelter

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

>>please everyone tell me you use other filegroups than PRIMARY...

Oh yes  :-(

At this job I worked at  there was one filegroup for every table and index including primary index (clustered)...(a lot of empty filegroups of course). The total filegroups was 500+

Can you imagine what you have to do when restoring a backup to another machine?

December 18, 2007 9:12 AM
 

Denis Gobo said:

I forgot the best part....:(

the person placed all the indexes on one drive and all the table filegroups on another...to improve performance

December 18, 2007 9:14 AM
 

James Luetkehoelter said:

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

December 18, 2007 9:16 AM
 

Linchi Shea said:

A filegroup for each table could be your granular recovery strategy, in some cases anyway.

December 18, 2007 10:50 AM
 

Denis Gobo said:

Linchi, I agree for big tables it is useful....but some of these tables were lookup tables with 10-20 rows  :-)

December 18, 2007 11:04 AM
 

Uri Dimant said:

Hi James

So , as I understood if you have SAN  it is unnecessary  to create a filegroup? On the other hand if you haven't , creating filegroup  improve perfomance.

Linchi , it would be great to see your comments/benchmark  on the subject :-))

December 18, 2007 11:55 PM
 

James Luetkehoelter said:

Uri:

Let me clarify:

If you have a SAN with:

*A large number disks

*A compitent and on-staff storage administrator for that SAN

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 :)

December 19, 2007 6:33 AM
 

Uri Dimant said:

Thanks James

When you say "A large number disks " how much do you mean?

December 19, 2007 9:33 AM
 

James Luetkehoelter said:

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

Leave a Comment

(required) 
(required) 
Submit

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.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement