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 

 4 NAME = NAutoGrowthTransactionLog, 

 5 FILENAME = NC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog.mdf,

 6 SIZE = 5120KB, 

 7 FILEGROWTH = 1024KB

 9 LOG ON 

11 NAME = NAutoGrowthTransactionLog_log,

12 FILENAME = NC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog_log.ldf,

13 SIZE = 1024KB,

14 FILEGROWTH = 10240000KB -- 10 GB Auto Growth!

16 GO

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

 1 -- Create a new table, every records needs a page of 8kb

 2 CREATE TABLE Chunk

 4 Col1 INT IDENTITY PRIMARY KEY,

 5 Col2 CHAR(8000)

 7 GO

 9 -- Another simple table

10 CREATE TABLE Foo

11 ( 

12 Bar INT NOT NULL

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)

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技术，收集整理相关文档撰写，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出此文链接！

Mysql中 慢查询日志和show profile进行sql分析 MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
【笔记】用户指南—诊断与优化—SQL审计与分析—日志报表 PolarDB-X 支持SQL审计与分析功能，依托日志服务，提供了运营中心、性能中心、安全中心等开箱即用的报表，方便您快速查看了解PolarDB-X数据库的执行状况、性能指标、潜在安全问题等情况。
通过定时SQL提取阿里云API网关访问日志指标 阿里云API网关服务提供API托管服务，提供了强大的适配和集成能力，可以将各种不同的业务系统API实现统一管理。API网关同时支持将API访问日志一键存储到日志服务，通过日志服务强大的查询分析能力，用户可以针对访问日志自定义计算多种指标，监测服务运行情况。继而通过定时SQL将结果指标直接存储到时序库，直接查询API网关指标，而且可以通过较低成本长期存储指标数据。 下面我们一步步从头开始，学习如何利用日志服务监测API网关及其代理的服务。