When dealing with databases in the 2-4GB range (and beyond), the default single-filegroup approach starts showing limitations. Here's my practical approach based on real-world implementations:

-- Sample diagnostic query to assess current filegroup usage
SELECT 
    fg.name AS filegroup_name,
    df.name AS file_name,
    df.physical_name,
    df.size/128 AS size_mb,
    df.growth/128 AS growth_mb
FROM sys.filegroups fg
INNER JOIN sys.database_files df ON fg.data_space_id = df.data_space_id;

• I/O Contention: When monitoring shows disk queue lengths > 2 consistently
• Backup Requirements: Partial backups needed for VLFs or specific schema objects
• Performance Patterns: Frequent table scans on large historical data while OLTP operations occur

A common setup I've implemented for 4GB+ databases:

-- Create filegroup structure example
ALTER DATABASE SalesDB ADD FILEGROUP FG_Data;
ALTER DATABASE SalesDB ADD FILEGROUP FG_Indexes;
ALTER DATABASE SalesDB ADD FILEGROUP FG_Archive;

ALTER DATABASE SalesDB 
ADD FILE (NAME = 'SalesDB_Data1', FILENAME = 'D:\SQLData\SalesDB_Data1.ndf') 
TO FILEGROUP FG_Data;

ALTER DATABASE SalesDB 
ADD FILE (NAME = 'SalesDB_Index1', FILENAME = 'E:\SQLIndexes\SalesDB_Index1.ndf') 
TO FILEGROUP FG_Indexes;

For time-series data where archival is frequent:

-- Create partition function and scheme
CREATE PARTITION FUNCTION PF_OrderDates (datetime)
AS RANGE RIGHT FOR VALUES 
('2020-01-01', '2021-01-01', '2022-01-01');

CREATE PARTITION SCHEME PS_OrderDates
AS PARTITION PF_OrderDates
TO (FG_Current, FG_2020, FG_2021, FG_Archive);

-- Apply to table
CREATE TABLE Orders (
    OrderID int,
    OrderDate datetime,
    -- other columns
) ON PS_OrderDates(OrderDate);

With multiple filegroups comes additional maintenance overhead:

• Filegroup-specific BACKUP and RESTORE operations
• DBCC CHECKFILEGROUP commands per filegroup
• Separate growth settings for different filegroups

Key DMVs to monitor filegroup performance:

SELECT 
    DB_NAME(mf.database_id) AS database_name,
    mf.name AS logical_name,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_bytes_read,
    vfs.io_stall_read_ms,
    vfs.num_of_writes,
    vfs.num_of_bytes_written,
    vfs.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id 
AND vfs.file_id = mf.file_id
ORDER BY io_stall_read_ms + io_stall_write_ms DESC;

When dealing with SQL Server databases beyond trivial sizes (2-4GB+), the default single-filegroup approach often becomes suboptimal. The key decision points revolve around:

• I/O performance distribution
• Backup/recovery granularity
• Storage tier utilization
• Maintenance operations flexibility

Here's my practical threshold matrix:

CREATE TABLE #FilegroupDecisionMatrix (
    Criteria VARCHAR(50),
    Threshold VARCHAR(100),
    Recommendation VARCHAR(150)
);

INSERT INTO #FilegroupDecisionMatrix VALUES
('Database Size', '> 5GB', 'Consider multiple data filegroups'),
('Transaction Volume', '> 1000 tps', 'Separate logs to dedicated storage'),
('Table Count', '> 50 significant tables', 'Consider functional grouping'),
('Index Size', '> 30% of data size', 'Dedicated index filegroup'),
('Availability SLA', '< 4h RTO', 'Partial database availability strategy');

For a 4GB database with moderate complexity, I typically implement:

-- Core filegroup structure
ALTER DATABASE YourDB 
ADD FILEGROUP FG_Data;
ALTER DATABASE YourDB 
ADD FILEGROUP FG_Indexes;
ALTER DATABASE YourDB 
ADD FILEGROUP FG_LOB;

ALTER DATABASE YourDB 
ADD FILE (
    NAME = 'YourDB_Data1',
    FILENAME = 'E:\SQLData\YourDB_Data1.ndf',
    SIZE = 2GB,
    FILEGROWTH = 500MB
) TO FILEGROUP FG_Data;

-- Additional files for specific purposes
ALTER DATABASE YourDB 
ADD FILE (
    NAME = 'YourDB_Index1',
    FILENAME = 'F:\SQLIndexes\YourDB_Index1.ndf',
    SIZE = 1GB,
    FILEGROWTH = 250MB
) TO FILEGROUP FG_Indexes;

For larger implementations, combine filegroups with partitioning:

-- Create partition function
CREATE PARTITION FUNCTION PF_OrderDateRange (datetime)
AS RANGE RIGHT FOR VALUES (
    '2020-01-01', '2021-01-01', '2022-01-01'
);

-- Map to filegroups
CREATE PARTITION SCHEME PS_OrderDateRange
AS PARTITION PF_OrderDateRange
TO (FG_Data_2019, FG_Data_2020, FG_Data_2021, FG_Data_Current);

Key metrics to monitor after implementation:

SELECT 
    f.name AS filegroup,
    SUM(a.total_pages) / 128 AS size_mb,
    SUM(a.used_pages) / 128 AS used_mb,
    (SUM(a.total_pages) - SUM(a.used_pages)) / 128 AS free_mb
FROM sys.filegroups f
LEFT JOIN sys.allocation_units a ON f.data_space_id = a.data_space_id
GROUP BY f.name;

• Over-partitioning small tables (creates unnecessary complexity)
• Placing filegroups on same physical drive (defeats the purpose)
• Neglecting to rebalance when adding new filegroups
• Forgetting to adjust backup strategies accordingly