sys.dm_db_missing_index_details (Transact-SQL)

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

返回有关缺失索引的详细信息。

在 Azure SQL 数据库中，动态管理视图不能公开将影响数据库包含的信息，也不能公开有关用户可以访问的其他数据库的信息。 为了避免暴露此信息，包含不属于所连接租户的数据的每行都会被筛选出。

列名称 数据类型 描述
index_handle int 标识特定的缺失索引。 该标识符在服务器中是唯一的。 index_handle 是此表的键。
database_id smallint 标识带有缺失索引的表所驻留的数据库。

在 Azure SQL 数据库中，这些值在单一数据库或弹性池中是唯一的，但在逻辑服务器中不是唯一的。
object_id int 标识索引缺失的表。
equality_columns nvarchar(4000) 构成相等谓词的列的逗号分隔列表，谓词的形式如下：

table.column = constant_value
inequality_columns nvarchar(4000) 构成不等谓词的列的逗号分隔列表，例如以下形式的谓词：

table.column>constant_value

“=”之外的任何比较运算符都表示不相等。
included_columns nvarchar(4000) 用于查询的涵盖列的逗号分隔列表。 有关涵盖列或包含列的详细信息，请参阅 创建包含列的索引。

对于内存优化索引（哈希和内存优化的非聚集索引），请忽略 included_columns。 每个内存优化索引中均包含表的所有列。
语句 nvarchar(4000) 索引缺失的表的名称。

备注

查询优化器优化查询时， sys.dm_db_missing_index_details 返回的信息会更新，并且不会持久保存。 只有在重启数据库引擎之前，才会保留缺少索引信息。 如果数据库管理员要在服务器回收后保留缺失索引信息，则应定期制作缺失索引信息的备份副本。 使用 sys.dm_os_sys_info 中的 sqlserver_start_time 列查找上次数据库引擎启动时间。

若要确定特定缺失索引所属的缺失索引组，可以通过根据index_handle列将索引与动态管理视图相等来查询sys.dm_db_missing_index_groups动态sys.dm_db_missing_index_details管理视图。

注意

此 DMV 的结果集限制为 600 行。 每行包含一个缺失的索引。 如果缺少的索引超过 600 个，则应解决现有的缺失索引，以便查看较新的索引。

在 CREATE INDEX 语句中使用缺少索引信息

若要将返回 sys.dm_db_missing_index_details 的信息转换为内存优化索引和基于磁盘的索引的 CREATE INDEX 语句，应将相等列放在不相等列之前，并且它们应一起创建索引键。 应该使用 INCLUDE 子句将包含列添加到 CREATE INDEX 语句。 若要确定相等列的有效顺序，请基于其选择性排序：首先列出选择性最强的列（列列表中的最左侧）。 在缺少索引建议的 Tune 非聚集索引中了解详细信息，包括缺少索引功能的限制。

有关内存优化索引的详细信息，请参阅 内存优化表的索引。

事务一致性

如果事务创建或删除了一个表，则包含有关已删除对象的缺失索引信息的行将从此动态管理对象中删除，以保持事务一致性。 详细了解 缺少索引功能的限制。

权限

对于 SQL Server 和 SQL 托管实例，需要 VIEW SERVER STATE 权限。

在SQL 数据库基本、S0 和 S1 服务目标以及弹性池中的数据库、服务器管理员帐户、Microsoft Entra 管理员帐户或服务器角色的成员##MS_ServerStateReader##身份是必需的。 对于所有其他 SQL 数据库服务目标，需要数据库的 VIEW DATABASE STATE 权限或 ##MS_ServerStateReader## 服务器角色中的成员身份。

SQL Server 2022 及更高版本的权限

要求对服务器具有 VIEW SERVER PERFORMANCE STATE 权限。

示例

以下示例返回当前数据库的缺少索引建议。 如果可能，应将缺少的索引建议与当前数据库中的现有索引组合在一起。 了解如何在缺少索引建议的优化非聚集索引中应用这些建议。

SELECT
  CONVERT (varchar(30), getdate(), 126) AS runtime,  mig.index_group_handle,  mid.index_handle,
  CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns, '') + CASE
    WHEN mid.equality_columns IS NOT NULL
    AND mid.inequality_columns IS NOT NULL THEN ','
    ELSE ''
  END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
	INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

注意

Microsoft 的 Tiger Toolbox 中的 Index-Creation 脚本会检查缺失索引 DMV，并自动删除所有多余的建议索引，解析出影响较小的索引，并生成索引创建脚本供你查看。 与上面的查询一样，它不会执行索引创建命令。 Index-Creation 脚本适用于 SQL Server 和 Azure SQL 托管实例。 对于 Azure SQL 数据库，请考虑实现自动索引优化。

后续步骤

在以下文章中详细了解缺少的索引功能：