SQL Server 为什么事务日志自动增长会降低你的性能-阿里云开发者社区

开发者社区> 衣舞晨风> 正文

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

简介: 原文地址:点击打开链接   在这篇文章里,我想详细谈下为什么你要避免事务日志(Transaction Log)上的自动增长操作(Auto Growth operations)。很多运行的数据库服务器,对于事务日志,用的都是默认的日志文件大小和自动增长设置。人们有时会很依赖自动增长机制,因为它们刚好能正常工作。当然,如果它正常工作的话,你不必太关注它,但很快你会发现会有问题出现
+关注继续查看

原文地址:点击打开链接

  在这篇文章里,我想详细谈下为什么你要避免事务日志(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的自动增长系数。这个的确是个不好的做法,但我只是用它来展示这个设置的副作用。请不要在你的生产数据库里使用这个错误配置!!! 

复制代码
 1 -- Create a new database with 10 GB Auto Growth for the Transaction Log
 2 CREATE DATABASE AutoGrowthTransactionLog ON PRIMARY 
 3 (
 4     NAME = N'AutoGrowthTransactionLog', 
 5     FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog.mdf',
 6     SIZE = 5120KB, 
 7     FILEGROWTH = 1024KB
 8 )
 9 LOG ON 
10 (
11     NAME = N'AutoGrowthTransactionLog_log',
12     FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog_log.ldf',
13     SIZE = 1024KB,
14     FILEGROWTH = 10240000KB -- 10 GB Auto Growth!
15 )
16 GO
复制代码

  下一步里我在数据库里创建2个表。第1个表我通过插入一些日志来快速填充我的事务日志。在事务日志自动增长阶段,我们在第2个表里插入新的记录来证明这个事务会被自动增长机制阻塞。

复制代码
 1 -- Create a new table, every records needs a page of 8kb
 2 CREATE TABLE Chunk
 3 (
 4     Col1 INT IDENTITY PRIMARY KEY,
 5     Col2 CHAR(8000)
 6 )
 7 GO
 8 
 9 -- Another simple table
10 CREATE TABLE Foo
11 (    
12     Bar INT NOT NULL
13 )
14 GO
复制代码

  现在我们已经创建了必须的数据库对象,因次我可以通过新的没有立即提交的事务来填充事务日志:

1 -- Begin a new transaction, that blocks the 1st VLF in the Transaction Log
2 BEGIN TRANSACTION
3 INSERT INTO Chunk VALUES (REPLICATE('x', 8000))
4 GO

  因为我们现在有了进行中,没提交的事务,SQL Server不能重用那部分事务日志,即这个事务存储的事务日志。它们有需要回滚的可能。因此现在我通过不同的会话插入66条其他记录来填充事务日志:

1 INSERT INTO AutoGrowthTransactionLog.dbo.Chunk VALUES (REPLICATE('x', 8000))
2 GO 66

  最后在第一个会话里提交我们的事务:

1 COMMIT

  这意味着在我们面前有一个几乎满的的事务日志,我们可以通过DBCC LOGINFO来验证:

1 DBCC LOGINFO


  现在当我们往表里插入兮的记录时,事务日志已经没有可用空间了,SQL Server进入事务日志的自动增长。

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

  在自动增长期间的同时,为了监控发生了什么,我们可以在SSMS里打开新的一个会话窗口,尝试在第2个表插入另外的记录——表Foo

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

  这个SQL 语句会阻塞,因为事务要写入事务日志记录的事务日志,当前不可用。为了进一步分析这个阻塞情形,你可以打开第3个会话窗口,执行下列2个SQL语句:

复制代码
1 -- Analyze the blocking situation
2 SELECT wait_type, * FROM sys.dm_exec_requests
3 WHERE session_id IN (54, 55)
4 
5 SELECT wait_type, * FROM sys.dm_os_waiting_tasks
6 WHERE session_id IN (54, 55)
7 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再次确认。在那个特定闩锁上你会看到一些等待。那个闩锁是事务获取的,由事务日志的自动增长触发,只要这个闩锁要获得,每个其他写事务都会被阻塞。因此在系统上有大量等待时间时,这暗示这在事务日志里当前有自动增长问题需要处理。

  希望我已经用这个日志说服你,依赖于事务日志的自动增长机制并不是最好的解决方案。用这个简单的例子可以看到,在你数据库里每个被自动增长操作阻塞的写入事务会发生阻塞,这肯定会伤及你数据库的吞吐量和扩展性。为了保证你有很好的事务日志性能,你可以最佳想实践下这个文章

  感谢关注! 

  注:此文章为WoodyTu学习MS SQL技术,收集整理相关文档撰写,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出此文链接!



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Spring 4.0.2 学习笔记(2) - 自动注入及properties文件的使用
接上一篇继续, 学习了基本的注入使用后,可能有人会跟我一样觉得有点不爽,Programmer的每个Field,至少要有一个setter,这样spring配置文件中才能用...来注入. 能否不要这些setter方法? 答案是Yes 一、为Spring配置文件,添加annotation支持,以及 de...
669 0
【云吞铺子】业务系统性能压测最佳实践(二)--业务压测注意事项
云吞铺子--双11最佳技术实践系列# 本期继续为大家带来阿里云在本次双11实战中沉淀的前沿经验和满满干货!由阿里云技术高手霄翎为您深度阐述云上业务压测全流程注意事项!大牛出品,必属精品!
8967 0
如何查看sqlserver日志的方法
定期分析sqlserver日志是DBA很重要的任务,那如何才能查看sqlserver日志呢?    在SQL Server 7.0和SQL Server2000中,可以用下面的命令查看:     DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )  ...
1591 0
仿酷狗音乐播放器开发日志二十二 动态调色板控件第二版(性能大幅提升附源码)
转载请说明原出处,谢谢~~         在上次写的博客《仿酷狗音乐播放器开发日志二十一 开发动态调色板控件(附源码)》发布后,我在群里和网友讨论这个控件的性能和优 缺点,发现了他很多不足,还有很多提升空间,之后我简单的修改了代码提升了控件的响应速度。
869 0
性能测试工具选择策略——仿真度对比测评分析报告
基于 AI 的软件自动化测试工具的特征介绍
703 0
《 自动化测试最佳实践:来自全球的经典自动化测试案例解析》一一3.6 我们的心得
本节书摘来自华章出版社《 自动化测试最佳实践:来自全球的经典自动化测试案例解析 》一 书中的第3章,第3. 6节,作者:(英)Dorothy Graham Mark Fewster 著 ,更多章节内容可以访问云栖社区“华章计算机”公众号查看
1260 0
解决 webpack-dev-server 不能自动刷新的问题
原文发表于我的技术博客 此文主要帮助大家解决 webpack-dev-server 启动后修改源文件浏览器不能自动刷新的问题。 原文发表于我的技术博客 1. webpack 不能热加载的问题 主要的问题是各个版本之间的兼容性问题,请在本地的项目配置文件 package.json 中直接拷贝下面的配置文件,然后完整删除 node_modules 文件夹后,在项目文件夹下执行 npm install 即可。
1128 0
+关注
衣舞晨风
http://blog.csdn.net/jiankunking
701
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载