Managing database files and filegroups appropriately will help with the availability of a database and of course its performance.
Files
There are at least two file types and you can have an optional third.
- Primary (mdf) – Stores the startup information for a database and pointers to other database files.
- Transaction (ldf) – Log information that is critical during database recovery
- Secondary (ndf) – Optional and stores user-defined data files.
Filegroups
Filegroups can be of two types, Primary and User Defined. It helps to group these data files separately. Default is a Primary filegroup and it contains all system objects. Unless other filegroups are created and specified, all user objects are also created under the primary filegroup.
Creating database files under different filegroups and spread across multiple disks is very important as that can help distribute the I/O. This in turn will boost the performance of your SQL Server because of parallel access.
1. Place larger tables that require more I/O in a separate filegroup and on a separate disk.
2. Make sure data and transaction log files are placed on different dedicated disks (I/O paths).
Note: All the reads and writes (I/O operation) happen at the page level. 'Page' is the basic unit of data storage in SQL Server. 