优化和维护索引

优化 T-SQL 查询最常见的（和最有效的）方法是评估和调整索引策略。 正确编制索引的数据库执行较少的 IO 以返回查询结果，而 IO 较少，意味着 IO 和存储系统面临的压力会降低。 降低 IO 甚至可以提高内存利用率。 请记住查询的读/写比率。

繁重的写入工作负载可能表明，将行写入到额外索引的成本并没有带来太多好处。 但工作负载主要执行还需要进行查找操作的更新的情况例外。 执行查找的更新操作可受益于添加到现有索引的额外索引或列。 目标应该始终是充分利用表上最少数量的索引。

常见的性能优化方法如下：

• 使用 sys.dm_db_index_operational_stats 和 sys.dm_db_index_usage_stats 评估现有索引使用情况。

• 请考虑消除未使用的和重复的索引，但应谨慎执行此操作。 某些索引只能在每月/每季度/每年操作中使用，并且对这些过程可能很重要。 还可以考虑创建索引，以便在安排操作之前支持这些操作，以减少因表具有未使用的索引而产生的开销。

• 查看并评估来自查询存储或扩展事件捕获的耗费大量资源的查询，并手动创建索引以更好地处理这些查询。

• 在非生产环境中创建索引，并测试查询执行和性能，并观察性能更改。 请务必注意生产环境和非生产环境之间的任何硬件差异，因为内存量和 CPU 数量可能会影响执行计划。

• 仔细测试后，实现对生产系统所做的更改。

验证索引的列顺序，索引中的首个列会影响列统计信息，且一般决定优化器是否选择该索引。 理想情况下，前导列将是选择性的，可用于许多查询的 WHERE 子句中。 请考虑使用变更控制过程来跟踪可能影响应用程序性能的更改。 在删除索引之前，请将代码保存在源代码管理中，以便在不经常运行的查询需要索引正常运行的情况下，可以快速重新创建索引。

最后，用于相等比较的列应位于用于不相等比较的列前面，具有更大选择性的列应位于具有较少非重复值的列前面。

可恢复索引

使用可恢复索引可以暂停索引维护操作，也可以在时段内进行维护，稍后再恢复。 使用可恢复索引操作的一个很好的示例是，降低索引维护在繁忙的生产环境中的影响。 然后，可以在特定维护时段内执行重新生成操作，从而可以更好地控制进程。

此外，为大型表创建索引可能会对整个数据库系统的性能产生负面影响。 在 SQL Server 2019 之前的版本中修复此问题的唯一方法是终止索引创建过程。 然后，如果系统回滚会话，则必须从头开始启动该过程。

使用可恢复索引，可以暂停生成，然后在暂停时重启它。

以下示例显示了如何创建可恢复索引：

-- Creates a nonclustered index for the Customer table

CREATE INDEX IX_Customer_PersonID_ModifiedDate 
    ON Sales.Customer (PersonID, StoreID, TerritoryID, AccountNumber, ModifiedDate)
WITH (RESUMABLE=ON, ONLINE=ON)
GO

在查询窗口中，暂停索引操作：

ALTER INDEX IX_Customer_PersonID_ModifiedDate ON Sales.Customer PAUSE
GO

此语句使用 PAUSE 子句暂时停止创建可恢复联机索引。

可以通过查询 sys.index_resumable_operations 系统视图来检查可恢复联机索引的当前执行状态。