优化索引维护以提高查询性能并减少资源消耗

项目
11/19/2024

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Analytics Platform System (PDW) Microsoft Fabric SQL 数据库

本文可帮助你决定何时以及如何执行索引维护。它介绍了索引碎片和页面密度等概念，以及它们对查询性能和资源消耗的影响。它介绍了索引维护方法（即重新组织索引和重新生成索引），并推荐了一种可平衡潜在性能提升和维护所需的资源消耗的索引维护策略。

注意

本文不适用于 Azure Synapse Analytics 中的专用 SQL 池。有关 Azure Synapse Analytics 中专用 SQL 池的索引维护的信息，请参阅为 Azure Synapse Analytics 中的专用 SQL 池表编制索引。

概念：索引碎片和页面密度

什么是索引碎片以及它对性能有怎样的影响：

在 B 树（行存储）索引中，当索引包含的页中，索引中的逻辑排序（基于索引中的键值）与索引页的物理排序不匹配时，就存在碎片。

注意

文档在提到索引时一般使用 B 树这个术语。在行存储索引中，数据库引擎实现了 B+ 树。这不适用于列存储索引或内存优化表上的索引。有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。
无论何时对基础数据执行插入、更新或删除操作，数据库引擎都会自动修改索引。例如，在表中添加行可能会导致拆分行存储索引中的现有页，以便腾出空间来插入新行。随着时间的推移，这些修改可能会导致索引中的数据分散在数据库中（含有碎片）。
对于使用完全或范围索引扫描读取多个页面的查询，碎片多的索引会降低查询性能，因为读取数据可能需要额外的 I/O。查询可能需要大量的小型 I/O 请求来读取相同数量的数据，而不是使用少量的大型 I/O 请求。
存储子系统的顺序 I/O 性能优于随机 I/O 性能时，索引碎片可能会降低性能，因为读取碎片索引需要更多的随机 I/O。

什么是页面密度（也称为页面填充度）以及它对性能有怎样的影响：

数据库中的每个页面包含的行数可以变化。如果行占用了页面上的所有空间，则页面密度为 100%。如果页面是空白的，则页面密度为 0%。如果密度为 100% 的页面拆分为两个页面以容纳新行，则这两个新页面的密度约为 50%。
页面密度较低时，存储相同数量的数据需要更多的页面。这意味着，读写此数据需要更多的 I/O，缓存此数据需要更多的内存。内存有限时，所缓存的查询所需页面较少，从而导致磁盘 I/O 增加。因此，页面密度较低会降低性能。
当数据库引擎在索引创建、重新生成或重组期间向页面添加行时，如果索引的填充因子设置为 100 以外的值（或 0，在此上下文中等效），则不会完全填充页面。这会导致页面密度较低，同样会增加 I/O 开销，并降低性能。
页面密度较低可能会增加中间 B 树级别的数量。这会适度增加在索引扫描和索引查找中找到查找叶级别页面的 CPU 和 I/O 开销。
当查询优化器编译查询计划时，会考虑读取查询所需数据需要的 I/O 开销。页面密度较低时，需要读取的页面数量更多，因此 I/O 开销更高。这可能会影响对查询计划的选择。例如，页面密度因页面拆分而随时间降低时，优化器可能会使用不同的性能和资源消耗配置文件为同一查询编译其他计划。

提示

在许多工作负载中，提高页面密度会比减少碎片更能提升性能。

为避免在不必要的情况下降低页面密度，Microsoft 不建议将填充因子设置为 100 或 0 以外的值，除非索引遇到大量页面拆分，例如，包含非顺序 GUID 值的前导列并且频繁修改的索引。

度量索引碎片和页面密度

碎片和页面密度都是决定是否执行索引维护以及要使用哪种维护方法时要考虑的因素。

对于行存储和列存储索引的碎片定义有所不同。对于行存储索引，可以通过 sys.dm_db_index_physical_stats 确定特定索引、表或索引视图上的所有索引、某个数据库中的所有索引或所有数据库中的所有索引的碎片和页面密度。对于已分区索引，sys.dm_db_index_physical_stats() 会对每个分区提供此信息。

sys.dm_db_index_physical_stats 返回的结果集包含以下列：

列	说明
`avg_fragmentation_in_percent`	逻辑碎片（索引中的无序页面）。
`avg_page_space_used_in_percent`	平均页面密度。

