Using Files and Filegroups
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, Microsoft® SQL Server™ 2000 writes an amount proportional to the free space in the file to each file within the filegroup, rather than writing all the data to the first file until full, and then writing to the next file. For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. This way both files become full at about the same time, and simple striping is achieved.
As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to accommodate more data (provided that the database is set to grow automatically). For example, a filegroup comprises three files, all set to automatically grow. When space in all files in the filegroup is exhausted, only the first file is expanded. When the first file is full, and no more data can be written to the filegroup, the second file is expanded. When the second file is full, and no more data can be written to the filegroup, the third file is expanded. If the third file becomes full, and no more data can be written to the filegroup, the first file is expanded again, and so on.
Using files and filegroups improves database performance by allowing a database to be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that comprises three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can simultaneously access the data in parallel, which speeds up database operations.
Additionally, files and filegroups allow data placement because a table can be created in a specific filegroup. This improves performance because all I/O for a specific table can be directed at a specific disk. For example, a heavily used table can be placed on one file in one filegroup, located on one disk, and the other, less heavily accessed tables in the database can be placed on the other files in another filegroup, located on a second disk.
Recommendations
These are some general recommendations for files and filegroups:
- Most databases will work well with a single data file and a single transaction log file.
- If you use multiple files, create a second filegroup for the additional file and make that filegroup the default filegroup. This way, the primary file will contain only system tables and objects.
- To maximize performance, create files or filegroups on as many different available local physical disks as possible, and place objects that compete heavily for space in different filegroups.
- Use filegroups to allow placement of objects on specific physical disks.
- Place different tables used in the same join queries in different filegroups. This will improve performance, due to parallel disk I/O searching for joined data.
- Place heavily accessed tables and the nonclustered indexes belonging to those tables on different filegroups. This will improve performance, due to parallel I/O if the files are located on different physical disks.
- Do not place the transaction log file or files on the same physical disk with the other files and filegroups.