為什麼事務日誌自動增長會降低你的效能

在這篇文章裡，我想詳細談下為什麼你要避免事務日誌（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 Log
CREATE 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 8kb
CREATE TABLE Chunk
(
    Col1 INT IDENTITY PRIMARY KEY,
    Col2 CHAR(8000)
)
GO

-- Another simple table
CREATE TABLE Foo
(    
    Bar INT NOT NULL
)
GO

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

-- Begin a new transaction, that blocks the 1st VLF in the Transaction Log
BEGIN TRANSACTION
INSERT 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 situation
SELECT wait_type, * FROM sys.dm_exec_requests
WHERE session_id IN (54, 55)

SELECT wait_type, * FROM sys.dm_os_waiting_tasks
WHERE 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再次確認。在那個特定閂鎖上你會看到一些等待。那個閂鎖是事務獲取的，由事務日誌的自動增長觸發，只要這個閂鎖要獲得，每個其他寫事務都會被阻塞。因此在系統上有大量等待時間時，這暗示這在事務日誌裡當前有自動增長問題需要處理。

希望我已經用這個日誌說服你，依賴於事務日誌的自動增長機制並不是最好的解決方案。用這個簡單的例子可以看到，在你資料庫裡每個被自動增長操作阻塞的寫入事務會發生阻塞，這肯定會傷及你資料庫的吞吐量和擴充套件性。為了保證你有很好的事務日誌效能，你可以最佳想實踐下這個文章。