对于列存储索引中的压缩行组，将碎片定义为已删除行数与总行数之比，并以百分比形式表示。可以通过 sys.dm_db_column_store_row_group_physical_stats 确定特定索引、表上的所有索引或数据库中所有索引的每个行组的总行数和已删除的行数。

sys.dm_db_column_store_row_group_physical_stats 返回的结果集包含以下列：

列	说明
`total_rows`	以物理方式存储在行组中的行数。对于压缩行组，这包括标记为已删除的行。
`deleted_rows`	以物理方式存储在压缩行组中且标记为要删除的行数。对于增量存储中的行组，值为 0。

可使用以下公式计算列存储索引中压缩行组的碎片：

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

提示

对于行存储索引和列存储索引，需要在删除或更新大量行后检查索引或堆碎片和页面密度。对于堆，如果频繁进行更新，需要定期检查碎片，避免前推记录激增。有关堆的详细信息，请参阅堆（没有聚集索引的表）。

若要获取用于确定碎片和页面密度的查询示例，请参阅示例。

索引维护方法：重新组织和重新生成

可以通过使用以下方法之一来减少索引碎片并增加页面密度：

重新组织索引
重新生成索引

注意

对于已分区索引，可以对索引的所有分区或单个分区使用以下方法之一。

重新组织索引

与重新生成索引相比，重新组织索引消耗的资源更少。因此，应首选此索引维护方法，除非出于特定原因需要使用索引重新生成。重新组织始终属于联机操作。也就是说，在执行 ALTER INDEX ... REORGANIZE 操作期间不保留长期对象级锁，且对基础表的查询或更新可以继续进行。

对于行存储索引，数据库引擎通过以物理方式重新排序叶级别页，以匹配叶节点逻辑顺序（从左到右），从而仅对表和视图中聚集索引和非聚集索引的叶级别进行碎片整理。重新组织还会压缩索引页，使页面密度等于索引的填充因子。若要查看填充因子设置，请使用 sys.indexes。有关语法示例，请参阅示例 - 行存储重新组织。
如果使用列存储索引，则在一段时间内插入、更新和删除数据后，增量存储可能最终会有多个小行组。重新组织列存储索引会强制增量存储行组移到列存储中的压缩行组，并将较小的压缩行组合并为较大的行组。重新组织操作还会以物理方式删除列存储标记为已删除的行。重新组织列存储索引可能需要额外的 CPU 资源来压缩数据。运行操作时，性能可能会变慢。但是，一旦压缩数据后，查询性能就会提高。有关语法示例，请参阅示例 - 列存储重新组织。

注意

从 SQL Server 2019 (15.x) 开始的 SQL Server、Azure SQL 数据库、Azure SQL 托管实例：元组移动器通过后台合并任务获得帮助，该任务会自动压缩较小的已存在一段时间（由内部阈值确定）的开放增量行组，或者合并已从中删除大量行的压缩行组。随着时间的推移，这会提高列存储索引的质量。在大多数情况下，此操作无需发出 ALTER INDEX ... REORGANIZE 命令。

提示

如果取消重新组织操作，或此操作因其他原因中断，则截至到该时刻它完成的进度将保留在数据库中。若要重新组织较大的索引，可以多次启动和停止该操作，直到此操作完成。

重新生成索引

重新生成索引将会删除并重新创建索引。重新生成操作可以脱机或联机执行，具体取决于索引类型和数据库引擎版本。脱机索引重新生成耗费的时间通常比联机重新生成少，但它会在重新生成操作持续期间保留对象级锁，阻止查询访问表或视图。

联机索引重新生成操作结束时才需要对象级锁，届时必须将锁短暂保留一段时间，重新生成才能完成。联机索引重新生成可以作为可恢复的操作启动，具体取决于数据库引擎的版本。可恢复的索引重新生成操作可以暂停，并保留截至到该时刻完成的进度。可恢复的重新生成操作可以在暂停或中断后恢复，也可在无需完成重新生成时中止。

有关 Transact-SQL 语法，请参阅 ALTER INDEX REBUILD。有关联机索引重新生成操作的详细信息，请参阅联机执行索引操作。

注意

索引执行联机重新生成时，对索引列中数据的每个修改都必须更新该索引的其他副本。在执行联机重新生成期间，这可能会导致数据修改语句的性能略微下降。

可恢复的联机索引操作暂停时，这会继续影响此性能，直到此可恢复的操作完成或中止。如果不打算完成可恢复的索引操作，请将其中止，而不要暂停它。

