In order to manage data files they need to be placed into “filegroups.” By placing files into filegroups, we can control how data is spread over multiple files and we can also direct specific tables to specific files or sets of files.
When SQL Server allocates extents to tables, it does so by the proportional amount of the free space in all files in a filegroup. For example, let’s say we had a filegroup that was made up of one file that had 50 MB free and another file that had 100 MB free. For every extent that was allocated from the first file, two extents would be allocated from the second file. Therefore the two data files would fill up at approximately the same time.
Filegroups also allow control over what files a table will be stored in. For example, say we had 4 hard disks with 1 secondary data file on each disk. In our database let’s say we have two large tables that we need to perform frequent join operations on. In order to get the best performance we can setup two file groups each containing two of the four data files. Next we can place each table in one of the filegroups to give us maximum performance. Note that we will see how to place tables and indexes in specific filegroups when we start creating tables in an upcoming article.
There are a few specific filegroups we need to know about before we start creating our database. First, the Primary data file is always created as part of the “Primary filegroup” and it can’t be removed from this group. The Primary filegroup contains all the system objects (system objects are objects that store information about the database) in the database. The system objects stored in the Primary filegroup can’t be removed from this group.
When you create a new object it is automatically placed into the “Default” filegroup unless you specify otherwise. The Default filegroup can be set to any group you wish, but by default, the Default filegroup is set to the Primary filegroup (got that?). For example, if you created a new group called “MainStore” and set it as the Default filegroup, any new tables you create would automatically be placed into the “MainStore” filegroup.
We will come back to the concept of filegroups when we start creating objects. Also, I will show you how to perform task such as moving objects between file groups then. So if you don’t have a total grasp on how filegroups fit in…don’t worry. In a later article we will also take a more in-depth look at using files and filegroups to optimize database performance.