SQL 问题与解答：瓶颈和事务日志

  • 项目

确定性能瓶颈的问题所在有时会很容易，但并非总是如此。配置事务日志也是这样。

Paul S. Randal

存储瓶颈

**问：**我在设法确定系统性能瓶颈的问题所在。我相信这是存储层的问题，因为我看到磁盘队列长度超过 2。我以前了解到，这能够证明 SQL Server 使得存储过载。真的是这样吗？如果是这样，我该怎么办呢？

**答：**很遗憾，您了解到的只是一种不可信的传闻。SQL Server 被设计为采用异步 IO，它能够妥善地将磁盘队列长度增加到 2 以上。每个发出 IO 的线程都可以继续执行其他任务（如果有），直到 IO 完成。SQL Server 会通过发出并发的异步 IO，力图使 IO 子系统的吞吐量最大化。此外，它还会在扫描大量数据时，执行预读之类的操作。

实际上，某些操作（如 DBCC CHECKDB）将使 IO 子系统处于满负荷状态。因此，磁盘队列长度达到数百都不奇怪。有关磁盘队列长度传闻的更多信息，请阅读此博客文章。

这样，问题就出现了：“如何判断 IO 子系统确实 遇到瓶颈了？”在物理磁盘性能对象中，有两个性能计数器。您应该注意观察以下数据：

  • 每次读取的平均磁盘扇区数
  • 每次写入的平均磁盘扇区数

这些数据显示了完成 IO 所花的时间（以毫秒计）。如果这些数值持续高于（或者定期出现较高峰值）正常值（5 毫秒至 12 毫秒之间），那就说明物理磁盘是 IO 瓶颈所在。当然，也可能物理磁盘是 SAN LUN，而您无法从 Windows 系统深入挖掘它的存储能力。

如果您的物理磁盘上存在多个 SQL Server 数据和日志文件，您可能就需要确定是哪些文件产生了 IO 负载。请利用动态管理视图 (DMV) sys.dm_io_virtual_file_stats，并对结果执行一些简单的时间系列分析。

如果 DMV 结果显示该物理磁盘上不存在很高的负载，则可能是存储管理员在该部分 IO 子系统中放置了其他应用程序的文件。工作负载可能是由独占 IO 带宽的文件产生的。在这种情况下，您需要请相关管理员将这些 SQL Server 文件移到专门的 IO 子系统部分。

如果仅仅是子系统上的 SQL Server 文件，并且您能够找出那些造成过多 IO 的文件，则可以考虑采取以下措施：

  • 查看数据库查询工作负载，确定是否由于错误的索引策略而导致执行过度表扫描，或者是否由于过期的统计数据而导致执行不当查询计划。
  • 将一些文件移到 IO 子系统的其他部分。
  • 在服务器中再添加一些内存，以获得更大的 SQL Server 缓冲池（内存中的数据文件页缓存），避免过多的读 IO。

如果上述措施不起作用，那就说明工作负载确实超出了 IO 子系统的承载量，请将其转移到承载量更大的 IO 子系统上。您也可以考虑使用企业级闪存存储系统，例如 Fusion-io。

大小问题

**问：**我在规划几台新服务器的存储需求，在确定事务日志的大小时遇到了困难。过去，我尝试过用事务大小作为估算依据。但有时会比实际需求量小一半。请问如何估计出适当的大小？

**答：**并没有简单的公式，可以计算出最佳事务日志大小。遗憾的是，也很难阻止事务日志增长，除非关闭日志文件的自动增长选项。但我们不建议您关闭自动增长选项。

首先是底层 RAID 级别。涉及到性能与冗余性问题时，各种 RAID 级别的权衡互不相同。例如，仍能提供一定冗余性的最便宜的 RAID 配置为 RAID-5，但此配置只能用于处理单驱动器故障（除非采用 RAID-6 或配置了热备用驱动器），并且根据阵列中驱动器的数量，它有时会削弱大量写入工作负荷的性能。

事务日志应始终能够自动增长。尤其是在发生日志文件大小监视失败这种紧急状况时。例如，假设您收到 SQL Server 代理警报：Percent Log Used 性能计数器的数值高于 90%，但是通过电子邮件/寻呼机通知的紧急联系人休病假了。如果日志不能增长，当前正在运行的要修改数据库的所有事务都会停止并回滚。也就是说会停止处理工作负载。

然而，说没有简单的公式有一定的误导性。许多操作都会占用事务日志的空间。您可以根据这些操作的大小来估计事务日志大小需求。这些操作包括产生日常工作负载的操作，以及一些不常执行的操作，如数据库维护。所有这些都需要考虑在内。具体的操作包括：

  • 您的工作负载执行的最大单项插入/更新/删除事务（无论是影响数百万表行的单一语句隐式事务，还是执行多项操作的显式事务）。
  • 您的工作负载执行的最大批处理操作，例如 BULK INSERT。如果您正在采用 FULL 恢复模式，也许能通过采用 BULK_LOGGED 恢复模式来减小生成的事务日志量。当您采用 BULK_LOGGED 恢复模式对某些操作进行最低限度的记录时，可能会影响灾难恢复能力。请参阅我的博客文章“SQL Server DBA 每日一题：BULK_LOGGED 恢复模式”。
  • 最大群集索引的索引重建。您也可以在这种情况下采用 BULK_LOGGED。