提示

根据可用资源和工作负载模式，在 ALTER INDEX REBUILD 语句中指定高于默认 MAXDOP 值的值，这样可能会缩短重新生成操作的持续时间，但会增加 CPU 消耗。

对于行存储索引，重新生成会消除索引的所有级别中的碎片，并根据指定的或当前的填充因子来压缩页面。如果指定 ALL，将通过单个操作删除表中的所有索引并重新生成它们。重新生成具有 128 或更多个区的索引时，数据库引擎会推迟页面取消分配，并获取相关联的锁，直到重新生成完成。有关语法示例，请参阅示例 - 行存储重新生成。
对于列存储索引，重新生成会消除碎片，将任何增量存储行移到列存储中，并以物理方式删除标记为已删除的行。有关语法示例，请参阅示例 - 列存储重新生成。

提示

自 SQL Server 2016 (13.x) 起，通常不需要重新生成列存储索引，因为 REORGANIZE 以联机操作形式执行重新生成的基本操作。

使用索引重新生成从数据损坏中恢复

在 SQL Server 2008 (10.0.x) 之前的版本中，有时可以重新生成行存储非聚集索引，更正索引数据损坏导致的不一致问题。

仍可以通过脱机重新生成非聚集索引，修复非聚集索引中的此类不一致问题。但是，你不能通过联机重新生成索引来纠正非聚集索引的不一致，因为联机重新生成机制会使用现有的非聚集索引作为重新生成的基础，因此仍存在不一致。脱机重新生成索引有时可以强制扫描聚集索引（或堆），进而将非聚集索引中的不一致数据替换为聚集索引或堆中的数据。

若要确保将聚集索引或堆用作数据源，请删除并重新创建非聚集索引，而不要重新生成它。与早期版本一样，可以通过从备份还原受影响的数据来从不一致中恢复。但是，可以通过脱机重新生成索引或重新创建索引来修复非聚集索引不一致问题。有关详细信息，请参阅 DBCC CHECKDB (Transact-SQL)。

自动索引和统计信息管理

利用自适应索引碎片整理等解决方案，自动管理一个或多个数据库的索引碎片和统计信息更新。此过程根据碎片级别以及其他参数，自动选择是重新生成索引还是重新组织索引，并使用线性阈值更新统计信息。

有关重新生成和重新组织行存储索引的注意事项

以下场景会导致自动在表上重新生成所有行存储非聚集索引：

在表上创建聚集索引，包括通过 CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON) 使用不同的键重新创建聚集索引
删除聚集索引，从而使表存储为堆

以下场景不会自动在同一个表上重新生成所有行存储非聚集索引：

重新生成聚集索引
更改聚集索引存储，例如应用分区方案或将聚集索引移到其他文件组

重要

如果索引所在的文件组处于脱机或只读状态，便无法重新组织或重新生成索引。如果指定了关键字 ALL，但有一个或多个索引位于脱机文件组或只读文件组中，该语句将失败。

当索引重新生成发生时，物理介质必须有足够的空间来存储索引的两个副本。在重新生成完成后，数据库引擎会删除原始索引。

如果使用 ALTER INDEX ... REORGANIZE 语句指定了 ALL，表上的聚集索引、非聚集索引和 XML 索引都会进行重新组织。

重新生成或重新组织小型行存储索引通常不会减少碎片。直至 SQL Server 2014 (12.x) 版本（包含该版本），SQL Server 数据库引擎都使用混合盘区分配空间。因此，小型索引的页面有时存储在混合区上，这会让这类索引隐式产生碎片。混合区最多可由八个对象共享，因此在重新组织或重新生成小索引之后可能不会减少小索引中的碎片。

有关重新生成列存储索引的注意事项

重新生成列存储索引时，数据库引擎会从原始列存储索引（包括增量存储）中读取所有数据。它将数据合并为新的行组，并将所有行组压缩到列存储中。数据库引擎通过以物理方式删除被标记为已删除的行，对列存储进行碎片整理。

注意

从 SQL Server 2019 (15.x) 开始，元组移动器通过后台合并任务获得帮助，该任务会自动压缩较小的已存在一段时间（由内部阈值确定）的开放增量存储行组，或者合并已从中删除大量行的压缩行组。随着时间的推移，这会提高列存储索引的质量。有关列存储术语和概念的详细信息，请参阅列存储索引：概述。

重新生成分区，而不是整个表

