sqlserver关于缺少索引的建议是怎么来的？

看sqlserver执行计划的时候有时会看到这样一行小绿字：

它会告诉你当前sql应该在哪些列上建索引（还有include列），建完之后能提升多少性能。对于特别复杂的sql，这个建议有时还是很方便的。那么这些数据是从哪查出来的？

sqlserver有四个索引缺失相关的视图：

• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_columns(index_handle)
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats

以上视图返回的信息会在查询优化器优化查询时更新，因而不是持久化的，缺失的索引信息只保留到重新启动SQL Server前。如果要在服务器回收后保留缺失索引信息，则应定期进行备份。

 

1. sys.dm_db_missing_index_details

返回有关缺失索引的详细信息，不包括空间索引。针对的是SQLSERVER从启动以来所有运行的语句，而不是某一个查询。

列名	数据类型	描述
index_handle	int	标识特定的缺失索引，该标识符在服务器中是唯一的
database_id	smallint	标识带有缺失索引的表所在的数据库
object_id	int	标识索引缺失的表
equality_columns	nvarchar(4000)	构成相等谓词的列的逗号分隔列表，谓词的形式如下： where table.column =constant_value
inequality_columns	nvarchar(4000)	构成不等谓词的列的逗号分隔列表，谓词的形式如下： where table.column > constant_value “=”之外的任何比较运算符都表示不相等。
included_columns	nvarchar(4000)	应该加在索引include部分的列。对于内存优化的索引 (包括哈希和内存优化非聚集)，忽略included_columns。 每个内存优化索引中均包含表的所有列。
statement	nvarchar(4000)	索引缺失的表的名称。

 

2. sys.dm_db_missing_index_columns(index_handle)

这是个动态管理函数，返回与缺少索引（不包括空间索引）的数据库表列有关的信息。

列名	数据类型	描述
column_id	int	列的 ID。
column_name	sysname	表列的名称。
column_usage	varchar(20)	查询使用列的方式。 可能的值及其说明是： equality：table.column = constant_value inequality：table.column > constant_value。 “=”之外的任何比较运算符都表示不相等。 include：用于索引的include列

select * from sys.dm_db_missing_index_columns(54);

返回的内容含义跟第一个视图类似，但是可读性稍微没那么好。比如根据上图，sqlserver的建议是：在ProductID上创建索引，SalesOrderID作为包含性列的索引。

CREATE INDEX idx_SalesOrderDetail_test_ProductID_IncludeIndex ON SalesOrderDetail_test(ProductID) INCLUDE(SalesOrderID);

 

3. sys.dm_db_missing_index_groups

返回有关特定缺失索引组中包含的缺失索引的信息，不包括空间索引（感觉没太大用处）

列名	数据类型	描述
index_group_handle	int	标识缺失索引组。
index_handle	int	标识缺失索引所属由指定的组index_group_handle。 一个索引组仅包含一个索引。

 

4. sys.dm_db_missing_index_group_stats

返回缺失索引组的摘要信息，不包括空间索引。最主要是avg_user_impact这个字段，告诉你增加了这个缺失索引，性能可以提高的百分比。

列名	数据类型	描述
group_handle	int	标识缺失索引组。 此标识符在服务器中是唯一的。 其他列提供有关组中的索引被视为缺失的所有查询的信息。 一个索引组仅包含一个索引。
unique_compiles	bigint	将从该缺失索引组受益的编译和重新编译数。 许多不同查询的编译和重新编译可影响该列值。
user_seeks	bigint	由可能使用了组中建议索引的用户查询所导致的查找次数。
user_scans	bigint	由可能使用了组中建议索引的用户查询所导致的扫描次数。
last_user_seek	datetime	由可能使用了组中建议索引的用户查询所导致的上次查找日期和时间。
last_user_scan	datetime	由可能使用了组中建议索引的用户查询所导致的上次扫描日期和时间。
avg_total_user_cost	float	可通过组中的索引减少的用户查询的平均成本。
avg_user_impact	float	实现此缺失索引组后，用户查询可能获得的平均百分比收益。 该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。
system_seeks	bigint	由可能使用了组中建议索引的系统查询（如自动统计信息查询）所导致的查找次数。 有关详细信息，请参阅Auto Stats 事件类。
system_scans	bigint	由可能使用了组中建议索引的系统查询所导致的扫描次数。
last_system_seek	datetime	由可能使用了组中建议索引的系统查询所导致的上次系统查找日期和时间。
last_system_scan	datetime	由可能使用了组中建议索引的系统查询所导致的上次系统扫描日期和时间。
avg_total_system_cost	float	可通过组中的索引减少的系统查询的平均成本。
avg_system_impact	float	实现此缺失索引组后，系统查询可能获得的平均百分比收益。 该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。

 

参考

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-group-stats-transact-sql?view=sql-server-ver15