SQL Server DBA 核心管理實戰：檔案架構、記憶體、I/O 隔離、監控與備份精要

資料庫檔案三大件

儲存單位與配置邏輯

• 頁 = 8 KB，資料不跨頁儲存（3 KB 資料佔 1 頁，剩 5 KB 空洞）
• 區 = 8 頁 = 64 KB，空間配置最小單位
• 大物件 (LOB: text, image, varchar(max)) 儲存於獨立頁面，資料頁只存指標（≤ 8 bytes）

記憶體與檢查點機制

• 讀取：Buffer Cache → 磁碟（未命中才讀磁碟）
• 寫入：非即時寫磁碟，先改 Buffer Cache，由 Checkpoint 定期批次刷盤（預設約 1 分鐘/或依恢復間隔動態調整）
• 未認交易資料可能在：tempdb（版本存儲/暫存表）或 Buffer Cache（髒頁）

I/O 隔離黃金法則

1. 資料檔與記錄檔分離：不同磁碟/陣列，平行 I/O
2. 大型資料庫使用檔案群組：不同資料表/索引放不同檔案群組 → 不同磁碟
3. 資料表分區：單表過大時，水平分區到不同檔案群組，實現分區剪裁與並行掃描

建立資料庫實戰細節

• 磁碟格式必須 NTFS（FAT32 單檔 2GB 上限）
• 檔案初始大小預留成長空間，避免頻繁自動成長碎片化
• 啟用 瞬間檔案初始化 (IFI)：授予 SQL Server 服務帳號 SE_MANAGE_VOLUME_NAME 權限，DBCC TRACEON(3004, 3605) 可驗證
• 自動收縮 生產環境禁用（碎片化、效能抖動），改用維護計畫定期 DBCC SHRINKFILE 配合索引重建

備份鐵律

1. 完整備份：週末
2. 差異備份：平日每日
3. 記錄備份：每 30-60 分鐘
4. 順序固定：完整性檢查 → 尾部記錄備份 → 完整/差異/記錄備份
5. 勾選「驗證備份」與「檢查總和檢查碼」
6. 備份檔異地備份（異地機房/雲端）

效能監控：資料收集器與效能監視器

• SSMS → 管理 → 資料收集器 → 設定收集集合
• 效能監視器 (perfmon) 關鍵計數器：
  • SQLServer:Buffer Manager\Page life expectancy > 300 秒
  • SQLServer:Memory Manager\Target Server Memory vs Total Server Memory
  • PhysicalDisk\Avg. Disk sec/Read/Write < 10ms
  • SQLServer:Locks\Deadlocks/sec = 0
  • SQLServer:General Statistics\User Connections 趨勢
• 死鎖追蹤：擴充事件 (Extended Events) xml_deadlock_report，輕量級替代 Profiler

壓縮資料庫 vs 壓縮檔案

• DBCC SHRINKDATABASE / DBCC SHRINKFILE：釋放未使用空間，會產生嚴重索引碎片，壓縮後必跑索引重建
• 資料壓縮：ROW/PAGE 壓縮（Enterprise 版），CPU 換 I/O，OLTP 謹慎評估