如果索引很大，则重新生成整个表将需要很长时间，并且在执行重新生成期间需要足够的磁盘空间来存储整个索引的额外副本。

对于已分区表，如果只有某些分区（例如，UPDATE、DELETE 或 MERGE 语句影响了其中许多行的分区）中存在碎片，则你不需要重新生成整个列存储索引。

在加载或修改数据后重新生成分区，这样可确保所有数据都存储在列存储中的压缩行组中。当数据加载进程使用行数少于 102,400 的批将数据插入到分区时，该分区的增量存储中最终可能会有多个开放的行组。重新生成会将所有增量存储行移到列存储中的压缩行组。

有关重新组织列存储索引的注意事项

重新组织列存储索引时，数据库引擎会将增量存储中的每个关闭的行组作为压缩行组压缩到列存储中。自 SQL Server 2016 (13.x) 起，在 Azure SQL 数据库中，REORGANIZE 命令会联机执行以下额外的碎片整理优化：

在逻辑删除了 10% 或更多行时从行组中物理移除行。例如，当包含 100 万行的压缩行组删除了 10 万行时，数据库引擎会移除已删除的行，并重新压缩包含 90 万行的行组，以减少存储使用量。
合并一个或多个压缩行组，以将每个行组的行增加到最多 1,048,576 行。例如，如果大容量插入 5 个分别具有 102,400 行的批，将获得 5 个压缩行组。如果你运行 REORGANIZE，这些行组将合并到一个具有 512,000 行的压缩行组中。这假定不存在任何字典大小或内存限制。
数据库引擎尝试将其中 10% 或更多的行被标记为已删除的行组与其他行组合并。例如，行组 1 已压缩且具有 500,000 行，而行组 21 未压缩且具有 1,048,576 行。行组 21 已将其 60% 的行标记为已删除，它还剩余 409,830 行。数据库引擎倾向于合并这两个行组来压缩为一个有 909,830 行的新行组。

在执行数据加载后，增量存储中可能会有多个小型行组。可以使用 ALTER INDEX REORGANIZE 强制这些行组移到列存储，然后将较小的压缩行组合并为较大的压缩行组。重新组织操作还将删除列存储标记为已删除的行。

注意

如果使用 Management Studio 重新组织列存储索引，会将压缩行组合并在一起，但不会强制将所有行组压缩到列存储中。将压缩关闭的行组，但不会将开放的行组压缩到列存储中。若要强制压缩所有行组，请使用包含 COMPRESS_ALL_ROW_GROUPS = ON 的 Transact-SQL 示例。

执行索引维护之前要考虑的事项

索引维护通过重新组织或重新生成索引实现，它需要消耗大量资源。这会导致 CPU 消耗、内存占用量和存储 I/O 大幅增加。但是，它带来的好处可能意义重大，也可能微不足道，具体取决于数据库工作负载和其他因素。

为了避免利用不必要的资源，请勿不加选择地执行索引维护。相反，应使用建议的策略根据经验确定每个工作负载可通过执行索引维护获得的性能提升，并将这些提升与获得它们需产生的资源成本和对工作负载的影响进行权衡。

当索引碎片较多或其页面密度较低时，更有可能认可重新组织或重新生成索引带来的性能提升。但是，这并不是要考虑的唯一因素。查询模式（事务处理与分析和报告）、存储子系统行为、可用内存和数据库引擎随时间的改进情况等因素都很重要。

重要

决定执行索引维护之前，应在每个工作负载的特定上下文下考虑多个因素，其中包括维护的资源成本。不应仅根据固定的碎片或页面密度阈值做出这些决策。

索引重新生成的积极影响

客户通常会发现，重新生成索引后性能得到提升。但在许多情况下，这些提升与碎片减少或页面密度增加无关。

索引重新生成具有一个重大的好处：它会通过扫描索引中的所有行来更新索引键列的统计信息。此操作等同于执行 UPDATE STATISTICS ... WITH FULLSCAN，与默认的采样式统计信息更新相比，它会让统计信息保持最新，有时它还会提高统计信息的质量。统计信息更新时，引用这些统计信息的查询计划将重新编译。如果由于统计信息陈旧、统计采样率不够高或者其他原因，导致以前的查询计划的性能不佳，那么重新编译的计划通常性能会更高。

客户通常会错误地将此提升归因于索引重新生成本身，将其视为碎片降低和页面密度增加的结果。实际上，通常通过更新统计信息能以更低的资源成本获得同样的提升，而不必重新生成索引。

