SQL Server 日志配置问题

xiaodouya33

　　这篇文章将会讨论事务日志性能主题以及由于事务日志配置导致的问题。原文来自：http://www.sqlperformance.com/2013/02/system-configuration/transaction-log-configuration
　　
　　太多VLFs
　　
　　SQL Server数据库引擎在内部将每一物理日志文件分成多个虚拟日志文件,这样日志管理系统可以轻松的跟踪那些部分是可以被重用的。事务日志文件根据下面的公式决定生成多少个VLFs,不管是自动增长还是手动增长：
　　Up to 1MB
　　2 VLFs, each  roughly 1/2 of the total size
　　1MB to 64MB
　　4 VLFs, each  roughly 1/4 of the total size
　　64MB to 1GB
　　8 VLFs, each  roughly 1/8 of the total size
　　More than 1GB
　　16 VLFs, each  roughly 1/16 of the total size
　　举个例子，如果创建一个8GB的事务日志文件，那么会得到16个VLF,每个大约512MB.如果日志一次性增长4GB,那么我们会得到另外16个VLF,每个大约256MB，整个文件具有32个VLF.
　　一般最好的做法是设置日志的自动增长而不是默认的10%,这样你可以更好控制日志由于zero-initializing操作导致的暂停。比方说，你创建一个256MB的事务日志，并设置自动增长到32MB，然后将日志增长到16GB的稳态大小。根据上述公式，这将导致你的交易记录有4000多的VLF。
　　这许多的VLF很可能会对需要事务日志的操作（如崩溃恢复，清除日志，日志备份，事务复制，数据库恢复）产生性能问题。这种情况被称为有VLF碎片。一般来说任何数量的VLF超过一千元左右将是有问题的，需要加以解决（我曾经听说过的最多的是154万的VLF在超过1TB的大小事务日志！）。
　　太多的VLFs可能会导致一些操作在处理日志的时候遇到性能问题(比如崩溃恢复，清除日志，日志备份，事务复制，数据库恢复)。这种情况被称为VLF碎片。一般来说超过1000的VLF是有问题的，需要加以解决（我曾经听说过的1TB的事务日志文件有超过154W的VLF）.
　　查询VLFs的数量可以使用undocumented（绝对安全）的DBCC LOGINFO命令。输出的行数就是VLF在事务日志中的数量。如果你觉得VLF太多，可以用下面的方式减少：
　　1.清除日志（比如通过日志备份等等截断日志）
　　2.手动收缩日志文件
　　3.重复步骤1和2，直到日志达到小尺寸（在繁忙的生产系统可能会比较麻烦）
　　4.手动将日志增长到期望的大小，比如8GB这样VLFS单个VLF不超过0.5GB.
　　你可以阅读更多有关VLF碎片问题并且如何解决：
　　·        Microsoft KB article that advises reducing VLF numbers
　　·        Can log files growth affect DML?
　　·        8 steps to better transaction log throughput
　　Tempdb
　　
　　
　　Tempdb日志配置应该跟其他数据库一样，而且也会像其他数据库一样自动增长。但是它有一些潜在的因素会导致问题。当一个SQL Server实例重新启动后，tempdb数据库的数据和日志文件将恢复到初始文件设置的大小，而其他数据库会保持当前大小不变。
　　这种行为意味着当tmpdb已经增长到合适大小，你必须使用Alter database设置日志文件的固定大小，否则重启之后日志文件需要从设置的初始值增长到合适大小。每当日志增长，新的空间必须要做零初始化并且将导致日志记录暂停，这个会影响性能。所以如果你没有正确的管理tempdb日志文件的大小，那么在实例重启之后你将会付出性能的损失。
　　定期日志收缩　
　　
　　
　　很多时候听到人们说他们在发现由于一些些普通的操作（例如每周一次的数据导入）导致数据库日志增长时，会做定期的收缩，这个操作我是不建议的。
　　正如我上面所解释的，每当事务日志增长或自动增长，都会因为日志文件zero-initialize动作导致停顿。如果事务日志文件经常需要增长到大小x,那么这意味着你的应用将会在日志增长到X的过程中遭受性能影响。
　　如果你的交易记录不断增加X大小，不要管它！主动将其设置为大小X，按照我们上面提到的方法管理VLF,因为这个大小是数据库正常操作需要的。
　　多个事务日志文件
　　
　　
　　一个数据库创建多个日志文件对性能不会有提升。当前的日志空间不足时，可能需要增加第二个日志文件。如果不增加第二个日志文件可以通过将数据库的恢复模式修改为Simple并且执行检查点(这会打破记录备份链)。
　　经常有人问我是否要除去第二个日志文件还是将它保留在原处。答案是尽快将其移除。
　　虽然第二个日志文件不会导致性能问题，但是可能影响灾难恢复。如果你的数据库由于某种原因被损坏，你需要从头恢复。还原的第一步是当数据和日志文件不存在的时候创建这两个文件。当你创建数据文件的时候可以启用instantfile initialization参数，这个选项会跳过zero-initialization，但是这个参数不适用于日志文件。这意味着使用完整备份恢复需要创建所有的日志文件（或在事务日志备份恢复期间）并且做zero-initialize。如果创建了第二个日志文件但是没有删除，zero-initialize过程增加了总的停机时间。虽然这不会导致性能问题，但是影响了服务器的可用性。
　　从数据库快照恢复
　　
　　
　　最后一个问题其实是在SQL Server中的bug。如果你使用一个数据库快照，以此来快速恢复到某个时间已知点从而避免还原备份（称为从快照恢复），那么你就可以节省大量的时间。然而，有一个很大的缺点。
　　当数据库从数据库快照恢复，事务日志重新创建了两个0.25MB的VLF。这意味着你需要手动调整日志文件大小到最佳值（或它会自动增长），否则又会导致前面提到的零初始化并且导致日志暂停的问题，显然这不是我们期望的。
　　总结：
　　
　　
　　从这篇文章可以看出，有很多事情可以导致事务日志性能问题，进而导致整个库性能问题。可以利用我们上面提到的方法设置你的日志，这样会拥有健康的日志。除此之外，你还需要监视事务日志，比如由于自动增长和　过度的读取和写入ＩＯ延迟。这些会在将来的文章中解释。