Recommend table and index storage including filegroups
In this we will learn about the recommended table and index storage including filegroups.
File and Filegroup Fill Strategy
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup. However, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is given from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
Rules for designing Files and Filegroups
The following rules pertain to files and filegroups:
- Firstly, a file or filegroup cannot be used by more than one database. For example, file sales.mdf and sales.ndf, which contain data and objects from the sales database, can’t be used by any other database.
- Secondly, a file can be a member of only one filegroup.
- Thirdly, transaction log files are never part of any filegroups.
Recommendations
Recommendations when working with files and filegroups:
- Firstly, most databases will work well with a single data file and a single transaction log file.
- Secondly, if you use multiple data files, create a second filegroup for the additional file and make that filegroup the default filegroup. In this way, the primary file will contain only system tables and objects.
- Thirdly, to maximize performance, create files or filegroups on different available disks as possible. Put objects that compete heavily for space in different filegroups.
- Fourthly, use filegroups to enable placement of objects on specific physical disks.
- Next, put different tables used in the same join queries in different filegroups. This step will improve performance, because of parallel disk I/O searching for joined data.
- After that, put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups. Using different filegroups will improve performance, because of parallel I/O if the files are located on different physical disks. However, don’t put the transaction log file(s) on the same physical disk that has the other files and filegroups.
- Lastly, if you need to extend a volume or partition on which database files reside using tools like Diskpart, you should back up all system and user databases and stop SQL Server services first. Also, once disk volumes extends successfully, you should consider running DBCC CHECKDB command to ensure the physical integrity of all databases residing on the volume.
Reference: Microsoft Documentation