SQL Server 2008: Is there any benefit to multiple filegroups on the same physical drive?

Question

This is most likely a ridiculous question, but I'm intrigued by the thought so I'll ask anyway. Is there any performance or benefit (outside of disaster recovery handling) of having a database on multiple filegroups stored on the same physical drive?

More specifically, if I create a secondary filegroup ONLY for full-text indexes on the same physical drive, is it beneficial? Could it be a bottleneck?

Log files in my situation are stored on a separate physical drive from data files.

Answer 1

It shouldn't provide any additional benefit, except that with separate file groups you could, potentially, split out your backups. As far as the I/O on the same drive, you won't gain much if anything by doing this, so if you're considering it strictly for an I/O performance reason, I would suggest holding off until you can budget separate spindles.

Answer 2

Multiple files have the benefit of reducing allocation contention (PFS latch contention). Really really really fast IO subsystems (eg. SSD drives) can expose this problem and require mitigation by adding more files to the database. There are more details on this at How many files should a database have? or on Benchmarking: Multiple data files on SSDs.

Multiple filegroups imply multiple files, but at the same time a hot table will not benefit from multiple filegroups because the hot spot will be, again, in a single filegroup (unless, of course, the hot filegroup is itself split into multiple files). So I would say that filegroups are to be used solely for administration purposes (eg. piece meal restore).