数据库事务日志自动增长会降低性能的原因是什么

今天小编给大家分享一下数据库事务日志自动增长会降低性能的原因是什么的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。

首先我为这个演示创建一个新的数据库。对于这个数据库，这里我不用默认的设置，对于事务日志，我指定了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再次确认。在那个特定闩锁上你会看到一些等待。那个闩锁是事务获取的，由事务日志的自动增长触发，只要这个闩锁要获 得，每个其他写事务都会被阻塞。因此在系统上有大量等待时间时，这暗示这在事务日志里当前有自动增长问题需要处理。

以上就是“数据库事务日志自动增长会降低性能的原因是什么”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注亿速云行业资讯频道。