為什么事務(wù)日志自動增長會降低你的性能
在這篇文章里，我想詳細(xì)談下為什么你要避免事務(wù)日志（Transaction Log）上的自動增長操作（Auto Growth operations）。很多運(yùn)行的數(shù)據(jù)庫服務(wù)器，對于事務(wù)日志，用的都是默認(rèn)的日志文件大小和自動增長設(shè)置。人們有時會很依賴自動增長機(jī)制，因為它們剛 好能正常工作。當(dāng)然，如果它正常工作的話，你不必太關(guān)注它，但很快你會發(fā)現(xiàn)會有問題出現(xiàn)。
只依賴于事務(wù)日志的自動增長機(jī)制總不是個好主意。首先它會導(dǎo)致嚴(yán)重的日志碎片（Log Fragmentation），在SQL Server啟動期間，在你數(shù)據(jù)庫上執(zhí)行崩潰恢復(fù)（Crash Recovery）時會有很大的負(fù)面影響。另外，在你數(shù)據(jù)庫里寫入事務(wù)需要等待，只要事務(wù)日志觸發(fā)了自動增長機(jī)制。
當(dāng)事務(wù)日志的自動增長機(jī)制發(fā)生時，SQL Server總要零初始化新塊，這個會在文件末尾加上。這和你的SQL Server實例是否用即時文件初始化（Instant File Initialization）特權(quán)——事務(wù)日志總會零初始化。這上面的原因非常明顯：當(dāng)SQL Server在過去已經(jīng)完成事務(wù)日志的環(huán)繞式處理（wrap-around ），崩潰恢復(fù)（Crash Recovery）需要知道在哪里停。
零初始化的問題是會占用更多的時間（取決與你的自動增長率，還有你的存儲速度）。在此期間沒有別的事務(wù)可以寫事務(wù)日志記錄到事務(wù)日志。在事務(wù)日志管 理器上會有閂鎖造成的阻塞。因此你的寫入事務(wù)會進(jìn)入掛起狀態(tài)（直到它們獲得需要的閂鎖），它們就等啊，等啊，等啊，直到你的事務(wù)日志自動增長完成。讓我們 用一個簡單的例子演示下。
首先我為這個演示創(chuàng)建一個新的數(shù)據(jù)庫。對于這個數(shù)據(jù)庫，這里我不用默認(rèn)的設(shè)置，對于事務(wù)日志，我指定了10GB的自動增長系數(shù)。這個的確是個不好的做法，但我只是用它來展示這個設(shè)置的副作用。請不要在你的生產(chǎn)數(shù)據(jù)庫里使用這個錯誤配置！！！
- -- 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
下一步里我在數(shù)據(jù)庫里創(chuàng)建2個表。第1個表我通過插入一些日志來快速填充我的事務(wù)日志。在事務(wù)日志自動增長階段，我們在第2個表里插入新的記錄來證明這個事務(wù)會被自動增長機(jī)制阻塞。
- -- 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
現(xiàn)在我們已經(jīng)創(chuàng)建了必須的數(shù)據(jù)庫對象，因次我可以通過新的沒有立即提交的事務(wù)來填充事務(wù)日志：
- -- Begin a new transaction, that blocks the 1st VLF in the Transaction Log
- BEGIN TRANSACTION
- INSERT INTO Chunk VALUES (REPLICATE('x', 8000))
- GO
因為我們現(xiàn)在有了進(jìn)行中，沒提交的事務(wù)，SQL Server不能重用那部分事務(wù)日志，即這個事務(wù)存儲的事務(wù)日志。它們有需要回滾的可能。因此現(xiàn)在我通過不同的會話插入66條其他記錄來填充事務(wù)日志：
INSERT INTO AutoGrowthTransactionLog.dbo.Chunk VALUES (REPLICATE('x', 8000))
GO 66
***在***個會話里提交我們的事務(wù)：
COMMIT
這意味著在我們面前有一個幾乎滿的的事務(wù)日志，我們可以通過DBCC LOGINFO來驗證：
DBCC LOGINFO
現(xiàn)在當(dāng)我們往表里插入兮的記錄時，事務(wù)日志已經(jīng)沒有可用空間了，SQL Server進(jìn)入事務(wù)日志的自動增長。
- -- This statement will trigger the Auto Growth mechanism!
- INSERT INTO Chunk VALUES (REPLICATE('x', 8000))
- GO
在自動增長期間的同時，為了監(jiān)控發(fā)生了什么，我們可以在SSMS里打開新的一個會話窗口，嘗試在第2個表插入另外的記錄——表Foo：
-- This statement is now blocked by the Auto Growth mechanism.
INSERT INTO Foo VALUES (1)
GO
這個SQL 語句會阻塞，因為事務(wù)要寫入事務(wù)日志記錄的事務(wù)日志，當(dāng)前不可用。為了進(jìn)一步分析這個阻塞情形，你可以打開第3個會話窗口，執(zhí)行下列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
（額，俺本機(jī)測試失敗………………）
從代碼里可以看到，我用2個DMV sys.dm_exec_requests 和 sys.dm_os_waiting_tasks對2個會話都進(jìn)行了跟蹤——觸發(fā)自動增長的會話，和被自動增長機(jī)制阻塞的會話。在這里，觸發(fā)自動增長的會 話里有所謂的搶占等待類型（Preemptive Wait Type）——PREEMPTIVE_OS_WRITEFILEGATHER。搶占等待類型是由SQL Server返回的等待類型，當(dāng)SQL Server 執(zhí)行一個WIN32 API函數(shù)在調(diào)度機(jī)制之外時。這里自動增長是通過WriteFileGather的WIN32 API函數(shù)完成的。
INSERT語句嘗試在Foo表里插入新的記錄出現(xiàn)LATCH_EX等待類型。如你從DMV sys.dm_os_waiting_tasks 里的resource_description列所見，在SQL Server的日志管理器上需要獲得閂鎖。你可以通過查詢DMV sys.dm_os_latch_stats 限制lactch class為LOG_MANAGER再次確認(rèn)。在那個特定閂鎖上你會看到一些等待。那個閂鎖是事務(wù)獲取的，由事務(wù)日志的自動增長觸發(fā)，只要這個閂鎖要獲 得，每個其他寫事務(wù)都會被阻塞。因此在系統(tǒng)上有大量等待時間時，這暗示這在事務(wù)日志里當(dāng)前有自動增長問題需要處理。
希望我已經(jīng)用這個日志說服你，依賴于事務(wù)日志的自動增長機(jī)制并不是***的解決方案。用這個簡單的例子可以看到，在你數(shù)據(jù)庫里每個被自動增長操作阻塞的寫入事務(wù)會發(fā)生阻塞，這肯定會傷及你數(shù)據(jù)庫的吞吐量和擴(kuò)展性。為了保證你有很好的事務(wù)日志性能，你可以***想實踐下這個文章。
