5.3 查找缺失的索引

索引的好处，大部分人都知道，但是索引也是有坏处的。对于SQL Server中的索引（甚至一切数据库管理系统，再甚至世间绝大部分事物）而言，并没有绝对的好或者绝对的坏。合适最主要。索引过多会导致update，delete，insert性能问题。索引过少会导致查询慢 CPU多，IO多，全表扫描。

对于核心表：所有索引不要超过6个。

对于普通表：所有索引不要超过4个。

对于小型表：所有索引不要超过3个。

5.3.1 查找缺失的索引需要的表

在生产系统中很难总是可以在合适的列上创建合适的索引。SQL2008以后，当一个查询执行的时候，SQLServer优化器会选择最适合的索引进行执行，当没有找到合适的索引话，优化器会产生一个次优执行计划，并且把丢失索引的信息存放到DMVs上。

当SQLServer服务重启后，所以存储在DMVs上的信息都会丢失，所以最好在正常使用大概90天收集这些信息，会比较有效。但是不能全部建立索引，有些索引是也许90天才使用一次，建立索引要适当。

sys.dm_db_missing_index_details：返回关于丢失索引的详细信息。

sys.dm_db_missing_index_group_stats：返回特定索引的汇总信息。

sys.dm_db_missing_index_groups：返回关于哪个丢失索引包含在哪个丢失索引组的句柄信息。

sys.dm_db_missing_index_columns(index_Handle)：返回在丢失索引中列的信息。

主要字段说明

avg_total_user_cost 可通过组中的索引减少的用户查询的平均成本。

avg_user_impact 实现此缺失索引组后，用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。

user_seeks 由可能使用了组中建议索引的用户查询所导致的查找次数。

user_scans 由可能使用了组中建议索引的用户查询所导致的扫描次数。

index_handle：标识特定的缺失索引。该标识符在服务器中是唯一

group_handle：标识缺失索引组，一个索引组仅包含一个索引。

5.3.2 查找缺失的索引

查找缺失的索引的表和列

SELECT mig.*, statement AS table_name,

column_id, column_name, column_usage

FROM sys.dm_db_missing_index_details AS mid

CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle

ORDER BY mig.index_group_handle, mig.index_handle, column_id;

查找缺失索引最多的

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

组合查询

SELECT avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement ,

last_user_seek ,

last_user_scan ,

[statement] AS [Object] ,

'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'

+ CONVERT(VARCHAR(32), D.index_handle) + '_'

+ REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')

+ ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')

+ CASE WHEN equality_columns IS NOT NULL

AND inequality_columns IS NOT NULL THEN ','

ELSE ''

END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('

+ included_columns

+ ')', '') AS Create_Index_Syntax

FROM sys.dm_db_missing_index_groups AS G

INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle

INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle

ORDER BY PossibleImprovement DESC

转载地址：http://lobai.baihongyu.com/

你可能感兴趣的文章

阿里P8架构师讲述：3—5年程序员的发展和出路在哪里？