提示

与索引重新生成相比，更新统计信息消耗的资源成本很少，并且此操作通常几分钟内便可完成。而索引重新生成可能需要数小时。

索引维护策略

Microsoft 建议客户考虑并采用以下索引维护策略：

不要假定索引维护始终会显著改进工作负载。
衡量重新组织或重新生成索引对工作负载的查询性能的特定影响。可通过查询存储使用 A/B 测试技术衡量“维护前”和“维护后”的性能。
如果发现重新生成索引会提高性能，请尝试将其替换为更新统计信息。这样可能会获得相似的提升。在这种情况下，可能不需要那么频繁地（或完全不需要）重新生成索引，而可以改为定期执行统计信息更新。若要获得某些统计信息，可能需要使用 WITH SAMPLE ... PERCENT 或 WITH FULLSCAN 子句提高采样率（这种情况并不常见）。
随着时间的推移监视索引碎片和页面密度，以确定这些值的升降与查询性能是否相关。如果增加碎片或降低页面密度导致性能下降到让人无法接受的程度，则重新组织或重新生成索引。通常，重新组织或重新生成性能下降的查询所使用的特定索引即可。这样就不必因维护数据库中每个索引产生更高的资源成本。
通过在碎片/页面密度和性能之间建立关联，你还可以确定维护索引的频率。不要假定必须按固定计划执行维护。更好的策略是监视碎片和页面密度，并在性能下降到不可接受的程度前根据需要运行索引维护。
如果已确定需要维护索引并且可接受其资源成本，则在资源使用率低的时段（如果存在这样的时段）内执行维护。
定期进行测试，因为资源使用模式可能会随时间而变化。

Azure SQL 数据库与 Azure SQL 托管实例中的索引维护

在 Azure SQL 数据库和 Azure SQL 托管实例中，除了上述注意事项和策略外，考虑索引维护的成本和好处也尤为重要。只有考虑了以下几点事项并证明需要执行此操作后，客户才应执行此操作。

Azure SQL 数据库和 Azure SQL 托管实例实施资源管理，以根据预配的定价层限制 CPU、内存和 I/O 消耗。这些限制适用于所有用户工作负载，包括索引维护。所有工作负载的累计资源消耗接近资源限制时，其他工作负载的性能可能会因重新生成或重新组织操作争用资源而下降。例如，由于并发的索引重新生成操作，事务日志 I/O 达到 100%，导致大批量数据加载速度变慢。在 Azure SQL 托管实例中，通过在具有有限资源分配的单独 Resource Governor 工作负荷组中运行索引维护，可以降低上述影响，但索引维护持续时间会延长。
为减少成本，客户通常会为数据库、弹性池和托管实例预配最少的资源空余空间。客户会选择足以满足应用程序工作负载需求的定价层。若要应对索引维护导致的资源使用量大幅增长同时不降低应用程序性能，客户可能需要预配更多资源并增加成本，但这不一定会提高应用程序性能。
在弹性池中，资源由池中的所有数据库共享。即使特定数据库处于空闲状态，在该数据库上执行索引维护也可能会影响同一个池中其他数据库内并发运行的应用程序工作负载。有关详细信息，请参阅密集弹性池中的资源管理。
对于 Azure SQL 数据库和 Azure SQL 托管实例使用的大多数类型的存储而言，顺序 I/O 和随机 I/O 之间没有性能差异。这会降低索引碎片对查询性能的影响。
如果在使用读取扩展或异地复制副本，则在主要副本上执行索引维护时，这些副本上的数据延迟通常会增加。如果为异地副本预配的资源量不足以支撑索引维护导致的事务日志生成增长，则异地副本可能会远远滞后于主副本，并导致系统重新设定其种子。这会导致此副本在重设种子完成前不可用。此外，在“高级”和“业务关键”服务层级中，用于实现高可用性的副本可能同样会在执行索引维护期间远远落后于主副本。如果在索引维护执行期间或完成不久后需要执行故障转移，则故障转移所需的时间可能会超出预期。
如果索引重新生成在主要副本上运行，并且一个长时间运行的查询同时在可读副本上执行，则查询可能会自动终止，防止阻塞副本上的重做线程。

在特定情况下，Azure SQL 数据库和 Azure SQL 托管实例可能需要执行一次性或定期的索引维护，但这种情况不常见：

