SQL Server 資料庫效能設定檢查清單 (第 5 部分)：資料庫層級配置選項

前言

本系列第 5 部分聚焦於**資料庫層級配置選項**的效能影響。許多 DBA 僅關注執行個體層級設定，忽略資料庫屬性頁中的關鍵選項。本文詳細解析 9 項核心資料庫選項的預設值、效能影響、最佳實踐與檢查腳本，助你建立標準化的資料庫配置基準線。

一、資料庫選項 vs 組態選項

• 資料庫選項：ALTER DATABASE ... SET OPTION、sp_dboption (已棄用)、SSMS 屬性 → 選項頁。範圍：單一資料庫。
• 組態選項：sp_configure、SSMS 執行個體屬性。範圍：整個執行個體。

本文僅涵蓋資料庫選項。

二、核心選項詳細分析

1. AUTO_CLOSE (預設 OFF)

• 功能：最後一個使用者斷線後自動關閉資料庫，下次連線需重新開啟 (載入中繼資料、恢復)。
• 適用場景：僅限 Express/桌面版、極低頻存取的封存庫。
• 生產環境風險：頻繁開關導致連線延遲飆升、計畫快取反覆建構、效能極不穩定。
• 建議：生產環境必須 OFF。檢查到 ON 立即關閉並追查原因。

2. AUTO_CREATE_STATISTICS (預設 ON)

• 功能：查詢優化器在 WHERE/JOIN/GROUP BY 使用到無統計資料的欄位時，自動建立單欄統計資料。
• 效能影響：首次建立統計有微小編譯延遲，但後續查詢受益於精確基數估計，整體效能大幅提升。
• 建議：保持 ON。關閉僅會增加優化器誤判風險，無明顯收益。

3. AUTO_UPDATE_STATISTICS (預設 ON)

• 功能：資料變更達臨界值 (20% + 500 列) 時，下次查詢編譯前同步更新統計資料。
• 風險：大表統計更新可能在尖峰時段觸發，造成短暫阻塞。
• 對策：
  1. 優先保持 ON，配合 AUTO_UPDATE_STATISTICS_ASYNC ON (SQL 2005+)，改為背景非同步更新，查詢使用舊統計但不阻塞。
  2. 若確因同步更新嚴重影響業務，可關閉並改用 UPDATE STATISTICS / sp_updatestats 排程在離峰執行。

4. AUTO_SHRINK (預設 OFF)

• 功能：每 30 分鐘檢查資料庫檔案可縮減空間 > 25% 時自動執行 DBCC SHRINKDATABASE。
• 嚴重副作用：產生大量碎片、消耗 IO/CPU、導致索引失效、觸發自動增長再擴張 (反覆拉扯)。
• 建議：永遠 OFF。需釋放空間請手動排程 DBCC SHRINKFILE (N, TRUNCATEONLY) 或重建索引後單次縮減。

5. READ_ONLY (預設 OFF)

• 功能：資料庫唯讀，移除所有鎖 (Sch-S 除外)，查詢不產生鎖開銷。
• 適用：報表專用副本、歸檔庫、資料倉儲只讀區。
• 建議：真正唯讀場景啟用；需寫入時切回 READ_WRITE。

6. TORN_PAGE_DETECTION (SQL 2000 預設 ON / SQL 7.0 OFF)

• 背景：SQL Server 8KB 頁寫入磁碟需拆分為 16 個 512B 磁區。斷電導致部分磁區寫入失敗形成「撕裂頁」。
• 功能：啟用後，讀取頁面時驗證頁面檢查碼，偵測到撕裂頁即標記資料庫可疑並中斷存取。
• 效能代價：每次頁面寫入/讀取額外 CPU 計算檢查碼。
• 現代替代：SQL 2005+ 引入 PAGE_VERIFY CHECKSUM (預設)，更強健、效能影響更小。建議所有版本啟用 PAGE_VERIFY CHECKSUM，關閉 TORN_PAGE_DETECTION。

7. COMPATIBILITY_LEVEL (SQL 2000: 80 / SQL 7.0: 70)

• 功能：控制 T-SQL 行為相容舊版本。
• 效能影響：舊相容等級禁用新版查詢優化器特性 (如索引交叉、統計資料自動建立、參數化強制等)，導致執行計畫劣化。
• 建議：務必設定為目前版本最高等級 (SQL 2022: 160)。升級資料庫後立即執行 ALTER DATABASE ... SET COMPATIBILITY_LEVEL = 160 並全面回歸測試。

8. DATABASE AUTO GROW (預設 ON, 10% 或 1MB)

9. TRANSACTION LOG AUTO GROW (預設 ON, 10%)

• 核心原則：自動增長是**安全網**，非**容量規劃手段**。每次增長會鎖定資料庫、零初始化新空間 (資料檔案可啟用「立即檔案初始化」跳過)、產生碎片。
• 最佳實踐：
  1. 預先規劃容量：依歷史增長趨勢預估 1-2 年所需空間，一次性預配足夠大小。
  2. 固定增量優於百分比：大型資料庫 (10GB+) 百分比增長過大 (1GB+)，改用固定值 (如 512MB/1GB)。
  3. 記錄檔特別注意：過頻繁自動增長會產生大量虛擬記錄檔 (VLF)，導致啟動恢復、備份、鏡像/複寫延遲。建議預配足夠大小、固定增量 512MB-1GB、監控 VLF 數量 (DBCC LOGINFO)。
  4. 啟用「立即檔案初始化」：資料檔案增長受益 (需 SQL 服務帳號具備 SE_MANAGE_VOLUME_NAME 特權)。

三、一鍵檢查腳本

SELECT
    d.name AS DatabaseName,
    d.recovery_model_desc AS RecoveryModel,
    d.compatibility_level AS CompatLevel,
    -- 選項
    DATABASEPROPERTYEX(d.name, 'IsAutoClose') AS AutoClose,
    DATABASEPROPERTYEX(d.name, 'IsAutoCreateStatistics') AS AutoCreateStats,
    DATABASEPROPERTYEX(d.name, 'IsAutoUpdateStatistics') AS AutoUpdateStats,
    DATABASEPROPERTYEX(d.name, 'IsAutoShrink') AS AutoShrink,
    DATABASEPROPERTYEX(d.name, 'IsReadOnly') AS ReadOnly,
    DATABASEPROPERTYEX(d.name, 'IsTornPageDetectionEnabled') AS TornPageDetection,
    -- 檔案自動增長
    mf.name AS FileName,
    mf.type_desc AS FileType,
    mf.size * 8. / 1024 AS CurrentSizeMB,
    CASE mf.is_percent_growth
        WHEN 1 THEN CAST(mf.growth AS VARCHAR(10)) + '%'
        ELSE CAST(mf.growth * 8. / 1024 AS VARCHAR(10)) + ' MB'
    END AS AutoGrowth,
    mf.max_size * 8. / 1024 AS MaxSizeMB
FROM sys.databases d
JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.database_id > 4  -- 排除系統庫
ORDER BY d.name, mf.type_desc;

四、標準化配置基準線 (範本)

五、結語

資料庫層級配置選項雖單一資料庫生效，但累積百庫千庫即成系統級效能基線。將上述基準線納入部署清單、自動化巡檢腳本、變更管理流程，確保每個新建/遷移/升級的資料庫皆符合標準，是 DBA 交付高穩定、高效能環境的關鍵基本功。