SQL Server中事务日志自动增长对性能的影响（上）

日期： 2009-07-02 作者：Michelle Gutzait翻译：孙瑞 来源：TechTarget中国 英文

在本文中，我将对事务运行过程中事务日志增长对性能的影响做测试。我这次的测试环境和配置同前两次的相同。 　　数据库 　　本次测试中使用的ShrinkDB数据库要预先配置好，保证数据库足够大并在事务运行中不会增长。事务日志文件大小只有2MB。

测试的目的就是让它随着事务进行而增长，从而衡量它的影响： 　　ShrinkDB数据库的还原模型设置为FULL。 　　使用以下查询语句（fileid = 2 → the T-Log file）： 　　select size from sysfiles where fileid = 2 　　这是事务日志文件的大小（每页8KB）。256*8=2MB。 　　原先测试用……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

在本文中，我将对事务运行过程中事务日志增长对性能的影响做测试。我这次的测试环境和配置同前两次的相同。

　　数据库

　　本次测试中使用的ShrinkDB数据库要预先配置好，保证数据库足够大并在事务运行中不会增长。事务日志文件大小只有2MB。测试的目的就是让它随着事务进行而增长，从而衡量它的影响：

　　ShrinkDB数据库的还原模型设置为FULL。

　　使用以下查询语句（fileid = 2 → the T-Log file）：

　　select size from sysfiles where fileid = 2

　　这是事务日志文件的大小（每页8KB）。256*8=2MB。

　　原先测试用过的ExpandDB表在本次测试用依然使用。

　　create table ExpandDB （a varchar（8000））

　　测试

　　测试的目标就是检验让数据库事务日志自动增长的INSERT, UPDATE和DELETE命令如何影响性能。

　　由于升级，插入和删除可能会相互影响，所以这个测试包含三个部分，每次一个操作：

　　UPDATES

　　INSERTS

　　DELETE

　　注意：在原先的文章中，我证明了只有大量的事务才能使事务日志自动增长。

　　插入命令

　　在本次测试中，一次事务我都向表中插入了10，000行。每一步都执行相同的代码，但事务日志的autogrowth设定不同：

　　在第一步中， autogrowth设置为1MB;

　　在第二步中， autogrowth设置为10MB。

　　这两个步骤总共执行三次，SQL Profiler来捕获执行统计。以下为代码：

　

  　-- Truncate the table
　　truncate table ExpandDB
　　go
　　-- Truncate the T-Log
　　backup transaction ShrinkDB with truncate_only
　　go
　　-- Shrink T-Log back to 2MB （initial size）:
　　DBCCSHRINKFILE （N'ShrinkDB_Log', 0, TRUNCATEONLY
　　Go
　　-- Insert 10000 rows
　　-- Big transaction
　　begin tran
　　declare @i int
　　set @i = 1
　　while @i <= 10000
　　begin insert into ExpandDB select replicate （'a',8000）
　　set @i = @i + 1
　　end
　　commit
　　Go
　　-- Check size and % free space in T-Log
　　dbcc sqlperf（logspace
　　go
　　select count（*） from ExpandDB
　　go
　　在第一步中，事务日志达到110MB。在第二步中达到了102MB。

　　以下是结果比较（只有插入）：

    我又进行了第三次测试，事务日志文件设定为130MB并在事务运行期间不增长。这次我没有缩减事务日志，代码如下：

　　-- Truncate the T-Log
　　backup transaction ShrinkDB with truncate_only
　　go
　　-- Insert 10000 rows
　　-- Big transaction
　　begin tran
　　declare @i int
　　set @i = 1
　　while @i <= 10000
　　begin
　　insert into ExpandDB select replicate （'a',8000）
　　set @i = @i + 1
　　end
　　commit
　　Go
　　-- Check size and % free space in T-Log
　　dbcc sqlperf（logspace）
　　go
　　select count（*）
　　from ExpandDB
　　go
　　现在我们有了上一次执行和第一次执行的比较，只有插入命令：

　　插入总结：

　　平均改进数据显示性能的增强和事务运行中事务日志增长数目有关。自动增长越少性能越佳，特别是对于整段时间而言（无增长比1MB增长性能改进了80%）。

　　升级命令

　　在上一篇文章中显示，当每次都有很多行改变时，升级会令事务日志增长。为此，我执行了以下代码：

　　

-- Truncate the T-Log
　　backup transaction ShrinkDB with truncate_only
　　go
　　-- Shrink T-Log back to 2MB:
　　DBCC SHRINKFILE （N'ShrinkDB_Log' , 0, TRUNCATEONLY）
　　Go
　　-- Update 10000 rows at a time
　　update ExpandDB set a = 'aaaaaaaaaaaaaaaaaaaaaaaa'
　　go
　　-- Check size and % free space in T-Log
　　dbcc sqlperf（logspace）
　　go
　　我运行了两次以确保事务日志按相同比例增长，令人困惑的是第一次运行中时事务日志增长了，而第二次一点没有增长（停留在2MB）！

　　这可能意味着行值不变的话，事务日志也就不增长。为证明，我加了一个Select语句来输出被升级影响的行数：

　　-- Truncate the T-Log
　　backup transaction ShrinkDB with truncate_only
　　go
　　-- Shrink T-Log back to 2MB:
　　DBCC SHRINKFILE （N'ShrinkDB_Log' , 0, TRUNCATEONLY）
　　Go
　　-- Update 10000 rows at a time
　　update ExpandDB set a = 'aaaaaaaaaaaaaaaaaaaaaaaa'
　　select @@rowcount go
　　-- Check size and % free space in T-Log
　　dbcc sqlperf（logspace）
　　go

　　返回的行值一直是10000.

　　当我修改了升级值（update ExpandDB set a = 'abcde'），

　　

    -- #1 ITERATION:
　　-- Truncate the T-Log
　　backup transaction ShrinkDB with truncate_only
　　go -- Shrink T-Log back to 2MB:
　　DBCC SHRINKFILE （N'ShrinkDB_Log' , 0, TRUNCATEONLY）
　　Go
　　-- Update 10000 rows at a time
　　update ExpandDB set a = 'aaaaaaaaaaaaaaaaaaabc'
　　go
　　-- Check size and % free space in T-Log
　　dbcc sqlperf（logspace）
　　go
　　-- #2 ITERATION:
　　-- Truncate the T-Log
　　backup transaction ShrinkDB with truncate_only
　　go -- Shrink T-Log back to 2MB:
　　DBCC SHRINKFILE （N'ShrinkDB_Log' , 0, TRUNCATEONLY）
　　Go
　　-- Update 10000 rows at a time
　　update ExpandDB set a = 'AAA'
　　go
　　-- Check size and % free space in T-Log
　　dbcc sqlperf（logspace）
　　go
　　-- #3 ITERATION:
　　-- Truncate the T-Log
　　backup transaction ShrinkDB with truncate_only
　　go -- Shrink T-Log back to 2MB:
　　DBCC SHRINKFILE （N'ShrinkDB_Log' , 0, TRUNCATEONLY）
　　Go
　　-- Update 10000 rows at a time
　　update ExpandDB set a = 'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz'
　　go
　　-- Check size and % free space in T-Log
　　dbcc sqlperf（logspace）
　　go
　　不出所料，当事务日志设置为每次增长1MB时，它最后的大小是不同的。

　　以下是结果的对比：


作者

Michelle Gutzait
Michelle Gutzait

翻译

孙瑞
孙瑞

相关推荐