增加数据库的页面密度并减少其已用空间大小，进而将其保持在定价层的大小限制范围之内。这样就无需扩展到大小限制更大的更高定价层。
如果需要收缩文件，则在收缩文件前重新生成或重新组织索引可增加页面密度。这样可提高收缩操作的运行速度，因为它需要移动的页面减少。有关更多信息，请访问：
- 管理 Azure SQL 数据库中的数据库的文件空间
- 管理 Azure SQL 托管实例中数据库的文件空间

提示

如果已确定 Azure SQL 数据库和 Azure SQL 托管实例工作负载需要执行索引维护，则应重新组织索引，或使用联机的重新生成索引操作。这样做时，查询工作负载可以在重新生成索引期间访问表。

此外，让此操作可恢复，这样就不必在计划或计划外的数据库故障转移中断此操作时重头开始重启该操作。当索引较大时，使用可恢复的索引操作尤为重要。

提示

脱机索引操作通常比联机操作更快完成。只有查询将无法在执行脱机索引操作期间访问表时（例如通过顺序 ETL 进程将数据加载到临时表后），才能使用此操作。

限制和局限

盘区超过 128 个的行存储索引通过两个单独的阶段重新生成：逻辑阶段和物理阶段。在逻辑阶段，将把由索引使用的现有分配单元标记为释放，对数据行进行复制并排序，然后将它们移到为存储重新生成的索引而创建的新分配单元。在物理阶段，先前标记为取消分配的分配单元在发生在后台的短事务中被物理删除，而且不需要很多锁。有关分配单元的详细信息，请参阅页和区体系结构指南。

ALTER INDEX REORGANIZE 语句要求包含索引的数据文件具有可用的空间，因为该操作仅可在同一文件中分配临时工作，而不能在同一个文件组内的另一个文件中进行分配。尽管文件组可能有可用空间，当数据文件空间不足时，用户仍会在执行重新组织操作期间遇到错误 1105：Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup。

当 ALLOW_PAGE_LOCKS 设置为 OFF 时，无法重新组织索引。

在 SQL Server 2017 (14.x) 及更低版本中，重新生成聚集列存储索引是一项脱机操作。执行重新生成操作时，数据库引擎必须获取表或分区上的排他锁。即使在使用 NOLOCK、读取已提交的快照隔离 (RCSI) 或快照隔离时，数据在重新生成期间仍处于脱机状态且不可用。自 SQL Server 2019 (15.x) 起，可以使用 ONLINE = ON 选项重新生成聚集列存储索引。

警告

对超过 1,000 个分区的表创建和重新生成非对齐索引是可能的，但不支持。这样做可能会导致性能下降，或在执行这些操作的过程中占用过多内存。 Microsoft 建议，当分区数超过 1,000 时，只使用对齐索引。

统计信息限制

创建或重新生成索引时，将通过扫描表中的所有行创建或更新统计信息，这等同于在 CREATE STATISTICS 或 UPDATE STATISTICS 中使用 FULLSCAN 子句。但是，从 SQL Server 2012 (11.x) 开始，当创建或重新生成已分区索引时，不会通过扫描表中的所有行来创建或更新统计信息。而会改为使用默认采样率。若要通过扫描表中所有行的方法创建或更新已分区索引上的统计信息，请使用 CREATE STATISTICS 或 UPDATE STATISTICS 以及 FULLSCAN 子句。
同样，当索引创建或索引重新生成操作可恢复时，将使用默认采样率来创建或更新统计信息。如果创建或上次更新统计信息时将 PERSIST_SAMPLE_PERCENT 子句设置为了 ON，则可恢复索引操作将使用所保留的采样率来创建或更新统计信息。
重新组织索引后，统计信息不会更新。

示例

使用 Transact-SQL 检查行存储索引的碎片和页面密度

下面的示例可确定当前数据库中所有行存储索引的平均碎片和页面密度。它通过 SAMPLED 模式快速返回可操作的结果。若要获得更准确的结果，请使用 DETAILED 模式。这需要扫描所有索引页面，并且可能需要很长时间。

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

上一语句返回如下的结果集：

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

有关详细信息，请参阅 sys.dm_db_index_physical_stats。

使用 Transact-SQL 检查列存储索引的碎片

下面的示例可确定当前数据库中具有压缩行组的所有列存储索引的平均碎片。

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

上一语句返回如下的结果集：

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

使用 SQL Server Management Studio 维护索引

重新组织或重新生成索引

