萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server DBA 核心管理實務:檔案架構、記憶體、I/O、備份、監控全景指南

1. 資料庫檔案架構三件套

  • .mdf:主資料檔(每資料庫唯一)。
  • .ndf:次要資料檔(可多個,跨檔案群組分散 I/O)。
  • .ldf:交易記錄檔(循環寫入,需定期備份截斷)。

2. 儲存單位與分配機制

  • 頁 (Page):8 KB,最小 I/O 單位。資料不跨頁 → 3KB 列 × 5 = 需 3 頁 (非 2 頁)。
  • 區 (Extent):8 頁 = 64 KB,空間配置單位。表空間不足時一次配置 1 區。
  • 大型值 (LOB):text/image/varchar(max) 儲存於 LOB 頁,資料頁僅存 16-24 字節根節點指標。

3. 記憶體與檢查點機制

  • 讀取:Buffer Cache → 缺頁 → 磁碟讀入 Cache。
  • 寫入 (增/刪/改):**不立即寫磁碟**,先修改 Buffer Cache (髒頁),由 Checkpoint 背景執行緒定期 (約 1 分鐘/或依恢復間隔) 將髒頁刷入磁碟。
  • 未認交易資料可能在 tempdb (版本存放/排序/雜湊) 或 Buffer Cache 中。

4. I/O 隔離:資料檔與記錄檔分盤

  • 記錄檔為循環序寫,資料檔為隨機讀寫。同一磁碟爭奪 I/O 會嚴重拖慢效能。
  • 最佳實踐:資料檔、記錄檔、tempdb、備份檔分別放置於獨立磁碟/陣列/LUN。
  • 每 4-6 小時執行記錄備份,截斷記錄檔避免暴增。

5. 檔案群組策略

  • 大型資料庫建立多檔案群組,不同表/索引置於不同群組 → 不同磁碟 → 並行 I/O。
  • 單一大表可跨群組分區,實現分區平行掃描。
  • 一般建議:先做表分區,再考慮多檔案群組。

6. 建庫時的檔案系統與大小規劃

  • NTFS 必備 (FAT32 單檔 2GB 上限)。
  • 預估成長,預先配置足夠空間,避免頻繁自動成長 (效能抖動、碎片)。
  • 自動成長設定固定 MB (非百分比),啟用瞬間檔案初始化 (服務帳戶授予 Perform Volume Maintenance Tasks)。
  • 需縮減檔案時:DBCC SHRINKFILE (謹慎使用,產生碎片),或重建索引後再縮減。

7. 備份勾選項與排程黃金法則

備份選項:驗證備份完成、檢查總和

  • 必勾:「驗證備份完成」、「執行檢查和檢查和」。
  • 排程:週末完整備份 → 平日差異備份 → 每 6 小時記錄備份。

8. 效能監控:SQL Server Profiler / 擴充事件

效能監視器啟動

  • 監控死鎖:加入 Lock:Deadlock、Lock:Deadlock Chain 事件。
  • 監控長時間查詢:RPC:Completed、SQL:BatchCompleted 篩選 Duration > 閾值。
  • 注意:追蹤本身消耗資源,生產環境建議使用輕量級 擴充事件 (Extended Events) 或僅在低峰期開啟 Profiler。

9. 總結:DBA 日常檢查清單

頻率檢查項目
每日備份成功/失敗、記錄檔使用率、磁碟空間、錯誤日誌、長時間執行查詢
每週索引碎片率、統計資料更新狀態、備份還原演練 (抽樣)
每月容量規劃、安全稽核、修補程式評估、災難恢復演練
copyright © 萬盛學電腦網 all rights reserved