SQL Server Filegroups

While filegroups provide an opportunity for fine-tuning performance by allowing you to move specific tables and indexes from one physical drive array to another, unless you have a lot of SQL Server experience and have a very large database, you may end up causing more performance problems than you fix.

As a general rule of thumb, don't try to manually assign tables and other objects to filegroups to reside on specific physical drives. Instead, let RAID 5, RAID 10, or virtual SAN devices do this for you automatically. In the long run, you will probably get better overall performance. [7.0, 2000, 2005] Updated 9-19-2005

*****

If you know for sure that a very large table in your database will always be accessed sequentially, consider putting this table in its own filegroup, and then locating it on a disk array by itself. Be sure you don't put more than one table in the filegroup and ensure that no other files exist on that disk array. By doing this, you can take advantage of the disk array's ability to operate sequentially to retrieve the data. Sequential access is always faster than random access.

The hard part is to identify if a table is accessed sequentially or not. Generally speaking, if the table is mostly read-only, and if it has a proper clustered index, and if the clustered index is used in the queries accessing the table, access can be considered to be sequential.

As you might guess, this a very arcane performance tip and one that you will rarely be able to take advantage of in the real world. [7.0, 2000, 2005] Updated 9-19-2005

*****

If your database is very large and very busy, multiple files can be used to increase performance. Here is one example of how you might use multiple files. Let's say you have a single table with 10 million rows that is heavily queried. If the table is in a single file, such as a single database file, then SQL Server would only use one thread to perform a read of the rows in the table. But if the table were divided into three physical files, then SQL Server would use three threads (one per physical file) to read the table, which potentially could be faster. In addition, if each file were on its own separate physical disk or disk array, the performance gain would even be greater.

Essentially, the more files that a large table is divided into, the greater the potential performance. Of course there is a point where the additional threads aren't of much use when you max out the server's I/O. But up until you do max out the I/O, additional threads (and files) should increase performance. [7.0, 2000, 2005] Updated 9-19-2005

*****

For VLDB, use filegroups to divide up the database into units that are relatively easy to backup and restore. It is virtually impossible to manage VLDB backups and restores in a timely manner without judicial use of filegroups. [7.0, 2000, 2005] Updated 10-02-2006

*****

For VLDB, tables and their related indexes should be separated onto separate files and physical disks for optimum performance running queries, but not separate filegroups. If they are in separate filegroups, then you cannot back up and restore them as a single unit. [7.0, 2000, 2005] Updated 10-02-2006

*****

If you use filegroups for your databases, and your application also uses either TEXT, NTEXT, or IMAGE datatypes, consider moving the tables that contain these data types into one or more files in their own filegroup, and then place this filegroup on its own disk array. TEXT, NTEXT, and IMAGE datatypes can create a large I/O burden on your server, and if you can move this data to its own array, you can help speed up access to it. Obviously, this will be a lot of work, but if your database is very large and your application is running slowly, you may want to consider this option. The more you can reduce disk contention, the faster your application can run. [7.0, 2000, 2005] Updated 10-02-2006