SQL Server 2005 / 2008 - multiple files / filegroups - how many? Why?

Solution 1:

The basic rule of thumb is to separate files onto different volumes to avoid contention, however the anmount of performance gain you get varies wildly by I/O subsystem and workload. For instance, multiple files on a single physical spindle is going to suck as far as performance goes, but the same arrangement with the volume being on a SAN LUN with several hundred drives from RAID 10 arrays may be just fine. Disk queue length counters are your friend as the simplest way to tell if you've got an I/O bottleneck.

You're looking at the I/O patterns on the databases - read-only, read-mostly, read-write, write-mostly, write-only - and basing things on that. You also need to choose the right RAID level and make sure your disk partition offsets, RAID stripe size, and NTFS allocation unit size are correctly set. Some people like to separate nonclustered indexes into a separate filegroup , but performance gains here vary just as I've explained above.

As well as performance, you should consider manageability and recovererability. Having a single, monolithic data file for a 100GB database means that your unit of restore is that file. Having it split into 4 25GB filegroups means you can use partial database availability and piecemeal restore to only have to restore a single filegroup in the event it gets damaged. By partitioning tables and indexes in multiple filegroups you can also limit which parts of the database are affected by maintenance operations (e.g. index fragmentation removal).

Tempdb is a whole special case, and I'll point you at a blog post of mine that explains all about why and how to split up tempdb - there are lots of misconceptions out there.

Without giving you a 'sweeping generalization' recommendation here, I'll point you at a bunch of whitepapers and blog posts for you to read:

• Whitepaper: Physical Database Storage Design
• Whitepaper: Predeployment I/O Best Practices
• Whitepaper: Partitioned Tables and Indexes in SQL Server 2005

• Whitepaper: Partial Database Availability

• Blog post: Misconceptions around TF 1118 (tempdb layout)

• Blog post: Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly? (with link to disk partition whitepaper)

Hope this helps you!

Solution 2:

The decision to split up a database up in different filegroups should be taken after having analyzed the current size and future growth of your tables. In my opinion unless you have a large database or tables with millions of rows, you should carefully consider pros and cons, since you may end up creating more performance problems than you fix.

There are some scenarios which could be interesting under certain premises:

• 2 filegroups: data and index
• 3 filegroups: read-only tables, read-write tables, index
• multiple filegroups: read-only, read-write, index, key table 1, key table 2, ...

You have to analyze your environment to decide if filegroups will help with your SQL Server growth, usage and performance needs.

Some key indicators to move to multiple filegroups (from this article):

• When disk queuing is causing application and user experience issues
  • If this is the case, consider leveraging additional disk drives with new filegroups housing IO intensive tables
• When particular tables are 10% or more of the database
  • If this is the case, consider moving these particularly large tables to separate filegroups on separate underlying disk drives
  • Depending on the table size in proportion to the remainder of the tables, consider building a filegroup for individual table(s)
• When non clustered index and data space are equal on large tables
  • If this is the case, consider splitting the data and clustered index from the non-clustered indexes
• When an almost equal percentage of read-only and read-write data exist in the database
  • If this is the case, consider splitting the read-only data in a separate filegroup as the read-write data
• When insufficient time is available to perform database maintenance
  • If this is the case, consider splitting the large tables into separate filegroups on different underlying disks and perform maintenance in parallel
• When the business or application will be changing significantly and the data is going to grow at a much higher rate
  • If this is the case, consider working with the users to understand the potential growth
• When archived data resides in the same database as the production data
  • If this is the case, consider separate file groups or one or more of the techniques in this tip - Archiving Data in SQL Server

If you find that filegroups could be improve your database's performance, write the code and test the process in a staging environment before you implement the changes on your production servers. Prepare some measurements before you implement the changes and compare them before/after. Since these processes can be very resource intensive and time consuming, perform these procedures during a maintenance period.

Don't forget, when creating new objects (tables and indexes), be sure that the objects are created in the correct filegroup to ensure expected performance and periodically validate the database objects are in the correct filegroups and correct as needed.