SQL问题与解答：维护日志和索引

发布时间：2010-11-26 14:17 来源：未知

切勿破坏这个结构链

问：我已经为数据库定义了备份策略。 我的计划涉及事务日志备份，这样我们执行灾难恢复时几乎不会丢失数据。 我研究了可能会遇到的一些问题，并几次读到需要注意不能破坏日志备份链。 您能解释一下这是什么意思以及在何种情况下会破坏它吗？

答：问得好，许多人都忽略了这个问题。 日志备份链（有时简称为日志链）指的是一系列不间断的事务日志备份，覆盖的时间段从最近的数据备份（完整备份或差异备份）到要还原该备份时。 还原序列的示例如下：

最近的完整数据库备份 然后是最近的差异数据库备份 最后是所有事务日志备份

大多数人会保留更多事务日志备份，一旦某个备份被破坏，您必须还原最近的数据备份。 在我去年撰写的两篇《TechNet 杂志》文章“了解 SQL Server 备份”和“利用备份进行灾难恢复”中，可获得有关备份和还原的更多信息。

假如任何必要的日志备份被损坏或不能按照所选序列还原，则日志备份链会被破坏并且无法还原被破坏的时间之前的备份。 假如只损坏了其中一个日志备份，您可能会使用 WITH CONTINUE_AFTER_ERROR 选项强制其进行还原。 这会强制还原已损坏的事务日志记录，从而导致数据库损坏。 我对是否强制进行此类还原也拿不定主意。

可能会导致必要的日志备份不可用的一项操作是“带外”日志备份，该操作不能确保会保留日志备份。 例如，您可能通过这种方式向开发人员提供副本。 该日志备份是日志备份链的一部分，因为它是唯一包含在前一个日志备份后生成的日志记录的日志备份。

也就是说，除非您使用 WITH COPY_ONLY 选项执行日志备份，并允许下一个 日志备份有效地备份同一组日志记录才行。 请参见我的博客文章“BACKUP WITH COPY_ONLY”，了解有关如何避免损坏备份链的详细信息。

损坏日志备份链的操作的一个更常见示例是阻止您在日常操作期间执行事务日志备份。 这些类型的操作包括：

切换到 SIMPLE 恢复模式，然后返回 FULL 或 BULK_LOGGED 使用 BACKUP LOG … WITH NO_LOG 或 TRUNCATE_ONLY 选项将日志转储到 SQL Server 2005 和早期版本中 从数据库快照还原数据库

您需要在上述任何操作后执行数据备份（完整备份或差异备份）以允许日志备份继续。 这称为“重新启动日志备份链”。

最后一件事：与一般认识相反，执行完整备份或差异备份不会 损坏日志备份链，事实上，不会对日志备份产生任何影响。

群集这些索引

问：SQL Server 2008 数据库中的许多表不包含群集索引。 我听说使用导致额外 IO 的前推记录时，可能遇到性能问题。 您是否能告诉我如何检查此问题以及应该采取什么措施？

答：堆是一个不包含群集索引的表。 它在本质上是无序的。 不了解堆中的前推记录及其用法的读者，请参见我的博客文章“转发和前推记录以及后向指针大小”了解详细信息。 在查询处理期间，堆中的前推记录可能会导致额外的随机 IO 操作，而这会导致性能下降。

检查您的查询是否正在处理前推记录的最简便方法是查看访问方法性能对象中的每秒前推记录性能计数器。 然后，对数据库中的一些表使用带有 DETAILED 模式的 sys.dm_db_index_physical_stats 动态管理函数，该函数将在输出的 forwarded_record_count 列中返回每个表的前推记录数。 有关详细信息，请参阅联机丛书中的此主题。

删除前推记录的最糟糕的方法是创建群集索引，然后再删除该索引。 这会导致表中的所有非群集索引自动重新生成两次，浪费大量资源。 有关详细信息，请参阅我的博客文章：“表结构更改时，非群集索引会怎样？”

永久删除和阻止堆中的前推记录的最简便方法是创建群集索引。 在此，我不希望讨论为什么在大多数情况下应当使用群集索引而不是堆，从而陷入“群集索引与堆”的争论 。 有关此问题的详细信息，请参阅我妻子 Kimberly Tripp 的“群集键”博客文章系列。 我鼓励您使用群集索引进行评估。

表记录大小增加时，假如空间不足，会引起前推记录。 因此，阻止前推记录的另一个方法是防止更改记录大小。 例如，这意味着使用可变长度列的默认值。

