sys.dm_db_missing_index_details (Transact-SQL)sys.dm_db_missing_index_details (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

返回有关缺失索引的详细信息,不包括空间索引。Returns detailed information about missing indexes, excluding spatial indexes.

Azure SQL 数据库Azure SQL Database 中,动态管理视图不能公开将影响数据库包含的信息,也不能公开有关用户可以访问的其他数据库的信息。In Azure SQL 数据库Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. 为了避免公开此信息,每个包含不属于所连接的租户的数据的行都将被筛选掉。To avoid exposing this information, every row that contains data that doesn't belong to the connected tenant is filtered out.

列名称Column name 数据类型Data type 说明Description
index_handleindex_handle intint 标识特定的缺失索引。Identifies a particular missing index. 该标识符在服务器中是唯一的。The identifier is unique across the server. index_handle 是此表的键。index_handle is the key of this table.
database_iddatabase_id smallintsmallint 标识带有缺失索引的表所驻留的数据库。Identifies the database where the table with the missing index resides.
object_idobject_id intint 标识索引缺失的表。Identifies the table where the index is missing.
equality_columnsequality_columns nvarchar(4000)nvarchar(4000) 构成相等谓词的列的逗号分隔列表,谓词的形式如下:Comma-separated list of columns that contribute to equality predicates of the form:

表列 =constant_valuetable.column =constant_value
inequality_columnsinequality_columns nvarchar(4000)nvarchar(4000) 构成不等谓词的列的逗号分隔列表,例如以下形式的谓词:Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form:

表列 > constant_valuetable.column > constant_value

“=”之外的任何比较运算符都表示不相等。Any comparison operator other than "=" expresses inequality.
included_columnsincluded_columns nvarchar(4000)nvarchar(4000) 用于查询的涵盖列的逗号分隔列表。Comma-separated list of columns needed as covering columns for the query. 有关覆盖列或包含列的详细信息,请参阅 创建包含列的索引For more information about covering or included columns, see Create Indexes with Included Columns.

对于内存优化索引 (哈希和内存优化的非聚集) ,请忽略 included_columnsFor memory-optimized indexes (both hash and memory-optimized nonclustered), ignore included_columns. 每个内存优化索引中均包含表的所有列。All columns of the table are included in every memory-optimized index.
语句statement nvarchar(4000)nvarchar(4000) 索引缺失的表的名称。Name of the table where the index is missing.

备注Remarks

sys.dm_db_missing_index_details 返回的信息会在查询优化器优化查询时更新,因而不是持久化的。Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. 缺失索引信息只保留到重新启动 SQL ServerSQL Server 前。Missing index information is kept only until SQL ServerSQL Server is restarted. 如果数据库管理员要在服务器回收后保留缺失索引信息,则应定期制作缺失索引信息的备份副本。Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling.

若要确定特定缺失索引所属的缺失索引组,可以查询 sys.dm_db_missing_index_groups 动态管理视图,方法是基于 index_handle 列将该视图与 sys.dm_db_missing_index_details 同等联接。To determine which missing index groups a particular missing index is part of, you can query the sys.dm_db_missing_index_groups dynamic management view by equijoining it with sys.dm_db_missing_index_details based on the index_handle column.

备注

此 DMV 的结果集限制为600行。The result set for this DMV is limited to 600 rows. 每一行都包含一个缺失索引。Each row contains one missing index. 如果缺少超过600个索引,则应该解决现有的缺失索引,以便可以查看更新的索引。If you have more than 600 missing indexes, you should address the existing missing indexes so you can then view the newer ones.

在 CREATE INDEX 语句中使用缺失索引信息Using Missing Index Information in CREATE INDEX Statements

若要将 sys.dm_db_missing_index_details 返回的信息转换为适用于内存优化索引和基于磁盘的索引的 CREATE index 语句,应将相等列放在不等列之前,并将它们放在一起,以创建索引的键。To convert the information returned by sys.dm_db_missing_index_details into a CREATE INDEX statement for both memory-optimized and disk-based indexes, equality columns should be put before the inequality columns, and together they should make the key of the index. 应该使用 INCLUDE 子句将包含列添加到 CREATE INDEX 语句。Included columns should be added to the CREATE INDEX statement using the INCLUDE clause. 若要确定相等列的有效顺序,请基于其选择性排序:首先列出选择性最强的列(列列表中的最左侧)。To determine an effective order for the equality columns, order them based on their selectivity: list the most selective columns first (leftmost in the column list).

有关内存优化索引的详细信息,请参阅 Memory-Optimized 表的索引For more information about memory-optimized indexes, see Indexes for Memory-Optimized Tables.

事务一致性Transaction Consistency

如果事务创建或删除了一个表,则包含有关已删除对象的缺失索引信息的行将从此动态管理对象中删除,以保持事务一致性。If a transaction creates or drops a table, the rows containing missing index information about the dropped objects are removed from this dynamic management object, preserving transaction consistency.

权限Permissions

在上 SQL ServerSQL Server ,需要 VIEW SERVER STATE 权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
在 SQL 数据库的基本、S0 和 S1 服务目标上,对于弹性池中的数据库, 服务器管理员 帐户或 Azure Active Directory 管理员 帐户是必需的。On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. 对于所有其他 SQL 数据库服务目标, VIEW DATABASE STATE 数据库中需要该权限。On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.

另请参阅See Also

sys.dm_db_missing_index_columns (Transact-sql) sys.dm_db_missing_index_columns (Transact-SQL)
sys.dm_db_missing_index_groups (Transact-sql) sys.dm_db_missing_index_groups (Transact-SQL)
sys.dm_db_missing_index_group_stats (Transact-sql)sys.dm_db_missing_index_group_stats (Transact-SQL)
sys.dm_db_missing_index_group_stats_query (Transact-sql)sys.dm_db_missing_index_group_stats_query (Transact-SQL)