資料庫效能優化實戰指南：檔案群組、資料表分區與 T-SQL 模板管理

資料庫優化不僅是 DBA 的專利，開發人員同樣應掌握核心技巧。本文整理三大關鍵主題：檔案/檔案群組組織、大型資料表分區策略、T-SQL 模板標準化管理，助你建立高效、可維護的 SQL Server 環境。

第九步：合理組織資料庫檔案群組與檔案

SQL Server 三種檔案類型

• .mdf（主資料檔）：每個資料庫僅一個，儲存系統物件；若無次要檔案，使用者物件也置於此。
• .ndf（次要資料檔）：可選，專門儲存使用者建立的物件（資料表、索引等）。
• .ldf（交易記錄檔）：1~N 個，儲存交易記錄，不屬於任何檔案群組。

檔案群組運作機制

新建資料庫時會自動建立 PRIMARY 檔案群組並設為預設。所有使用者物件預設寫入 PRIMARY（即 .mdf）。若要將使用者物件導向 .ndf，需：

1. 建立新檔案群組並設為預設：ALTER DATABASE ... ADD FILEGROUP ...; ALTER DATABASE ... MODIFY FILEGROUP ... DEFAULT
2. 建立 .ndf 檔案歸屬該群組：ALTER DATABASE ... ADD FILE ... TO FILEGROUP ...

大型資料庫（>1000MB）檔案群組最佳實務

1. PRIMARY 專存系統物件，勿放使用者物件，並取消預設群組設定。
2. 多磁碟分散 I/O：將不同檔案群組的檔案置於不同實體磁碟，實現平行讀寫。
3. 熱門資料表與索引獨立群組，加速存取。
4. 含 LOB（text/image/varchar(max)）欄位的熱門表獨立群組，最佳做法是將 LOB 資料放入專屬磁碟。
5. 交易記錄檔 (.ldf) 必須獨佔磁碟，記錄寫入屬寫入密集型作業，良好的 I/O 效能至關重要。
6. 唯讀/唯寫資料表分離，優化讀取與寫入路徑。
7. 審慎使用自動成長/收縮：自動成長成本高，建議設定固定增長量（如每週）；禁用自動收縮，改用排程手動收縮（如每月一次）。

第十步：在大型資料表套用分區

什麼是資料表分區？

將大型資料表拆分為多個較小的物理儲存單元（分區），查詢時只掃描相關分區，遵循「分而治之」原則。當其他優化手段用盡、百萬列以上大表查詢仍緩慢時，應考慮分區。

分區類型

本文著重水平分區實作。

分區最佳實務

1. 每個分區置於獨立檔案，並配置到不同磁碟，發揮平行 I/O 效能。
2. 歷史資料依「年齡」分區：如訂單表以 OrderDate 為分區鍵，每年一個分區。

四步驟實作水平分區（以 1999-2002 年訂單為例）

1. 新增檔案群組與 .ndf 檔案

ALTER DATABASE OrderDB ADD FILEGROUP [1999];
ALTER DATABASE OrderDB ADD FILE (
    NAME = N'1999', 
    FILENAME = N'C:\OrderDB\1999.ndf', 
    SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB
) TO FILEGROUP [1999];
-- 同理建立 2000, 2001, 2002 檔案群組

2. 建立分區函數

CREATE PARTITION FUNCTION FNOrderDateRange (datetime)
AS RANGE LEFT FOR VALUES ('19991231', '20001231', '20011231');

• RANGE LEFT：邊界值屬於左側分區（≤ 1999/12/31 進第一區）。
• 四個分區對應：≤1999、1999-2000、2000-2001、>2001。

3. 建立分區配置

CREATE PARTITION SCHEME OrderDatePScheme
AS PARTITION FNOrderDateRange
TO ([1999], [2000], [2001], [2002]);

4. 套用至資料表（重建叢集索引）

-- 移除既有叢集索引/主鍵
ALTER TABLE Orders DROP CONSTRAINT PK_Orders;

-- 在分區配置上重建叢集索引
CREATE UNIQUE CLUSTERED INDEX PK_Orders ON Orders(OrderDate)
ON OrderDatePScheme (OrderDate);

資料表即依 OrderDate 自動分散至四個檔案群組。

第十一步：使用 T-SQL 模板統一管理 DBMS 物件（加分項）

為何需要模板？

團隊開發時，缺乏一致的程式碼結構會導致後期除錯、效能調校如惡夢般困難。模板強制統一結構，並內建「審查」區塊利於 Code Review。

常用模板清單

• Template_StoredProcedure.txt：預存程序
• Template_View.txt：檢視表
• Template_Trigger.txt：觸發器
• Template_ScalarFunction.txt：純量函數
• Template_TableValuedFunction.txt：表值函數

建立模板步驟（SSMS）

1. 下載模板原始碼。
2. SSMS → 檢視 → 範本總管。
3. 右鍵對應節點（如「預存程序」）→ 新增 → 範本，命名。
4. 右鍵新模板 → 編輯，貼上下載內容並儲存。

使用模板建立物件

1. 範本總管雙擊模板 → 連線驗證。
2. 編輯器開啟，按 Ctrl+Shift+M 填入參數值（如 Schema、Procedure Name、Author 等）。
3. 選取目標資料庫 → 執行。

總結

優化是種「心態」：相信效能問題可解決，結合經驗與最佳實踐，預防勝於治療。開發部署階段多投入一小時實施規範，日後可省下數百小時排錯時間。聰明工作，而非單純辛苦工作！