SQL Server 檔案與檔案組管理實戰：記憶體配置、多檔案組架構與 tempdb 優化

一、伺服器記憶體配置基線

SQL Server 為記憶體密集型服務，正確設定 min/max server memory 避免與作業系統/其他服務爭搶記憶體：

-- 範例：保留 4GB 給 OS，最大使用 256GB (視實體記憶體調整)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'min server memory (MB)', 4096;   -- 下限，防止被 OS 回收過多
EXEC sp_configure 'max server memory (MB)', 262144; -- 上限，留給 OS 及非 SQL 工作負載
RECONFIGURE WITH OVERRIDE;

💡 原則：專用資料庫伺服器保留 10-15% 或 4-8GB 給 OS；啟用「鎖定記憶體頁面」 (Lock Pages in Memory) 權限給 SQL 服務帳號，防止分頁至磁碟。

二、建立多檔案組資料庫架構 (效能與管理性並重)

將資料依業務/存取模式分散至不同檔案組，配置於不同磁碟/磁碟陣列，實現 I/O 平行化與分層儲存：

完整建立腳本

USE master;
GO

CREATE DATABASE MyDB
ON PRIMARY                                    -- 主檔案組 (系統表、元資料)
( NAME = 'MyDB_Primary', FILENAME = 'C:\Data\MyDB_Prm.mdf', SIZE = 512MB, FILEGROWTH = 256MB ),
FILEGROUP MyDB_FG1                            -- 使用者資料檔案組 1 (熱點交易表)
( NAME = 'MyDB_FG1_Dat1', FILENAME = 'C:\Data\MyDB_FG1_1.ndf', SIZE = 1GB, FILEGROWTH = 512MB ),
( NAME = 'MyDB_FG1_Dat2', FILENAME = 'D:\Data\MyDB_FG1_2.ndf', SIZE = 1GB, FILEGROWTH = 512MB ), -- 不同磁碟
FILEGROUP MyDB_FG2                            -- 使用者資料檔案組 2 (大物件/歷史/封存)
( NAME = 'MyDB_FG2_Dat', FILENAME = 'E:\Data\MyDB_FG2.ndf', SIZE = 2GB, FILEGROWTH = 1GB )
LOG ON                                        -- 交易日誌
( NAME = 'MyDB_Log', FILENAME = 'L:\Logs\MyDB.ldf', SIZE = 1GB, FILEGROWTH = 512MB );
GO

-- 設定預設檔案組為 FG1 (新建表預設落此處)
ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

USE MyDB;
GO

-- 在預設檔案組建立表，LOB 資料 (image/varchar(max)/varbinary(max)) 導向 FG2
CREATE TABLE MyTable
( ColA INT PRIMARY KEY,
  ColB CHAR(8),
  ColC IMAGE
) TEXTIMAGE_ON MyDB_FG2;  -- 關鍵：LOB 資料分離存放

-- 在 FG2 建立索引 (索引亦可獨立檔案組)
CREATE INDEX IX_MyTable_ColA ON MyTable(ColA) ON MyDB_FG2;
GO

檔案組設計模式

檔案生命週期管理：移除檔案

-- 1. 將檔案資料遷移至同檔案組其他檔案 (EMPTYFILE)
DBCC SHRINKFILE (MyDB_FG1_Dat1, EMPTYFILE);
GO
-- 2. 移除檔案
ALTER DATABASE MyDB REMOVE FILE MyDB_FG1_Dat1;
GO

三、tempdb 專項優化：減少爭用、提升並行

tempdb 為所有暫存物件、版本存放區、排序/雜湊溢寫的共享資源，預設單一資料檔案易成瓶頸。

最佳實務配置

1. 資料檔案數量 = 邏輯 CPU 核心數 (≤ 8)，超過 8 核從 8 開始，視 PFS/GAM/SGAM 爭用調整。
2. 所有檔案大小、成長量一致，啟用比例填滿演算法均勻分布頁面。
3. 預先配置大小 避免啟動時自動成長延遲。
4. 放置於專用高速儲存 (NVMe/獨立 RAID)，分離使用者資料庫 I/O。
5. 啟用追蹤旗標 1117/1118 (SQL 2016+ 預設開啟)：同步成長、統一區塊配置。

調整腳本範例 (8 核心、專用 D: 槽)

-- A. 擴充主檔案至 10GB (預先配置)
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 10240MB);
GO

-- B. 移動主檔案至 D: (需重啟服務生效)
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = 'D:\TempDB\tempdb.mdf');
GO

-- C. 新增 7 個次要檔案 (共 8 個)
ALTER DATABASE tempdb ADD FILE (NAME = 'tempdb_1', FILENAME = 'D:\TempDB\tempdb_1.ndf', SIZE = 10240MB);
ALTER DATABASE tempdb ADD FILE (NAME = 'tempdb_2', FILENAME = 'D:\TempDB\tempdb_2.ndf', SIZE = 10240MB);
ALTER DATABASE tempdb ADD FILE (NAME = 'tempdb_3', FILENAME = 'D:\TempDB\tempdb_3.ndf', SIZE = 10240MB);
ALTER DATABASE tempdb ADD FILE (NAME = 'tempdb_4', FILENAME = 'D:\TempDB\tempdb_4.ndf', SIZE = 10240MB);
ALTER DATABASE tempdb ADD FILE (NAME = 'tempdb_5', FILENAME = 'D:\TempDB\tempdb_5.ndf', SIZE = 10240MB);
ALTER DATABASE tempdb ADD FILE (NAME = 'tempdb_6', FILENAME = 'D:\TempDB\tempdb_6.ndf', SIZE = 10240MB);
ALTER DATABASE tempdb ADD FILE (NAME = 'tempdb_7', FILENAME = 'D:\TempDB\tempdb_7.ndf', SIZE = 10240MB);
GO

-- 重啟 SQL Server 服務後生效移動與新檔案

四、驗證與監控指標

結語

檔案與檔案組是 SQL Server 存儲層的核心抽象。透過「記憶體基線配置 → 多檔案組分層架構 → tempdb 專項優化」三步走，可將 I/O 瓶頸前移至規劃階段，實現可預測、可擴展、可維護的高效能儲存基礎設施。定期檢視空間使用、等待統計、VLF 健康度，配合自動化擴容/遷移腳本，讓儲存層始終匹配業務增長節奏。