对于所有这些操作，您不仅需要考虑事务日志量，还要考虑为事务日志管理系统保留一定的空间，以容纳适当的事务回滚。 如果某项事务生成 100MB 的事务日志记录，系统会在事务日志中保留大约 100MB 的空白空间，以保证能够中止事务并适当回滚。阻止数据库发生不一致是一种安全机制。因此，即便您认为自己为最大的事务留出了足够的空间，事务日志仍然可能会增长。

另一个要考虑的问题是，为什么事务日志记录必须保留在日志中，其原因何在？这可能会导致事务日志不断增长。部分原因可能如下：

  • 数据库采用的是 FULL 或 BULK_LOGGED 恢复模式，而不是执行事务日志备份（或者不定期执行备份）。您在丢弃日志记录之前必须将其备份。
  • 有一项运行时间超长的事务。在这种情况下，就不能丢弃这个长时间运行的事务从启动以来所生成的全部事务日志记录。
  • 正在进行数据库镜像，某些事务日志记录尚未从主体服务器发送到镜像服务器。在这些记录发送完毕之前，您不能丢弃它们。
  • 正在进行事务复制（或对等复制），日志读取器代理作业尚未处理完所有事务日志记录。

如果您发现事务日志在增长而不确定是什么原因引起的，请询问 SQL Server。发出以下查询：

SELECT [log_reuse_wait_desc] FROM sys.databases
WHERE [name] = 'dbmaint2008';
GO

查询的结果就是您不能丢弃某些日志记录并重用日志空间（称为“清除”或“截断”日志）的原因。

正如您所看到的，影响事务日志大小的因素有很多，如果将 tempdb 数据库考虑在内，影响因素还会更多。有关详细信息，请参阅我的一篇有关此主题的博客文章“控制适当事务日志大小的重要性”，以及发表在 TechNet 杂志 上的文章“了解 SQL Server 中的日志记录和恢复功能”。

日志记录强制性

**问：**请问为什么我无法使 SQL Server 操作不记录到日志中？我了解到表截断操作不会记录，那为什么不能设置为所有操作都不记录，以便 SQL Server 运行得更快？如果我不需要考虑灾难恢复的问题，可不可以这样设置？ 我特别希望能够忽略 tempdb 事务日志。

**答：**您了解到的关于 TRUNCATE TABLE 操作的传闻有误。所有数据库上进行的所有操作都会在一定程度上记录到日志中。 只不过诸如表截断之类的操作进行的是最低限度的记录。简单来说，最低限度记录操作是指仅记录数据文件页的分配和取消分配信息，而不记录对页上的表/索引记录进行的操作。这会加快操作速度，并意味着生成的事务日志较少，但仍会记录一些信息。

在所有恢复模式下，都会对表截断操作进行最低限度的记录。而仅在采用 SIMPLE 或 BULK_LOGGED 恢复模式时，才会对其他一些操作（例如索引构建/重建和批量加载）进行最低限度的记录。

在 SQL Server 中，真正不记录的只有那些会影响 tempdb 中的版本存储的操作，而版本存储用来支持快照隔离和联机索引操作等功能。不记录这些操作是因为永远不需要对版本存储操作进行回滚，也不需要对 tempdb 数据库执行崩溃恢复。

至此就看到了问题的关键所在。为什么必须记录 SQL Server 操作？因为 SQL Server 必须保证在发生故障时能够回滚操作。如果没有对于操作过程的描述（如事务日志记录），SQL Server 如何知道怎样回滚呢？只有在进行了记录的情况下才能完成回滚。

即便您不关心能否进行崩溃恢复，在数据库空间耗尽或出现损坏的扇区时，或者在查询耗尽内存时，SQL Server 也必须能够回滚操作。如果 SQL Server 无法回滚某项操作，数据库就会不可用，工作负载就会停止处理。

这同样也适用于 tempdb 数据库。基于同样的原因，尽管 tempdb 中的日志记录已经简化，数量已经减少，但您绝不能将记录全部删除。同时，SQL Server 还必须在每次发生崩溃之后能够执行崩溃恢复，以确保每个数据库的一致性。否则数据库将不可用。底线：在 SQL Server 中绝不能不对操作进行日志记录，我想这一点绝不会改变。

Paul Randal

Paul S. Randal是 SQLskills.com 的常务董事、Microsoft 区域总监和 SQL Server MVP。从 1999 年到 2007 年，他一直在 Microsoft 的 SQL Server 存储引擎团队工作。他曾编写过 DBCC CHECKDB/repair for SQL Server 2005，并在 SQL Server 2008 的开发过程中负责核心存储引擎部分的工作。Randal 是灾难恢复、高可用性和数据库维护方面的专家，经常在全球出席一些会议。您可以访问他的博客 SQLskills.com/blogs/paul，也可以通过 Twitter (Twitter.com/PaulRandal) 与他联系。

相关内容