SQL Server数据库镜像下有效的索引维护

SQL Server数据库镜像下有效的索引维护

我们在做索引重建的时候，由于索引重建产生太多日志，习惯切换到大容量日志恢复模式。而在生产环境，我们配置了数据库镜像作为高可用，镜像只能运行于完整恢复模式，难道我们就不做索引维护了么？

当生产环境使用镜像的时候，这是一个很常见的问题。当使用镜像前，尽管很多人会做负载性能测试，以我的经验，在他们的测试中不会包含维护操作。常规的维护操作是业务环节的一部分，因此，我们应该将其包含在所有的测试场景中。

我们先来了解下原理，使用大容量日志恢复模式允许索引重建（使用ALTER INDEX ... REBUILD）操作产生更少的事务日志，也就是说，事务日志不会像在完整恢复模式下执行的操作那么大。而数据库镜像只允许使用完整恢复模式，因此索引重建会记录完整日志。产生的大量的额外的日志很容易导致主和镜像之间的网络问题，在主上会会导致非常大的SEND队列。如果SEND队列变得非常大，当主宕机时，会有潜在的数据丢失的隐患，甚至超出可以容忍的数据丢失服务级别协议。

那么我们该怎么做呢？

我们可以错开时间使用ALTER INDEX ... REORGANIZE做索引维护。重新组织索引只是处理存在的碎片，它是可以中断的，而不会丢失已经完成的工作。重建索引，不论碎片有多少，总是会建立一个新索引，如果你中断了，整个重建过程将回滚。

对于很大的索引，去重建是不实际的，应该执行以下步骤：

• 第1天：在维护窗口，开始ALTER INDEX ... REORGANIZE。让它运行1个小时，然后KILL掉。它不会回滚索引维护的操作，刚才的索引重新组织将会移除一些碎片。

• 第2天：再次开始重新组织索引。它不会记住第1天工作到哪了，它会快速穿过第1天的工作，开始移除索引的下一个部分的碎片。在1小时后KILL掉。

• 重复以上动作直到碎片级别降到合适的阈值，或者只是继续无限期的每天处理。

该方法允许你限制通过常规的索引维护产生的事务日志的数量。如果你想更深入的优化，不用再一定时间后杀掉重新组织的进程，你可以监控事务日志产生的数量，然后到达一定阈值后再杀掉。具体，可以参考：Script: open transactions with text and plans（http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/）