在“对象资源管理器”中，展开包含要重新组织索引的表的数据库。
展开 “表” 文件夹。
展开要为其重新组织索引的表。
展开 “索引” 文件夹。
右键单击要重新组织的索引，然后选择 “重新组织”。
在“重新组织索引”对话框中，确认正确的索引位于“要重新组织的索引”网格中，然后选择“确定”。
选中 “压缩大型对象列数据” 复选框，以指定也压缩所有包含大型对象 (LOB) 数据的页。
选择“确定”。

重新组织表中的所有索引

在“对象资源管理器”中，展开包含你要重新组织索引的表的数据库。
展开 “表” 文件夹。
展开要为其重新组织索引的表。
右键单击 “索引” 文件夹，然后选择 “全部重新组织”。
在 “重新组织索引” 对话框中，确认正确的索引位于 “要重新组织的索引”中。若要从 “要重新组织的索引” 网格中删除索引，请选择该索引，再按 Delete 键。
选中 “压缩大型对象列数据” 复选框，以指定也压缩所有包含大型对象 (LOB) 数据的页。
选择“确定”。

使用 Transact-SQL 维护索引

注意

有关使用 Transact-SQL 重新生成或重新组织索引的更多示例，请参阅 ALTER INDEX 示例 - 行存储索引和 ALTER INDEX 示例 - 列存储索引。

重新组织索引

下面的示例重新组织 AdventureWorks2022 数据库中 HumanResources.Employee 表内的 IX_Employee_OrganizationalLevel_OrganizationalNode 索引。

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

下面的示例重新组织 AdventureWorksDW2022 数据库中 dbo.FactResellerSalesXL_CCI 表内的 IndFactResellerSalesXL_CCI 列存储索引。运行此命令以将所有关闭和打开的行组强制到列存储中。

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

重新组织表中的所有索引

下面的示例重新组织 AdventureWorks2022 数据库中 HumanResources.Employee 表内的所有索引。

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

重新生成索引

下面的示例在 AdventureWorks2022 数据库的 Employee 表中重新生成单个索引。

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

重新生成表中的所有索引

下面的示例使用 ALL 关键字重新生成所有与 AdventureWorks2022 数据库中的表关联的索引。其中指定了三个选项。

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

有关详细信息，请参阅 ALTER INDEX。

通过

优化索引维护以提高查询性能并减少资源消耗

概念：索引碎片和页面密度

度量索引碎片和页面密度

索引维护方法：重新组织和重新生成

重新组织索引

重新生成索引

使用索引重新生成从数据损坏中恢复

自动索引和统计信息管理

有关重新生成和重新组织行存储索引的注意事项

有关重新生成列存储索引的注意事项

重新生成分区，而不是整个表

有关重新组织列存储索引的注意事项

执行索引维护之前要考虑的事项

索引重新生成的积极影响

索引维护策略

Azure SQL 数据库与 Azure SQL 托管实例中的索引维护

限制和局限

统计信息限制

示例

使用 Transact-SQL 检查行存储索引的碎片和页面密度

使用 Transact-SQL 检查列存储索引的碎片

使用 SQL Server Management Studio 维护索引

重新组织或重新生成索引

重新组织表中的所有索引

使用 Transact-SQL 维护索引

重新组织索引

重新组织表中的所有索引

重新生成索引

重新生成表中的所有索引

反馈

其他资源

通过

优化索引维护以提高查询性能并减少资源消耗

概念：索引碎片和页面密度

度量索引碎片和页面密度

索引维护方法：重新组织和重新生成

重新组织索引

重新生成索引

使用索引重新生成从数据损坏中恢复

自动索引和统计信息管理

有关重新生成和重新组织行存储索引的注意事项

有关重新生成列存储索引的注意事项

重新生成分区，而不是整个表

有关重新组织列存储索引的注意事项

执行索引维护之前要考虑的事项

索引重新生成的积极影响

索引维护策略

Azure SQL 数据库与 Azure SQL 托管实例中的索引维护

限制和局限

统计信息限制

示例

使用 Transact-SQL 检查行存储索引的碎片和页面密度

使用 Transact-SQL 检查列存储索引的碎片

使用 SQL Server Management Studio 维护索引

重新组织或重新生成索引

重新组织表中的所有索引

使用 Transact-SQL 维护索引

重新组织索引

重新组织表中的所有索引

重新生成索引

重新生成表中的所有索引

相关内容

反馈

其他资源