SQL Server 2005 / 2008 - multiple filegroups?

Question

I'm a developer at heart - but every now and then, a customer doesn't have a decent DBA to deal with these issues, so I'm called in to decide....

What are your strategies / best practices when it comes to dealing with a reasonably sized SQL Server database (anything larger than Northwind or AdventureWorks) - do you use multiple filegroups? If so: how many? And why?

What are your criteria to decide when to move away from the "one filegroup for everything" approach:

• database size?
• database complexity?
• availability / reliability requirements?
• what else?

If you use multiple file groups, how many do you use? One for data, one for index, one for log? Several (how many) for data? What are your reasons for your choice - why do you use that exact number of filegroups :-)

Thanks for any hints, pointers, thoughts!

Cheers, Marc

Answer 1

I've worked on a good range of DBs, and the only time we've used filegroups was when a disk was running short on space, and we had to create a new file group on another spindle. I'm sure there are good performance reasons why that's not ideal, but that was the reality.

Answer 2

The Microsoft trained and best practice methodology is as follows:

• Log files are placed on a separate physical drive
• Data files are placed on a separate physical drive
• Multiple file groups: When a particular table is extremely big. Often the case in transactional database (Separate Physical Drive)
• Multiple file groups: When using ranges or when wanting to split lookup data into a read-only database file (Separate Physical Drive)

Keep in mind that an MDF technically works similarly to a hard drive partition when it comes to storing data. The MDF is a randomly read file, whereas the LDF is a sequentially read file. Therefore splitting them into separate drives causes a huge performance gain, unless running solid state drives, in which case the gain is still there.

Answer 3

In addition to the previous posters comments.

Depending on your transaction throughput requirements, it is now common practice on Enterprise class database platforms to create multiple data files for your transaction log, the rule of thumb is one per processor core.

With regard to Filegroups, the type of data you store will determine what architecture you decide to use. For example, if you have tables that are relatively static, you may wish to store these on lower grade disk storage and save your higher grade kit for your more frequently accessed tables.

Also it is important to separate out the storage devices used for your system database from your user databases. This way should the disk storage of your user databases fail it will not bring the whole instance of SQL Server down.

Filegroups are also often used to simplify your backup strategy. Filegroup backups permit you to backup specific Filegroups, for example what is the point in backing up read only data that never changes.

Any slightly important production database should be run as a SQL Cluster on a Windows Clustered configuration in my view. This is your starting point for availability.

I would be happy to offer you specific advice if you can provide further details of your specific platform requirements.

Answer 4

There's at least ONE good reason for having multiple (at least two) file groups in SQL Server 2008 : if you want to use the FILESTREAM feature, you have to have a dedicated and custom filegroup for your FILESTREAM data :-)

Marc

Answer 5

Generally you should just have one Primary Filegroup and one log file against that.

Sometimes when you have very static data, you can create a SECOND filegroup that contains this static data. You can then make the filegroup READONLY which improves your performance. After all, this is pretty static data. It's not worth it if you have a low number of readonly rows (eg. lookup table values). But for some stuff (eg. archived content that can still be read in) then this might be a great option.

I got the idea from this blog post.

HTH.

Answer 6

Maintaining multiple filegroups helps you reduce the I/O burden. It also allows you storage flexibility where you can back up a filegroup easily rather than a single file and separate them into an individual disk drive per file group.