在 SQL Server 2008 中，有一个新的 ALTER TABLE … REBUILD 语句允许您重新生成堆。 此语句的工作原理与允许您重新生成索引的 ALTER INDEX … REBUILD 语句的工作原理相同。 Microsoft 添加此语句是为了支持数据压缩功能，但这里我们将其用于其他目的。 有关详细信息，请参阅联机丛书中的此主题。

索引维护

问：我已将索引维护例程更改为使用联机索引重新生成，但有时在维护例程运行时仍会出现阻塞问题。 为什么会这样？ 我认为联机索引操作不使用锁定，所以不应该出现任何阻塞才对。 这是正常现象，还是我的操作有问题？

答：您看到的是正常现象。 在操作开始时有一个必需的共享表锁定，同时操作初始化（这个过程非常快）。 此过程转瞬即逝。 此锁定的排队方式必须与任何其他锁定类似，并且它将阻止任何新查询对该表进行修改，直到您再次同意并释放此锁定。

在您完成所有当前正在运行的修改查询后，才可以获取此锁定。 这可能需要相当长一段时间，具体取决于您的工作负荷。 这意味着在联机索引操作开始时可能会出现阻塞。

在操作结束时，必须对锁定进行架构修改，将该锁定视为独占锁定以完成修改。 此过程同样相当快。 然后立即解除独占该锁定。 此锁定将阻止对表进行任何类型的新查询（读取或写入），直到您同意并释放此锁定。

再次重申，在 SQL 完成所有当前正在运行的读取或写入查询后，才可以获取此锁定。 这同样意味着可能出现阻塞。

总而言之，尽管此功能名为联机索引操作，但它的确还需要两个可能导致阻塞问题的短期锁定。 与传统的脱机索引操作相比，这种方法的优点在于绝大多数索引操作没有使用锁定，因此整体并发程度有所提高。 “SQL Server 2005 中的联机索引操作”白皮书对这些操作的工作原理进行了更为详细的介绍。

缩短索引维护时间

问：我继承了一些系统，在这些系统中，常规索引维护操作的运行时间很长并生成大量 IO，但因为索引不能是零碎的，所以我没有重新生成任何索引。 我希望减少工作负荷，因为性能没有得到任何改进。 您能为我建议一个有帮助的策略吗？

答： 这个问题普遍存在。 原因在于维护索引操作决定要重新生成或重新组织哪些索引的方式。

大多数人针对数据库中的所有索引运行 sys.dm_db_index_physical_stats 动态管理函数（以前提到过），然后选择是重新生成、重新组织还是不执行任何操作。 他们使用输出中的 WHERE 子句根据 avg_fragmentation_in_percent、page_count 和 avg_page_space_used_in_percent 值做出决定。

问题是索引碎片不像其他统计信息一样存储在内存中。 此函数必须读取并处理各个索引才能确定其零碎程度。 假如数据库中的大多数索引一成不变或缓慢变化（就碎片来说），将不会重新生成或重新组织这些索引。 每次执行维护索引操作时检查它们的碎片根本就是在浪费时间。

大多数动态管理视图支持“谓词推送”，即只处理与 WHERE 子句中的谓词匹配的数据。 但是，sys.dm_db_index_physical_stats 是一个函数而不是视图，因此它无法做到这一点。 这就意味着您必须手动筛选函数，并要求函数只处理您知道可能成为碎片且可能需要重新生成或重新组织的那些索引。

我建议监视几周内出现的碎片。 这样您就可以了解哪些索引需要检查碎片，而不用检查所有索引。 有了这些索引的列表后，创建一个包含表名称、索引名称和碎片阈值的表以便采取措施。 您可能会发现，一些索引包含较多的碎片才会影响性能，而另一些则不然。 这将是您稍后用来推动索引维护操作的“驱动力表”。 系统将循环处理该表中介绍的所有索引，并只对它们运行 sys.dm_db_index_physical_stats 函数。

我已对多个客户应用了此方法。 在某些情况下，索引维护操作的运行时间会从几小时缩短到 15 分钟或更短。 这完全是因为没有对静态索引运行此函数。 您还可以进一步跟踪索引重新生成以及可能自动更改索引的 FILLFACTOR 设置的频率，希望这可以进一步减少索引维护操作所执行的工作。

有关执行索引维护的各种方法的详细信息，请参阅我的博客文章“索引维护的重要性”，有关函数实际操作的详细说明，另请参阅我的博客文章“深入了解 sys.dm_db_index_physical_stats”。