数据库引擎是高度优化的闭环系统，基于执行计划的反馈，查询优化器在一定程度上自动优化现有的执行计划。查询优化的核心是索引优化，数据库引擎通过计数器统计关于索引操作的数据，统计的信息包括：使用次数、物理存储、底层操作的计数，以及缺失索引等，这些统计数据存储在内存中，是数据库引擎执行情况的真实反馈，高度概括了索引的执行情况，有意识地利用索引的统计信息，有针对性地优化现有的业务逻辑代码，调整查询的执行计划，能够提高数据库的查询性能。
在用户成功提交查询语句时，执行计划中每一个单独的索引操作（Seek，Scan，Lookup或Update）都会被统计到sys.dm_db_index_usage_stats 中，例如，user_updates 计数器统计索引执行Insert,Update或Delete操作的次数，查找计数器（user_seeks, user_scans, user_lookups）统计在索引上执行的seek，scan和lookup操作的次数，如果查找计数器远远小于user_updates 计数器，这说明基础表会执行大量的更新操作，维护索引更新的开销比较大，数据库引擎利用索引提升查询性能的空间有限。
在计数时，每一个单独的seek、scan、lookup或update操作都被计算为对该索引的一次使用，并使该视图中的相应计数器加1。
索引的Seek，Scan，Lookup和Update的含义是：
Index Seek和Index Scan的区别是：
如果索引的Seek，Scan，Lookup的计数值较多，那么说明索引被引用的次数多；如果查找计数器数值较小，但是Update数值较多，说明维护Index的开销高于查询带来的性能提升，应该考虑修改索引的结构，或者直接把索引删除。
select db_name(us.database_id) as db_name ,object_schema_name(us.object_id)+‘.‘+object_name(us.object_id) as table_name ,i.name as index_name ,i.type_desc as index_type_desc ,us.user_seeks ,us.user_scans ,us.user_lookups ,us.user_updates from sys.dm_db_index_usage_stats us inner join sys.indexes i on us.object_id=i.object_id and us.index_id=i.index_id where us.database_id=db_id() --us.database_id=db_id(‘database_name‘) --and us.object_id=object_id(‘schema_name.table_name‘) order by us.user_seeks desc
使用 sys.dm_db_index_physical_stats 函数统计索引的物理存储，例如，碎片的百分比，数据存储的集中和分散程度，以及page空间的利用率等：
请阅读《索引碎片的检测和整理》，以了解更多。
使用 sys.dm_db_index_operational_stats 函数统计底层IO、加锁（Locking）、Latch和数据访问模式的计数，通过这些数据，用户能够追踪到查询请求必须等待多长时间才能完成数据的读写、标识索引是否存在IO热点。
在统计索引的底层操作之前，先了解跟数据的物理存储相关的术语：
This (pulled in-row) occurs when an update operation frees up space in a record and provides an opportunity to pull in one or more off-row values from the LOB_DATA or ROW_OVERFLOW_DATA allocation units to the IN_ROW_DATA allocation unit.
以下脚本用于统计索引底层的存储动作和锁/Latch的争用：
select db_name(ops.database_id) as db_name ,object_schema_name(ops.object_id)+‘.‘+object_name(ops.object_id) as table_name ,i.name as index_name ,ops.partition_number ,ops.leaf_insert_count ,ops.leaf_delete_count ,ops.leaf_update_count ,ops.leaf_ghost_count ,ops.nonleaf_insert_count ,ops.nonleaf_delete_count ,ops.nonleaf_update_count ,ops.range_scan_count ,ops.singleton_lookup_count ,ops.forwarded_fetch_count ,iif(ops.row_lock_wait_count=0,0,ops.row_lock_wait_in_ms/ops.row_lock_wait_count) as avg_row_lock_wait_ms ,iif(ops.page_lock_wait_count=0,0,ops.page_lock_wait_in_ms/ops.page_lock_wait_count) as avg_page_lock_wait_ms ,iif(ops.page_latch_wait_count=0,0,ops.page_latch_wait_in_ms/ops.page_latch_wait_count) as avg_page_latch_wait_ms ,iif(ops.page_io_latch_wait_count=0,0,ops.page_io_latch_wait_in_ms/ops.page_io_latch_wait_count) as avg_page_io_latch_wait_ms from sys.dm_db_index_operational_stats(db_id(),object_id(‘dbo.FactThread‘),null,null) as ops inner join sys.indexes i on ops.object_id=i.object_id and ops.index_id=i.index_id order by index_name
该函数统计的Latch征用数据主要分为PageLatch和PageIOLatch，其区别是：
PageLatch是施加在Buffer上的Latch， 用来保护：Data page，Index Page, 系统page（PFS,GAM,SGAM,IAM等）的争用访问；在数据更新时，分配新的page，或拆分 索引页（Index Page），会产生PageLatch 等待。
分析查询结果，根据计数器的数值，调整数据库，使系统达到最优状态：
alter index index_name on table_name rebuild
查询优化器（Query Optimizer）在执行查询时，如果检测到执行计划缺失索引，会把缺失索引的相关信息存储在缓存中，通过 sys.dm_db_missing_index_details 可以检测查询优化器建议创建的缺失索引。
该视图返回的缺失索引的索引键及包含列信息，在索引列的顺序上，通常来说，相等列（equality）应该排在不等列（inequality）之前，用户需要根据查询的条件来调整相等列和不等列的顺序，包含列（Included）应该添加到INCLUDE子句中，但是，该视图不会标识出相等列（equality）的排列顺序，需要根据查询语句和选择性来设置，索引键的第一列至关重要。而不等列（inequality）是指除等号（=）之外的比较符号，例如，table.cloumn>value。
select mid.index_handle ,db_name(mid.database_id) as db_name ,mid.object_id ,object_name(mid.object_id,mid.database_id) as object_name ,mid.equality_columns ,mid.inequality_columns ,mid.included_columns ,mid.statement as underlying_table ,mic.column_id ,mic.column_name ,mic.column_usage from sys.dm_db_missing_index_details as mid cross apply sys.dm_db_missing_index_columns(mid.index_handle) as mic order by mid.object_id ,mid.index_handle
statement字段是缺失索引的表的名称，object_id字段是缺失索引的表的id，index_handle用于标识缺失的索引。
缺失的索引都被分组，这意味着每一个缺失索引都被分配到一个特定的分组中，系统根据缺失索引的索引键把缺失索引分配到一个组中。
在实际的数据库系统中，缺失索引可能很多，但是，并不是所有的缺失索引都对查询性能的提升有同等重要的作用，这可以通过系统视图：sys.dm_db_missing_index_group_stats 来度量：
select top 111 g.index_handle ,gs.unique_compiles ,gs.user_scans ,gs.user_seeks ,gs.avg_total_user_cost ,gs.avg_user_impact ,gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) benefit_weight from sys.dm_db_missing_index_groups g inner join sys.dm_db_missing_index_group_stats gs on g.index_group_handle=gs.group_handle order by benefit_weight desc
重要的字段注释：
在实际的数据库系统中，数据库管理员需要监控分组的统计数据，根据开销和收益来创建缺失的索引，以最大程序的提高系统查询性能。
通过视图 sys.indexes 和 sys.index_columns 查看在基础表创建的所有索引：
select o.name as table_name ,i.index_id ,i.name as index_name ,i.type_desc as index_type ,c.name AS index_column_name ,ic.key_ordinal as index_key_ordinal ,iif(ic.is_descending_key=1,‘desc‘,‘asc‘) as sort_direction ,ic.index_column_id ,ic.is_included_column ,i.fill_factor ,i.is_padded ,i.has_filter ,i.filter_definition --,ic.partition_ordinal from sys.objects o inner join sys.indexes i on o.object_id = i.object_id inner join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id inner join sys.columns c on o.object_id = c.object_id and ic.column_id = c.column_id where o.name = ‘table_name‘ --and i.name=‘index_name‘ order by i.index_id, ic.index_column_id
参考文档：
An in-depth look at Ghost Records in SQL Server
Index Related Dynamic Management Views and Functions (Transact-SQL)
原文：https://www.cnblogs.com/lonelyxmas/p/11980778.html
