索引-缺失、使用情况SQL语句

查询当前数据库中缺失的索引，知道你进行优化的参考

 
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,
 
migs.last_user_seek , --上一次访问时间
 
mid.[statement] AS [Database.Schema.Table] ,--表
 
mid.equality_columns , --等式判断列
 
mid.inequality_columns ,--不等式判断列
 
mid.included_columns ,--于查询的涵盖列的逗号分隔列表。有关涵盖列或包含列的详细信息
 
migs.unique_compiles , --将从该缺失索引组受益的编译和重新编译数。许多不同查询的编译和重新编译可影响该列值
 
migs.user_seeks , --由可能使用了组中建议索引的用户查询所导致的查找次数
 
migs.avg_total_user_cost ,-- 可通过组中的索引减少的用户查询的平均成本
 
migs.avg_user_impact --实现此缺失索引组后，用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。
 
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
 
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle
 
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle
 
WHERE mid.database_id = DB_ID()--默认当前数据库。如果自己定义的数据库则使用DB_ID ( [ 'database_name' ] )
 
ORDER BY index_advantage DESC

缺失索引具体sql

SELECT
  avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)
    AS PossibleImprovement
  ,last_user_seek
  ,last_user_scan
  ,statement AS Object
  ,'CREATE INDEX [IDX_' + CONVERT(VARCHAR,GS.Group_Handle) + '_' +
  CONVERT(VARCHAR,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
  GS.group_handle = G.index_group_handle
INNER JOIN
  sys.dm_db_missing_index_details AS D
ON
  G.index_handle = D.index_handle
Order By PossibleImprovement DESC

查询当前数据库中所有未使用的索引

 
SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,
i.name
FROM sys.indexes AS i     
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN
( 
 SELECT s.index_id 
 FROM sys.dm_db_index_usage_stats AS s     
 WHERE s.[object_id] = i.[object_id] 
 AND i.index_id = s.index_id 
 AND database_id = DB_ID()  
 
 --下列条件作为时间判断，查看在某个时间之后未使用的索引列表，如果不需要可删除
 
AND
 (
 last_user_seek>='@DateTime' or   --用户上次执行搜索时间
 last_user_scan>='@DateTime' or   --用户上次执行扫描时间
 last_system_seek>='@DateTime' or --系统上次执行搜索的时间
 last_system_scan>='@DateTime'    --系统上次执行扫描的时间
 )
) 
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC

查询当前数据库中使用较少的索引或者写入次数大于读取次数的索引

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , 
i.name AS [Index Name] , --索引名称
i.index_id , 
user_updates AS [Total Writes] ,--写入次数
user_seeks + user_scans + user_lookups AS [Total Reads] ,--读取次数
user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]--写入与读取只差
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) 
INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id
WHERE
OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1 
AND s.database_id = DB_ID() 
AND user_updates > (user_seeks + user_scans + user_lookups ) 
AND i.index_id > 1 --聚集索引和非聚集索引
ORDER BY [Difference] DESC , 
[Total Writes] DESC , 
[Total Reads] ASC ;

查看现有索引的使用情况

SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] , 
  i.name AS [IndexName] , i.index_id , 
  user_seeks + user_scans + user_lookups AS [Reads] , 
  user_updates AS [Writes] , 
  i.type_desc AS [IndexType] , 
  i.fill_factor AS [FillFactor]--填充因子
  FROM sys.dm_db_index_usage_stats AS s 
  INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
  WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1 
  AND i.index_id = s.index_id 
  AND s.database_id = DB_ID()
  ORDER BY OBJECT_NAME(s.[object_id]) , 
  Writes DESC ,
  Reads DESC ;

参考资料: https://www.cnblogs.com/51net/p/13976108.html