dbi302_深入了解sqlserver交易纪录.ppt

深入了解 SQL Server 交易紀錄 胡百敬 合夥顧問 集英信誠 DBI302 大綱 交易紀錄簡介 Checkpoint Virtual Log Files 結構 VLF 重用 VLF 破碎 資料庫復原模式 交易紀錄備份 Bulk 交易紀錄 維護交易紀錄 循環使用 交易紀錄 資料檔案 資料存放的地方 一定在某個檔案群組內 考慮使用兩個以上的檔案群組，Primary 檔案群組存放系統物件 隨機讀寫 交易紀錄檔完成寫入後，才會到此更新 資料變更會寫入兩次，先交易紀錄，後資料檔案 交易紀錄檔 擷取資料庫變動的狀態 一定先更動此，再更新到資料檔 維護交易的 Atomicity 和 Durability 循序寫入，通常也循序讀出 一般只需要一個交易紀錄檔 交易紀錄先寫 在更新資料檔之前，所有的變化須能永久記錄在交易紀錄內 Atomic 變更能全部完成，或完全沒變 交易紀錄能保證回復可行 Durability 變更能在穩定的媒體上永久保存 放置交易紀錄檔 交易紀錄檔應該放哪？ 專用的 LUN 通常是較好的選項 如果存取量高，需要專屬的磁碟系統 RAID 1 或 RAID 10 提供保護與效能 RAID 5 通常不利於更新 儘量不要與其他檔案共用 不同的檔案放在一起存取，大家都變成隨機存取 SQL Server 資料庫上線 資料庫需要經過一系列的動作，才能進入一致的狀態 一般稱為 Recovery 需要交易紀錄 何時發生： 資料庫從離線轉成線上 附加資料庫 還原資料庫 服務停止重起/叢集容錯移轉 Recovery 的階段 Checkpoint 確保 Recovery 的 Redo 階段最少工作量 將更新過的資料頁(dirty page)寫入到磁碟內的資料檔 不管交易狀態如何，將所有的 dirty page 寫入到資料檔 可能造成大量的 I/O 不會從 Buffer pool 移除資料頁 當有記憶體壓力時，Lazy Writer 執行此工作 Checkpoint 相關選項 搭配 sp_configure 設定執行個體 recovery interval 選項 SQL Server 2012 後可設定資料庫層級 Target_Recovery_Time 選項 Demo Checkpoint 交易紀錄 保證資料庫的交易 每筆紀錄透過 Log Sequence Number 唯一標示 不僅與完成的交易有關 記載所有的變動 包含可以 Redo 和 Undo 交易的所有資訊 大綱 交易紀錄簡介 Checkpoint Virtual Log Files 結構 VLF 重用 VLF 破碎 資料庫復原模式 交易紀錄備份 Bulk 交易紀錄 維護交易紀錄 循環使用 交易紀錄 資料庫復原模式 交易紀錄備份 將 Redo 和 Undo 不再需要的交易紀錄存放到備份檔中，並從交易紀錄檔移除 搭配完整備份，以還原資料庫到特定時間點 Simple 復原模式無法備份交易紀錄 不保證交易紀錄空間會被重用 不會縮小交易紀錄檔案 Bulk Logged 特定的行為可以讓 SQL Server 僅記錄發生該行為以及使用的空間 可以讓動作執行得較快 注意：在 Bulk Logged 動作完成交易前，必須將受影響的資料頁內容寫入資料檔 交易紀錄檔成長得較小，但交易紀錄備份檔不會較小 Demo Bulk Logged 行為 大綱 交易紀錄簡介 Checkpoint Virtual Log Files 結構 VLF 重用 VLF 破碎 資料庫復原模式 交易紀錄備份 Bulk 交易紀錄 維護交易紀錄 循環使用 交易紀錄 記錄的行為 我們希望交易紀錄檔有限地成長 一旦不再需要局部的紀錄，期待該空間可以回收使用 交易紀錄有循環的特徵，應可以重用空間 DBA 負有管好交易紀錄的責任 什麽是合理的交易紀錄檔成長量，異常成長的原因為何？ 交易區塊(Log Block) 實體寫入到交易紀錄檔的單位 大小從 512 位元組到 60K 在記憶體中以 “log buffer” 儲存 儲存在 Buffer Pool 交易完畢或寫滿 60K，就更新到交易紀錄檔 較大的交易量(接近 60K)完成寫入較有效率 有時多筆小交易對效能不利 虛擬交易紀錄檔(Virtual Log File) 在交易紀錄檔內清除交易的單位 在建立交易紀錄檔或增長檔案大小時產生 資料庫內有多少數量的 VLF 是有差異的 交易紀錄檔增長 增加 VLF 的量 = 64 MB 4 64 MB 和 = 1 GB 8 1 GB 16 需要多少 VLF 檔案 依需求！ 太多，傷讀取紀錄和還原資料庫的效能 太少，很長時間才能釋放和重用 VLF 區段，造成交易紀錄檔更多的成長 若 VLF 檔案太多，SQL Server 會發出警告 Da