?

最后一个重点要记住的是ALTER INDEX REBUILD操作是在一个单独事务里。如果索引很大，事务的执行时间会很长，在期间，这会阻止日志里的空间重用。这就是说，即使你在简单模式里重建索引，你也要想到自检查点（CHECKPOINT）操作后日志应该保持很小，重建是最小化日志，在剧烈的重建期间，日志文件还会快速扩展。

?

索引重组

?

和重建索引相比，使用ALTER INDEX REORGANIZE或者SQL Server里的DBCC INDEXDEFRAG(已废弃)重组（碎片整理）索引都是完整记录操作，不管是任何恢复模式，因此实际的页修改总被记录。但是，通常索引重组比索引重建需要更少的日志空间，尽管这是索引里降低碎片的一个功能；比起轻度碎片，重度碎片索引会需要更多的日志空间来重组。

?

另外，ALTER INDEX REORGANIZE操作是通过多个更短的事务完成的。因此，当与定期的日志备份相结合（或在简单恢复模式里）时，在此操作期间，日志空间可以被重用，因此要求操作期间日志空间最小化。

?

例如，对于重建操作，重建20GB的索引会需要超过20GB的空间，因为它发生在一个单独的事务里。但是，重组20GB的索引会需要更少的空间，因为在重组里每个页分配修改是个单独的事务，因此日志记录可以用定期日志备份截断，让日式空间可以重用。

?

控制日志过度措施

?

如果你的组织对任何潜在数据丢失不能容忍的，那么你没有选择，只能让所有的数据库运行在完整恢复模式里，并且妥当计划你的日志大小和增长。因此索引重建是作为一个单独线程发生的，日志至少会和你重建的索引一样的大小。如刚才所说，索引重组会需要更少的空间，且允许在操作期间通过日志备份来截断日志。这样的话，为了同时避免日志暴涨，可行的话，你可以用日志重组。

?

如果你的SLA和操作级别协议（Operational Level Agreements（OLAs））允许一些潜在的数据丢失，那么在索引重建前l切换到大容量日志恢复模式可以为重建索引最小化空间需求量。但是，要在最小化数据丢失的方式下进行，例如已经讨论确认过了。

?

不管使用的恢复模式，你可以通过重组索引而不是重建索引来在日志上最小化索引维护操作的影响。可以的话，可以看下微软的指导方针，为了最小化索引维护操作的影响，对于绝大数情况，并不是所有情况，决定什么时候进行索引重建，什么时候进行索引重组（查看索引重组和重建）。他们也声明：对于碎片级别大于5%且小于30%，你应该重组索引，对于碎片级别大于30%，你应该重建它。

?

但是，在索引维护期间，在保护日志过度增长里，最有效的武器是维护那些真正需要的索引。使用SSMS维护计划向导，索引维护是个孤注一掷的操作：要么重建（或重组）数据库里（维护计划里的所有数据库）的所有索引，要么全不维护。一个更好的方法是使用sys.dm_db_index_physical_stats的DMV来看下碎片程度根据需要来决定索引重建/重组策略。

?

Ola Hallengren的免费维护脚本

?

Ola Hallengren提供一个综合的免费维护工具，它展示了如何使用sys.dm_db_index_physical_stats进行索引分析来进行智能维护，它可以用来代替SSMS里向导创建的数据库维护计划（https://ola.hallengren.com/）。

?

但是最好的方法，是计划只维护那些可以在查询上提供真正持久影响的索引。逻辑碎片（在乱序中的索引页）挫败了SQL Server的预读机制（https://msdn.microsoft.com/zh-cn/library/ms191475%28v=sql.105%29.aspx），且使在磁盘上读取连续页I/O-效率更低。但是，这只真正影响从磁盘的大范围扫描。即使对非常大碎片的索引，如果你不扫描表，重建或重组索引不会提高性能。降低页深度（通过页分裂或删除造成的很多缺口）会带来更多的页占用磁盘空间，且在内存里，会需要更多的I/O带宽来传输数据。再说一次，这个碎片格式不会真正影响不频繁修改的索引，因此重建它们不会有帮助。

?

计划索引维护前，问下自己什么性能标准从维护受益？它会大会减少I/O？它会提高你最昂贵查询的多少性能？它是持久正面影响么？如果这些答案是“否”或“不知道”，那么定期索引维护可能不是个长远的答案。最后，值得注意的是对小索引维护是不值得的。通常引用的阈值是近1000页。在微软，当Paul Randal管理存储引擎开发团队时，建议这些值作为参考，在在线帮助里记录了。注意，尽管这只是个建议并不对所有环境合适，如Paul在他的博客文章里谈到的：“在线帮助的索引碎片阈值来自哪里？”

?

调查重日志写入事务

?

sys.dm_tran_database_transactions的DMV提供在事务日志上事务活动影响的有用内部信息。在他们的书里，《使用SQL Server动态管理视图进行性能调优》，得到他们的允许后，复制在这里，作者Louis Davidson和Tim Ford，演示了如何使用这个DMV和一些其他的，来调查可能造成事务日志过度增长的事务。

?

在代码7.1里的例子重用来自第6篇的FullRecovery数据库和PrimaryTable_Large表。在一个显性事务里，它重建了聚集索引然后调查日志增长。

?

?1 USE FullRecovery

?2 GO

?3 BEGIN TRANSACTION?

?4?

?5 ALTER INDEX ALL ON dbo.PrimaryTable_Large REBUILD

?6?

?7 SELECT DTST.[session_id],?

?8 ?DES.[login_name] AS [Login Name],?

?9 ?DB_NAME (DTDT.database_id) AS [Database],?

10 ?DTDT.[database_transaction_begin_time] AS [Begin Time],?

11 ?DATEDIFF(ms, DTDT.[database_transaction_begin_time], GETDATE())

12 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? AS [Duration ms] ,

13 ?CASE DTAT.transaction_type?

14 ? ?WHEN 1 THEN 'Read/write'?

15 ? ? WHEN 2 THEN 'Read-only'?

16 ? ? WHEN 3 THEN 'System'?

17 ? ? WHEN 4 THEN 'Distributed'?

18 ? END AS [Transaction Type],?

19 ? CASE DTAT.transaction_state?

20 ? ? WHEN 0 THEN 'Not fully initialized'?