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

Answer 1

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.