SQL Server 辅助文件与用户定义文件组创建顺序最佳实践咨询

核心操作顺序 #

必须先创建用户定义辅助文件组，再向该文件组中添加辅助文件。
创建辅助数据文件（.ndf）时必须指定其归属的已存在文件组，如果未提前创建对应文件组，添加文件的语句会直接抛出归属对象不存在的错误，反向操作完全不可行。

标准操作语法 #

1. 先创建自定义辅助文件组 #

ALTER DATABASE [目标数据库名称]
ADD FILEGROUP [自定义辅助文件组名称];

2. 再向新建的文件组中添加辅助文件 #

ALTER DATABASE [目标数据库名称]
ADD FILE
(
    NAME = N'辅助文件逻辑名称',
    FILENAME = N'磁盘存储路径\自定义文件名.ndf',
    SIZE = 100MB, -- 初始大小根据业务需求调整
    MAXSIZE = 20GB, -- 文件最大上限，可设置为 UNLIMITED 但不推荐
    FILEGROWTH = 512MB -- 自动增长步长，避免设置过小导致频繁扩容产生碎片
)
TO FILEGROUP [刚才创建的辅助文件组名称];

生产环境最佳实践 #

• 同一文件组下建议配置多个大小一致的辅助文件，SQL Server的比例填充算法会将数据均匀写入多个同规格文件，可显著提升IO并发性能，避免单文件IO瓶颈
• 同文件组下的所有辅助文件需配置相同的初始大小、自动增长步长与上限，避免因文件大小不一致导致比例填充算法失效，出现单文件负载过高的问题
• 业务数据统一存储在自定义辅助文件组中，不要将业务表、索引等对象放到默认主文件组，避免主文件组被占满后数据库直接进入不可用状态
• 辅助文件、主文件组文件、事务日志文件建议分别存储在不同的物理磁盘上，规避IO资源争抢
• 用于归档冷数据的辅助文件组，完成数据写入后可设置为只读状态，减少锁开销与不必要的IO扫描，同时也能降低数据损坏风险

注意：不要将辅助文件的自动增长步长设置为百分比，会导致后期文件扩容大小不可控，也容易产生磁盘碎片。

内容的提问来源于stack exchange，提问作者Vincent Tian