探索数据库维护检查

  • 7 分钟

查询优化器利用索引中的统计信息来尝试生成最佳执行计划。

在 Azure SQL 中，会为你进行维护任务（例如备份和完整性检查）处理，尽管你能够不通过自动更新来使统计信息保持最新状态，但有时还不够。

拥有正常的索引和统计信息可确保所有给定的计划都能以最佳效率执行。 应定期执行索引维护，因为数据库中的数据会随时间推移而变化。 可根据数据的修改频率更改索引维护策略。

重新生成和重新组织

当索引页内的逻辑顺序与物理排序不匹配时，将发生索引碎片。 例行执行数据修改语句（如 UPDATE、DELETE 和 INSERT）期间，页可能会乱序。 碎片可能会导致性能问题，因为需要额外的 I/O 来查找索引页中的指针所引用的数据。

在从索引中插入、更新和删除数据时，索引中的逻辑排序将不再与构成索引的页面内和页面之间的物理排序相匹配。 此外，随着时间的推移，数据修改可能导致数据在数据库中被分散或分段。 当数据库引擎需要读取额外页面以查找所需的数据时，这种分段可能会降低查询性能。

重新组织索引是一项联机操作，它会对（聚集和非聚集）索引的叶级别进行碎片整理。 此碎片整理过程将按从左至右的顺序对叶级页在物理上进行重新排序，以匹配节点的逻辑顺序。 在此过程中，还会根据配置的 fillfactor 值压缩索引页。

重新生成可以是联机操作，也可以是脱机操作，具体取决于所执行的命令或要使用的 SQL Server 版本。 脱机重新生成过程将删除索引本身，然后重新创建一个。 如果你可联机执行此操作，新索引将会与现有索引并行生成。 新索引生成后，现有索引将被删除，然后新索引会重命名以匹配旧索引名称。 请记住，联机版本将需要更多空间，因为新索引将与现有索引并行生成。

索引维护的常见指导如下：

  • > 5% 但 < 30% - 重新组织索引

  • >30% - 重新生成索引

使用这些数字作为一般建议。 根据你的工作负载和数据，你可能需要采取更积极的措施，或在某些情况下，对于主要执行查找特定页面的查询的数据库，你可以延迟它的索引维护。

SQL Server 和 Azure SQL 平台提供 DMV，使你能够检测对象中的碎片。 用于实现此目的的最常用 DMV 为：适用于 b 树索引的 sys.dm_db_index_physical_stats 和适用于列存储索引的 sys.dm_db_column_store_row_group_physical_stats。

需要注意的另一点是，索引重新生成会导致更新索引上的统计信息，这可以进一步帮助提高性能。 索引重新组织不更新统计信息。

从 SQL Server 2017 开始，Microsoft 引入了可恢复重新生成索引操作。 使用“可恢复重新生成索引操作”选项可以更灵活地控制重新生成操作可能对给定实例施加的时间。 在 SQL Server 2019 中，引入了控制相关最大并行度的功能，进一步为数据库管理员提供更精细的控制。

统计信息

在 Azure SQL 中执行性能优化时，了解统计信息的重要性至关重要。

统计信息以二进制大型对象 (blob) 形式存储在用户数据库中。 这些 blob 包含与数据值在表或索引视图的一列或多列中的分布有关的统计信息。

统计信息包含有关数据值在列中的分布的信息。 查询优化器使用列和索引统计信息来确定基数，基数是指查询预期返回的行数。

然后，查询优化器使用基数估算来生成执行计划。 基数估算还有助于优化器确定使用哪种类型的操作（例如索引查找或扫描）来检索所请求的数据。

若要查看上次更新日期的用户定义的统计信息的列表，请运行以下查询：

SELECT sp.stats_id, 
       name, 
       last_updated, 
       rows, 
       rows_sampled
FROM sys.stats
     CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1

创建统计信息

在 AUTO_CREATE_STATISTICS 选项为 ON 时，查询优化器会默认创建有关索引列的统计信息。 查询优化器还会为查询谓词中的单列创建统计信息。

这些方法为大多数查询提供高质量的查询计划。 有时，可能需要使用 CREATE STATISTICS 语句创建更多统计信息来改进特定的查询计划。

建议使 AUTO_CREATE_STATISTICS 选项保持启用状态，因为它允许查询优化器自动为查询谓词列创建统计信息。

每当遇到以下情况时，请考虑创建统计信息：

  • 数据库引擎优化顾问建议创建统计信息
  • 查询谓词包含尚不位于相同索引中的多个列
  • 查询从数据的子集中选择数据
  • 查询缺少统计信息

维护任务自动化

Azure SQL 提供本机工具来执行数据库维护任务以实现自动化目的。 可以使用不同的工具，具体取决于运行数据库的平台。

Azure 虚拟机上的 SQL Server

可以访问 SQL 代理或 Windows 任务计划程序等计划服务。 这些自动化工具有助于最大程度地减少索引中的碎片量。 对于较大的数据库，必须在索引的重新生成和重新组织之间找到平衡，以确保性能达到最佳。 SQL 代理或任务计划程序提供的灵活性使你能够运行自定义作业。

Azure SQL 数据库

由于 Azure SQL 数据库的性质，你无权访问 SQL Server 代理和 Windows 任务计划程序。 如果没有这些服务，则必须使用其他方法创建索引维护。 管理 SQL 数据库的维护操作的方法有三种：

  • Azure 自动化 Runbook

  • Azure 虚拟机中的 SQL Server 的 SQL 代理作业（远程调用）

  • Azure SQL 弹性作业

Azure SQL 托管实例

与 Azure 虚拟机上的 SQL Server 一样，你可通过 SQL Server 代理在 SQL 托管实例上计划作业。 使用 SQL Server 代理可以灵活地执行为减少数据库中索引内的碎片而设计的代码。