为什么事务日志自动增长会降低你的性能

在这篇文章里,我想详细谈下为什么你要避免事务日志(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函数完成的。

相关推荐