SQL Server 系統函數實用指南：檔案與檔案群組管理、記憶體配置、TempDB 優化

SQL Server 提供豐富的系統函數與 DBCC 指令管理儲存架構。本文整理三大實用場景：**記憶體選項動態調整**、**檔案/檔案群組生命週期管理 (建立、預設切換、移動、刪除)**、**TempDB 檔案屬性調整與多檔案配置**，附完整可執行 T-SQL 範例與參數說明。

一、 動態調整伺服器記憶體選項

無需重啟服務，即時生效 (需 `RECONFIGURE`)：

-- 設定最小記憶體 (MB)，預留給 SQL Server，防止 OS 回收
EXEC sp_configure 'min server memory (MB)', 0; -- 0 = 不預留 (不建議生產環境)
GO

-- 設定最大記憶體 (MB)，**必須設定**，預留 OS 及其他服務記憶體
-- 建議：實體記憶體 - 4GB (OS) - 其他服務
EXEC sp_configure 'max server memory (MB)', 256; -- 範例 256MB，生產環境依實際調整 (如 64GB 機器設 58000)
GO

-- 立即生效
RECONFIGURE WITH OVERRIDE;
GO

最佳實務：監控 `Available MBytes` 計數器 > 1GB，`Target Server Memory` 穩定於 `Max Server Memory`。

二、 檔案與檔案群組完整管理範例

場景：建立資料庫 `MyDB`，包含主檔案群組、兩個使用者定義檔案群組 (各兩個檔案)、記錄檔。切換預設群組、建立表指定群組、索引指定群組、縮減並移除檔案。

-- 1. 切換到 master
USE master;
GO

-- 2. 建立資料庫：多檔案群組、多檔案
CREATE DATABASE MyDB
ON PRIMARY
( NAME = 'MyDB_Primary', FILENAME = 'C:\MyDB_Prm.mdf' ),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1', FILENAME = 'C:\MyDB_FG1_1.ndf' ),
( NAME = 'MyDB_FG1_Dat2', FILENAME = 'D:\MyDB_FG1_2.ndf' ),
FILEGROUP MyDB_FG2
( NAME = 'MyDB_FG2_Dat', FILENAME = 'E:\MyDB_FG2.ndf' )
LOG ON
( NAME = 'MyDB_log', FILENAME = 'D:\MyDB.ldf' );
GO

-- 3. 修改預設檔案群組 (新建表預設放此群組)
ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

-- 4. 切換語境
USE MyDB;
GO

-- 5. 在預設群組 (FG1) 建表，LOB 資料 (image) 指定放 FG2
CREATE TABLE MyTable
( cola INT PRIMARY KEY, colb CHAR(8), colc IMAGE )
TEXTIMAGE_ON MyDB_FG2; -- 大型物件資料群組
GO

-- 6. 在 FG2 建立索引 (索引獨立於表群組)
CREATE INDEX IX_MyTable ON MyTable(cola) ON MyDB_FG2;
GO

-- 7. 移除檔案流程：先縮減 EMPTYFILE (資料遷移至同群組其他檔案)，再移除檔案
DBCC SHRINKFILE('MyDB_FG1_Dat1', EMPTYFILE);
GO
ALTER DATABASE MyDB REMOVE FILE MyDB_FG1_Dat1;
GO

關鍵點提醒

• TEXTIMAGE_ON：控制 `TEXT/NTEXT/IMAGE/XML/VARBINARY(MAX)/VARCHAR(MAX)` 等 LOB 資料儲存群組。
• ON FileGroup (索引/表)：**僅影響該物件**，不傳遞給關聯物件 (表壓縮不影響非叢集索引)。
• EMPTYFILE：將資料遷移至**同一檔案群組**的其他檔案，群組需至少保留一個檔案。
• 檔案移除後物理檔案不會自動刪除，需手動清理 OS 層面。

三、 TempDB 優化：檔案大小預設、移動位置、多檔案配置

TempDB 重建於每次啟動，**預設單一資料檔案極易成為 PFS/GAM/SGAM 爭用熱點**。

A. 設定主資料檔案初始大小 (避免自動增長延遲)

ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', SIZE = 100MB); -- 依工作負載預估，如 1GB
GO

B. 移動主資料檔案至專用高速磁碟 (需重啟生效)

ALTER DATABASE tempdb MODIFY FILE
( NAME = 'tempdev', FILENAME = 'D:\tempdb.mdf' ); -- D: 為專用 NVMe/RAID
GO

C. 新增次要資料檔案 (核心優化：CPU 核心數 ≤ 8 時檔案數 = 核心數；>8 時從 8 開始測試)

ALTER DATABASE tempdb ADD FILE
( NAME = 'tempdata_1', FILENAME = 'D:\tempdb_data_1.ndf', SIZE = 100MB, FILEGROWTH = 64MB );
GO
-- 依核心數繼續 ADD FILE tempdata_2, tempdata_3 ... 所有檔案大小、成長量保持一致

D. 記錄檔同樣預設大小、固定成長量 (避免 VLF 碎片)

ALTER DATABASE tempdb MODIFY FILE
( NAME = 'templog', SIZE = 50MB, FILEGROWTH = 64MB );
GO

四、 驗證腳本

-- 檢視檔案配置
USE tempdb;
EXEC sp_helpfile;
-- 或
SELECT name, physical_name, size*8/1024. AS SizeMB, growth*8/1024. AS GrowthMB
FROM sys.database_files;

五、 總結檢查清單

[ ] max server memory 已設定 (留 4GB+ 給 OS)
[ ] 使用者資料庫：多檔案群組隔離業務/LOB/索引
[ ] 預設檔案群組已切換至使用者群組 (非 PRIMARY)
[ ] TempDB：資料檔案數 = 邏輯核心數 (上限 8 起步)
[ ] TempDB：所有檔案預設大小一致、固定成長量 (非百分比)
[ ] TempDB：檔案放置於專用高速儲存 (非 C:、非資料檔磁碟)
[ ] 記錄檔 (LDF) 與資料檔 (MDF/NDF) 物理隔離