SQLServer索引优化实操

运行环境：3台SQLSever，做的高可用配置，一写两读，分别定义为主库、读库，报表库，

系统一直在不断的迭代，有新增功能，也有功能停用，这就造成了有的索引缺失，有的索引用量较低

数据表，有的记录条达到千万级，用量较低的索引也会占用较多的空间。

针对上述问题，进行了索引优化操作，1 查找缺失索引，2 清理冗余索引

1 缺失索引处理

由于企业工作性质原因，日常应用主要集中在白天，晚上用量较少。

因此，下午下班时，分别在三台服务器上，执行缺失索引查找的SQL语句，根据综合情况，针对同一张表索引缺失列的情况，找出适合的索引列和包含列，然后新建索引

2 冗余索引处理

冗余索引处理比较麻烦，因为3台数据库的应用场景不同，对索引的使用的频次也不相用，而且公司周末的数据访问量是平时的2倍。

第一步，记录索引用量

编写一个程序，每天定时抓取、合并索引的使用量，记录每天每个表每个索引在3个表中的查询量、扫描量。

这样运行一段时间，为后期的优化准备好数据依据

第二步，确认冗余索引

取一个时段，综合比较索引在3个表中的查询量，初步筛选每日查询量均小于300，索引进行删除。后期，观察缺失索引的情况，再逐步提高阈值。

经过一轮的新增、删除索引，第二天缺失、冗余索引情况会发生较大的变化，再收集几天的数据，再优化一轮。

这样经过几轮优化，系统运行效果会提高很多。

1.索引空间

SELECT tn.[name] AS [Table name], ix.[name] AS [Index name]
,SUM(sz.[used_page_count]) * 8 AS [Index size (KB)]
FROM sys.dm_db_partition_stats AS sz
INNER JOIN sys.indexes AS ix ON sz.[object_id] = ix.[object_id]
AND sz.[index_id] = ix.[index_id]
INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id
 WHERE tn.[name]='表名'
 GROUP BY tn.[name], ix.[name]
 ORDER BY tn.[name]

2.索引使用量

select db_name(database_id) as N'数据库名称',
       object_name(a.object_id) as tbname,
      b.name N'索引名称',
       user_seeks N'用户索引查找次数',
       user_scans N'用户索引扫描次数'
from sys.dm_db_index_usage_stats a 
 left join sys.indexes b on a.index_id = b.index_id and a.object_id = b.object_id
where database_id=db_id('库名')   
and object_name(a.object_id)='表名'
order by object_name(a.object_id) , user_seeks 

3.缺失索引

SELECT [TotalCost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0),
       avg_user_impact,user_seeks,user_scans,
       TableName = statement,
       [EqualityUsage] = equality_columns,
       [InequalityUsage] = inequality_columns,
       [Include Cloumns] = included_columns
  FROM sys.dm_db_missing_index_groups g
 INNER JOIN sys.dm_db_missing_index_group_stats s
    ON s.group_handle = g.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details d
    ON d.index_handle = g.index_handle
   where statement like '%表名%'
  ORDER BY  user_seeks DESC;

4.冗余索引

SELECT OBJECT_NAME(i.object_id) ,i.name indexname,user_seeks,user_scans,last_user_seek,last_user_scan
  FROM sys.dm_db_index_usage_stats s
 INNER JOIN sys.indexes i
    ON s.object_id = i.object_id
   AND s.index_id = i.index_id
 WHERE database_id = db_id('库名')
	 and not i.name is null 
 	 and not i.name like 'PK%'
  ORDER BY user_seeks