数据库事务日志自动增长会降低性能的原因是什么
导读:本文共2374字符,通常情况下阅读需要8分钟。同时您也可以点击右侧朗读,来听本文内容。按键盘←(左) →(右) 方向键可以翻页。
摘要: 首先我为这个演示创建一个新的数据库。对于这个数据库,这里我不用默认的设置,对于事务日志,我指定了10GB的自动增长系数。这个的确是个不好的做法,但我只是用它来展示这个设置的副作用。请不要在你的生产数据库里使用这个错误配置!!!--Createanewdatabasewith10GBAutoGrowthfortheTransactionLogCREATEDATAB... ...
目录
(为您整理了一些要点),点击可以直达。首先我为这个演示创建一个新的数据库。对于这个数据库,这里我不用默认的设置,对于事务日志,我指定了10GB的自动增长系数。这个的确是个不好的做法,但我只是用它来展示这个设置的副作用。请不要在你的生产数据库里使用这个错误配置!!!
--Createanewdatabasewith10GBAutoGrowthfortheTransactionLogCREATEDATABASEAutoGrowthTransactionLogONPRIMARY(NAME=N'AutoGrowthTransactionLog',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog.mdf',SIZE=5120KB,FILEGROWTH=1024KB)LOGON(NAME=N'AutoGrowthTransactionLog_log',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog_log.ldf',SIZE=1024KB,FILEGROWTH=10240000KB--10GBAutoGrowth!)GO
下一步里我在数据库里创建2个表。第1个表我通过插入一些日志来快速填充我的事务日志。在事务日志自动增长阶段,我们在第2个表里插入新的记录来证明这个事务会被自动增长机制阻塞。
--Createanewtable,everyrecordsneedsapageof8kbCREATETABLEChunk(Col1INTIDENTITYPRIMARYKEY,Col2CHAR(8000))GO--AnothersimpletableCREATETABLEFoo(BarINTNOTNULL)GO
现在我们已经创建了必须的数据库对象,因次我可以通过新的没有立即提交的事务来填充事务日志:
--Beginanewtransaction,thatblocksthe1stVLFintheTransactionLogBEGINTRANSACTIONINSERTINTOChunkVALUES(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进入事务日志的自动增长。
--ThisstatementwilltriggertheAutoGrowthmechanism!INSERTINTOChunkVALUES(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语句:
--AnalyzetheblockingsituationSELECTwait_type,*FROMsys.dm_exec_requestsWHEREsession_idIN(54,55)SELECTwait_type,*FROMsys.dm_os_waiting_tasksWHEREsession_idIN(54,55)GO
从代码里可以看到,我用2个DMVsys.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等待类型。如你从DMVsys.dm_os_waiting_tasks 里的resource_description列所见,在SQL Server的日志管理器上需要获得闩锁。你可以通过查询DMVsys.dm_os_latch_stats 限制lactch class为LOG_MANAGER再次确认。在那个特定闩锁上你会看到一些等待。那个闩锁是事务获取的,由事务日志的自动增长触发,只要这个闩锁要获 得,每个其他写事务都会被阻塞。因此在系统上有大量等待时间时,这暗示这在事务日志里当前有自动增长问题需要处理。
</div> <div class="zixun-tj-product adv-bottom"></div> </div> </div> <div class="prve-next-news">
数据库事务日志自动增长会降低性能的原因是什么的详细内容,希望对您有所帮助,信息来源于网络。