我们的SQL Server数据库索引需要多久重建一次？

Question

目前我们的数据库大小为10 GB，每个月增长大约3 GB。经常听说应该定期重建索引以改善查询执行时间。那么在这种情况下，我应该多久重建一次索引呢？

- HotTester

Answer 1

普遍认为，当索引碎片化达到5%（有时是10%）以上时，应该重新组织（“碎片整理”）索引，并在碎片化率超过30%时完全重建它们（至少这是我听到的很多地方提倡的数字）。

Michelle Ufford（又名“SQL Fool”）有一个自动索引碎片整理脚本，它使用这些确切的限制来决定何时重新组织或重建索引。

此外，Brad McGehee关于重建索引的提示提供了一些有关如何处理索引重建的良好思路和技巧。

---

我在这里使用这个脚本（不记得从哪里得到的 - 谁给了我：非常感谢！真的很有帮助）来显示给定数据库中所有索引的索引碎片情况：

SELECT 
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count
    

- marc_s

---

Brad McGhee的技巧链接已经失效。也许这是正确的链接，http://www.sql-server-performance.com/2007/rebuilding-indexes/。 - Jonathon Watney

还有 Ola Hallengren的SQL Server维护解决方案，其中包括统计信息和索引管理过程。默认情况下，它也采用5%重组/30%重建。 - Bacon Bits

Answer 2

“需要”和“能够”时机的选择！

例如...

• 首先测试碎片化情况，然后决定是不做任何操作、重新组织还是重建索引。例如，SQL Fool的脚本具有@minFragmentation和@rebuildThreshold参数。

• 每天执行统计，但在周末执行索引维护。你的维护窗口是什么时间？

- gbn

Answer 3

您应该经常重建索引，以使生产不受索引退化的影响。我知道这听起来很模糊，但所有数据库都是不同的，并且以不同的方式使用。您只需要定期重建/碎片整理写入操作（插入/更新）的索引 - 您的静态或大多数只读表不需要太多重新索引。

您需要使用 dbcc showcontig([Table]) 来检查索引的碎片程度，确定它们变得碎片化的频率以及碎片化的程度。

当索引过于碎片化（超过20％-30％左右）时，请使用 dbcc dbreindex([Table]) 进行完全重建索引，但如果您找不到足够长的停机时间窗口并且碎片化水平相对较低（1％-25％），则应使用dbcc indexdefrag([Database], [Table], [Index]) 以“在线”方式碎片整理索引。还要记住，您可以停止索引碎片整理操作，并在以后的某个时间重新开始而不会丢失任何工作。

保持数据库及其索引的“协调”需要一些监视才能真正了解何时以及何时重新索引。

- NTDLS

---

我承认自己是一个老派的 SQL 程序员。请留下一些细节... 启发我。 - NTDLS

因为它们从SQL Server 2005开始被标记为弃用，并将在未来的版本中被删除。这里只有两个URL提到了这一点：http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60121.aspx和http://www.mssqltips.com/tip.asp?tip=1352，这是官方的智慧之言：http://msdn.microsoft.com/en-us/library/ms143729.aspx。 - Frank Kalis

我必须说，sys.dm_db_index_physical_stats比showcontig要好得多。谢谢你的信息！ - NTDLS

Answer 4

考虑到您的数据库大小，每月重建一次索引是很容易的。但随着大小的增加，比如达到500GB左右，您可以每月重建两次。

- Coder

---

当然，这取决于打开的事务数量、它们的类型和生命周期。这可能是一个非常活跃的10GB数据库。 - NTDLS

Answer 5

在Bacon Bits comment和Ola Hallengren's SQL Server Maintenance Solution中提到，IndexOptimize支持SQL Server 2008、SQL Server 2008 R2、SQL Server 2012、SQL Server 2014、SQL Server 2016、SQL Server 2017、SQL Server 2019、Azure SQL Database和Azure SQL Database Managed Instance。
它在https://github.com/olahallengren/sql-server-maintenance-solution上有2K颗星。

Michelle Ufford （又名“SQL Fool”）所提出的自动索引碎片整理脚本 ，建议采纳被接受的答案，从概念上看似乎与Ola Hallengren的SQL Server维护解决方案相同，但最新版本是2011年。

- Michael Freidgeim