SQL Server - Filegroups
SQL Server Filegroups: 
Filegroups provide a logical layer for organizing SQL Server data files and controlling where database objects are stored. When designed correctly, filegroups can help with:
- Storage organization
- Large database management
- File and filegroup-level backup strategies
- VLDB administration
- Read-only archival data
- Partitioning strategies
- Administrative flexibility
Understanding SQL Server Storage Architecture
Before discussing filegroups, let us understand where they fit within SQL Server storage architecture.
↓
Database
↓
Filegroups
↓
Data Files (.mdf / .ndf)
↓
Extents
↓
Pages
↓
Tables and Indexes
A very important point is that filegroups contain data files logically. The physical files themselves store the actual database pages.
SQL Server Database File Types
A SQL Server database can normally contain three important file categories.
| File Type | Typical Extension | Purpose |
|---|---|---|
| Primary Data File | .mdf | Primary database data file containing system information and user data depending on placement. |
| Secondary Data File | .ndf | Additional data files used to distribute or organize database storage. |
| Transaction Log File | .ldf | Stores transaction log records used for transactional consistency and recovery. |
What Is a Filegroup?
A filegroup is a logical collection of one or more SQL Server data files.
Instead of requiring the administrator to manage object placement directly against individual physical files, SQL Server allows objects to be associated with a filegroup.
The simplified relationship is:
↓
Filegroup
↓
One or More Data Files
↓
Database Pages
PRIMARY Filegroup
Every SQL Server database contains a PRIMARY filegroup.
The primary data file belongs to the PRIMARY filegroup. Unless another filegroup is explicitly configured as the default, new objects are normally created in PRIMARY.
A simple database could therefore look like:
│
├── PRIMARY Filegroup
│ └── MyDatabase.mdf
└── Transaction Log
└── MyDatabase_log.ldf
User-Defined Filegroups
DBAs can create additional filegroups to separate database objects logically.
For example:
FG_DATA
FG_INDEX
FG_ARCHIVE
These names are administrator-defined and should ideally describe the purpose of the storage.
Creating a Filegroup
Use ALTER DATABASE to add a new filegroup.
ALTER DATABASE [YourDatabase]
ADD FILEGROUP [FG_DATA];The filegroup now exists logically, but it still needs one or more physical data files before it can store data.
Adding a Data File to a Filegroup
ALTER DATABASE [YourDatabase]
ADD FILE
(
NAME = N'YourDatabase_Data01',
FILENAME = N'D:\SQLData\YourDatabase_Data01.ndf',
SIZE = 1024MB,
FILEGROWTH = 256MB
)
TO FILEGROUP [FG_DATA];This creates a secondary data file and assigns it to FG_DATA.
Multiple Files Inside a Filegroup
A filegroup can contain multiple data files.
│
├── Data01.ndf
├── Data02.ndf
├── Data03.ndf
└── Data04.ndf
SQL Server uses a proportional-fill allocation mechanism when allocating pages among files in a filegroup. Files with more available free space generally receive proportionally more allocations.
Creating a Table on a Specific Filegroup
Once a filegroup is available, a table can be explicitly placed on the filegroup.
CREATE TABLE dbo.CustomerOrders
(
OrderID BIGINT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME2 NOT NULL,
Amount DECIMAL(18,2) NOT NULL
)
ON [FG_DATA];The table is now associated with FG_DATA.
Placing an Index on a Filegroup
Indexes can also be created on specific filegroups.
CREATE NONCLUSTERED INDEX IX_CustomerOrders_OrderDate
ON dbo.CustomerOrders (OrderDate)
ON [FG_INDEX];This provides additional flexibility when designing storage for large or specialized databases.
A Critical Point About Clustered Indexes
A SQL Server clustered index contains the table's data at its leaf level. Therefore, moving or creating a clustered index on another filegroup can effectively move the table's data to that filegroup.
Conceptually:
↓
Create Clustered Index ON FG_DATA
↓
Table Data Stored with Clustered Index
↓
Data Moves to FG_DATA
This distinction is especially important when planning data movement between filegroups.
Default Filegroup
Every database has a default filegroup. Objects created without an explicit filegroup specification are created in the default filegroup.
You can make a user-defined filegroup the default:
ALTER DATABASE [YourDatabase]
MODIFY FILEGROUP [FG_DATA] DEFAULT;After this change, objects created without an explicit filegroup specification will normally be allocated to FG_DATA.
How to Check Filegroups
SELECT
name,
type_desc,
is_default,
is_read_only
FROM sys.filegroups
ORDER BY name;Check Files and Their Filegroups
The following query helps identify which physical files belong to which filegroups:
SELECT
fg.name AS FilegroupName,
df.name AS LogicalFileName,
df.physical_name AS PhysicalFileName,
df.size * 8.0 / 1024 AS SizeMB,
df.max_size,
df.growth,
df.is_percent_growth
FROM sys.database_files AS df
LEFT JOIN sys.filegroups AS fg
ON df.data_space_id = fg.data_space_id
ORDER BY fg.name, df.file_id;Find Which Filegroup Contains a Table
For a non-partitioned table, the data space associated with the heap or clustered index can be inspected to identify its placement.
SELECT
s.name AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.index_id,
ds.name AS DataSpaceName
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i
ON t.object_id = i.object_id
INNER JOIN sys.data_spaces AS ds
ON i.data_space_id = ds.data_space_id
WHERE i.index_id IN (0,1)
ORDER BY s.name, t.name;For partitioned objects, the data space may represent a partition scheme rather than a single filegroup, so partition metadata should also be reviewed.
Why Do We Need Filegroups?
A small database usually does not need a complex filegroup structure. For large databases, however, filegroups can provide significant administrative flexibility.
1. Logical Storage Organization
Different categories of information can be organized into different filegroups.
FG_HISTORY → Historical data
FG_ARCHIVE → Archived data
2. Large Database Administration
For very large databases, separating data logically can make capacity planning, backup design, archival operations and data lifecycle management easier.
3. Filegroup Backup and Restore
SQL Server supports file and filegroup backup operations. This can be particularly valuable for very large databases where the backup and recovery strategy is deliberately designed around filegroup architecture.
BACKUP DATABASE [YourDatabase]
FILEGROUP = 'FG_ARCHIVE'
TO DISK = 'D:\SQLBackup\YourDatabase_FG_ARCHIVE.bak'
WITH COMPRESSION, STATS = 10;4. Read-Only Historical Data
A filegroup containing historical information that no longer changes can potentially be configured as read-only.
ALTER DATABASE [YourDatabase]
MODIFY FILEGROUP [FG_ARCHIVE] READ_ONLY;To make the filegroup writable again:
ALTER DATABASE [YourDatabase]
MODIFY FILEGROUP [FG_ARCHIVE] READ_WRITE;Filegroups and Table Partitioning
Filegroups become particularly useful when combined with SQL Server partitioned tables.
For example, a large sales table may contain many years of data:
2024 → FG_2024
2025 → FG_2025
2026 → FG_2026
The logical architecture becomes:
↓
Partition Scheme
↓
Partition Function
↓
Different Partitions
↓
Filegroups
↓
Physical Data Files
Real-World Filegroup Design
Consider a large transactional database that contains current orders as well as several years of historical orders.
A possible architecture could look like:
→ System/default objects
FG_ACTIVE
→ Current transactional data
FG_HISTORY
→ Historical operational data
FG_ARCHIVE
→ Older archival data
The archive filegroup could eventually become read-only if the archived information no longer changes.
This can provide a cleaner architecture for managing a very large database, but it should be introduced because the database has a real lifecycle, backup, recovery, or capacity-management requirement, not simply because multiple filegroups look more sophisticated.
Moving Data Between Filegroups
Object movement between filegroups requires planning.
For tables with clustered indexes, rebuilding the clustered index onto another filegroup is one method of moving the table's data.
CREATE UNIQUE CLUSTERED INDEX CIX_CustomerOrders_OrderID
ON dbo.CustomerOrders (OrderID)
WITH (DROP_EXISTING = ON)
ON [FG_DATA];Removing a Data File from a Filegroup
A data file cannot simply be removed while allocated pages remain inside it. A common administrative method is to first empty the file:
DBCC SHRINKFILE
(
N'YourDatabase_Data04',
EMPTYFILE
);Once the file is empty and all requirements are satisfied, it can be removed:
ALTER DATABASE [YourDatabase]
REMOVE FILE [YourDatabase_Data04];Removing a Filegroup
Once a user-defined filegroup contains no files or objects that prevent removal and is no longer required, it can be removed.
ALTER DATABASE [YourDatabase]
REMOVE FILEGROUP [FG_DATA];Never perform this operation until the filegroup's dependencies and contents have been fully validated.
File Size and Autogrowth Strategy
Filegroup design and file sizing should be considered together.
Avoid designs similar to:
Autogrowth: 1 MB
Expected Database Size: 500 GB
Such a configuration can require an excessive number of growth operations.
A better design is to:
- Estimate expected database growth
- Pre-size data files appropriately
- Use controlled fixed-size autogrowth increments
- Monitor disk free space
- Review unexpected growth patterns
- Plan capacity before critical thresholds are reached
Common Filegroup Mistakes
1. Creating Multiple Filegroups Without a Requirement
Additional filegroups add administrative complexity. Use them where they solve an actual storage, backup, recovery, partitioning, lifecycle or manageability requirement.
2. Assuming More Files Automatically Improve Performance
More files do not automatically mean more performance. Underlying I/O architecture and workload behavior matter.
3. Separating Indexes Without Physical I/O Separation
Creating FG_DATA and FG_INDEX provides logical separation, but if every physical file uses the same underlying storage path, the expected physical I/O benefit may not exist.
4. Ignoring File Autogrowth Settings
Badly configured growth settings can create unnecessary growth events and operational surprises.
5. Allowing the PRIMARY Filegroup to Become Unnecessarily Large
For databases that genuinely need a multi-filegroup architecture, separating appropriate user objects from PRIMARY can provide cleaner storage organization and management.
6. Treating Filegroups as a Replacement for Backups
Filegroups are part of database storage architecture. They do not eliminate the need for a properly tested backup and recovery strategy.
Example Filegroup Design
For a sufficiently large database with clear data-lifecycle requirements, an architecture might look like:
| Filegroup | Purpose | Example Objects |
|---|---|---|
| PRIMARY | Primary/default storage | Database system information and selected objects |
| FG_ACTIVE | Active transactional data | Orders, transactions |
| FG_HISTORY | Historical data | Older operational records |
| FG_ARCHIVE | Archive storage | Long-term historical data |
Useful DBA Filegroup Queries
List Filegroups
SELECT
data_space_id,
name,
type_desc,
is_default,
is_read_only
FROM sys.filegroups
ORDER BY data_space_id;List Database Files
SELECT
file_id,
name,
type_desc,
physical_name,
size * 8.0 / 1024 AS SizeMB,
max_size,
growth,
is_percent_growth
FROM sys.database_files
ORDER BY file_id;Map Files to Filegroups
SELECT
fg.name AS FilegroupName,
df.name AS LogicalFileName,
df.physical_name,
df.size * 8.0 / 1024 AS SizeMB
FROM sys.database_files AS df
INNER JOIN sys.filegroups AS fg
ON df.data_space_id = fg.data_space_id
ORDER BY fg.name, df.file_id;When Should You Use Multiple Filegroups?
Consider multiple filegroups when you have a genuine requirement such as:
- Very large database management
- Partitioned data architecture
- Historical or archival data management
- Read-only data
- File/filegroup backup and recovery requirements
- Specific storage placement requirements
- Data lifecycle management
- Administrative isolation of large datasets
For a small application database, a complicated filegroup architecture may provide little practical benefit.
How Should a DBA Think About Filegroups?
↓
Data Size and Growth
↓
Data Lifecycle
↓
Backup and Restore Requirements
↓
Partitioning Requirements
↓
Storage Architecture
↓
Filegroup Design
↓
Data File Layout
↓
Monitoring and Capacity Management
SQL Server Filegroup Best Practices
- Keep filegroup architecture as simple as the workload permits.
- Create additional filegroups only when there is a clear operational benefit.
- Use meaningful filegroup and logical file names.
- Pre-size physical files based on expected growth.
- Use sensible fixed-size autogrowth increments.
- Monitor file and volume capacity proactively.
- Understand the underlying storage before claiming performance benefits from file separation.
- Plan transaction log capacity before moving large amounts of data.
- Test file/filegroup backup and restore procedures before depending on them.
- Document which business datasets belong to each filegroup.
The DBA Mindset
The wrong question is:
The better question is:
If there is no clear answer, additional filegroups may simply be unnecessary complexity.
Final Thoughts
SQL Server filegroups are much more than logical names attached to database files. For the right workload, they become an important part of database storage architecture.
A well-designed filegroup strategy can provide:
- Better logical data organization
- More flexible storage management
- Improved VLDB manageability
- Filegroup-level backup options
- Cleaner partitioning architecture
- Better historical and archival data management
However, filegroups should solve real problems. Creating many filegroups or data files without understanding the workload can increase administrative complexity without delivering meaningful benefits.
Comments
Post a Comment