【轉】為什麼交易記錄自動成長會降低你的效能

標籤：

在這篇文章裡，我想詳細談下為什麼你要避免交易記錄（Transaction Log）上的自動成長操作（Auto Growth operations）。很多啟動並執行資料庫伺服器，對於交易記錄，用的都是預設的記錄檔大小和自動成長設定。人們有時會很依賴自動成長機制，因為它們剛好能正常工作。當然，如果它正常工作的話，你不必太關注它，但很快你會發現會有問題出現。

只依賴於交易記錄的自動成長機制總不是個好主意。首先它會導致嚴重的日誌片段（Log Fragmentation），在SQL Server啟動期間，在你資料庫上執行崩潰恢複（Crash Recovery）時會有很大的負面影響。另外，在你資料庫裡寫入事務需要等待，只要交易記錄觸發了自動成長機制。

當交易記錄的自動成長機制發生時，SQL Server總要零初始化新塊，這個會在檔案末尾加上。這和你的SQL Server執行個體是否用檔案立即初始化（Instant File Initialization）特權——交易記錄總會零初始化。這上面的原因非常明顯：當SQL Server在過去已經完成交易記錄的環繞式處理（wrap-around ），崩潰恢複（Crash Recovery）需要知道在哪裡停。

零初始化的問題是會佔用更多的時間（取決與你的自動成長率，還有你的儲存速度）。在此期間沒有別的事務可以寫交易記錄記錄到交易記錄。在交易記錄管理器上會有閂鎖造成的阻塞。因此你的寫入事務會進入掛起狀態（直到它們獲得需要的閂鎖），它們就等啊，等啊，等啊，直到你的交易記錄自動成長完成。讓我們用一個簡單的例子示範下。

首先我為這個示範建立一個新的資料庫。對於這個資料庫，這裡我不用預設的設定，對於交易記錄，我指定了10GB的自動成長係數。這個的確是個不好的做法，但我只是用它來展示這個設定的副作用。請不要在你的生產資料庫裡使用這個錯誤配置！！！

-- Create a new database with 10 GB Auto Growth for the Transaction LogCREATE DATABASE AutoGrowthTransactionLog ON PRIMARY (    NAME = N‘AutoGrowthTransactionLog‘,     FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog.mdf‘,    SIZE = 5120KB,     FILEGROWTH = 1024KB)LOG ON (    NAME = N‘AutoGrowthTransactionLog_log‘,    FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog_log.ldf‘,    SIZE = 1024KB,    FILEGROWTH = 10240000KB -- 10 GB Auto Growth!)GO

下一步裡我在資料庫裡建立2個表。第1個表我通過插入一些日誌來快速填入我的交易記錄。在交易記錄自動成長階段，我們在第2個表裡插入新的記錄來證明這個事務會被自動成長機制阻塞。

-- Create a new table, every records needs a page of 8kbCREATE TABLE Chunk(    Col1 INT IDENTITY PRIMARY KEY,    Col2 CHAR(8000))GO-- Another simple tableCREATE TABLE Foo(        Bar INT NOT NULL)GO

現在我們已經建立了必須的資料庫物件，因次我可以通過新的沒有立即提交的事務來填充交易記錄：

-- Begin a new transaction, that blocks the 1st VLF in the Transaction LogBEGIN TRANSACTIONINSERT INTO Chunk VALUES (REPLICATE(‘x‘, 8000))GO

因為我們現在有了進行中，沒提交的事務，SQL Server不能重用那部分交易記錄，即這個事務儲存的交易記錄。它們有需要復原的可能。因此現在我通過不同的會話插入66條其他記錄來填充交易記錄：

INSERT INTO AutoGrowthTransactionLog.dbo.Chunk VALUES (REPLICATE(‘x‘, 8000))GO 66

最後在第一個會話裡提交我們的事務：

COMMIT

這意味著在我們面前有一個幾乎滿的的交易記錄，我們可以通過DBCC LOGINFO來驗證：

DBCC LOGINFO

現在當我們往表裡插入兮的記錄時，交易記錄已經沒有可用空間了，SQL Server進入交易記錄的自動成長。

-- This statement will trigger the Auto Growth mechanism!INSERT INTO Chunk VALUES (REPLICATE(‘x‘, 8000))GO

在自動成長期間的同時，為了監控發生了什麼，我們可以在SSMS裡開啟新的一個會話視窗，嘗試在第2個表插入另外的記錄——表Foo：

-- This statement is now blocked by the Auto Growth mechanism.INSERT INTO Foo VALUES (1)GO

這個SQL 陳述式會阻塞，因為事務要寫入交易記錄記錄的交易記錄，當前不可用。為了進一步分析這個阻塞情形，你可以開啟第3個會話視窗，執行下列2個SQL語句：

-- Analyze the blocking situationSELECT wait_type, * FROM sys.dm_exec_requestsWHERE session_id IN (54, 55)SELECT wait_type, * FROM sys.dm_os_waiting_tasksWHERE session_id IN (54, 55)GO

（額，俺本機測試失敗………………）

從代碼裡可以看到，我用2個DMV sys.dm_exec_requests 和 sys.dm_os_waiting_tasks對2個會話都進行了跟蹤——觸發自動成長的會話，和被自動成長機制阻塞的會話。在這裡，觸發自動成長的會話裡有所謂的搶佔等待類型（Preemptive Wait Type）——PREEMPTIVE_OS_WRITEFILEGATHER。搶佔等待類型是由SQL Server返回的等待類型，當SQL Server 執行一個WIN32 API函數在調度機制之外時。這裡自動成長是通過WriteFileGather的WIN32 API函數完成的。

INSERT語句嘗試在Foo表裡插入新的記錄出現LATCH_EX等待類型。如你從DMV sys.dm_os_waiting_tasks 裡的resource_description列所見，在SQL Server的日誌管理器上需要獲得閂鎖。你可以通過查詢DMV sys.dm_os_latch_stats 限制lactch class為LOG_MANAGER再次確認。在那個特定閂鎖上你會看到一些等待。那個閂鎖是事務擷取的，由交易記錄的自動成長觸發，只要這個閂鎖要獲得，每個其他寫事務都會被阻塞。因此在系統上有大量等待時間時，這暗示這在交易記錄裡當前有自動成長問題需要處理。

希望我已經用這個日誌說服你，依賴於交易記錄的自動成長機制並不是最好的解決方案。用這個簡單的例子可以看到，在你資料庫裡每個被自動成長操作阻塞的寫入事務會發生阻塞，這肯定會傷及你資料庫的輸送量和擴充性。

【轉】為什麼交易記錄自動成長會降低你的效能