首先我们来看一下如何查询无用的索引。sys.dm_db_index_usage_stats 记录自上次重启或数据库离线或重置统计信息后使用到的索引,sys.indexes 记录数据中所有表的索引,排除掉最近使用的索引,即为最近没有使用的索引,具体脚本如下:
- --查询数据库中没有使用过到索引
- USE WideWorldImporters;
- GO
- DECLARE @dbid INT=DB_ID('WideWorldImporters');
- WITH cte AS(
- SELECT
- [object_id],index_id
- FROM sys.indexes
- EXCEPT
- SELECT
- [object_id],index_id
- FROM sys.dm_db_index_usage_stats
- WHERE database_id=@dbid)
- SELECT
- o.name tableName,i.name indexName
- FROM sys.indexes i
- INNER JOIN cte ON cte.index_id=i.index_id AND cte.[object_id]=i.[object_id]
- INNER JOIN sys.objects o ON i.[object_id]=o.[object_id]
- WHERE o.[type] IN ('U','V') AND i.[type]>0;
因为我们只考察用户创建的表或者索引视图,最后我们只筛选出sys.objects 中type为“U”(用户创建的表)和“V”(用户创建的视图索引)。sys.indexes 中type=0是堆,所以也排除。下面给出产生删除索引的脚本:
- --删除没有使用过索引脚本产生
- USE WideWorldImporters;
- GO
- DECLARE @dbid INT=DB_ID('WideWorldImporters');
- WITH cte AS(
- SELECT
- [object_id],index_id
- FROM sys.indexes
- EXCEPT
- SELECT
- [object_id],index_id
- FROM sys.dm_db_index_usage_stats
- WHERE database_id=@dbid)
- SELECT
- 'DROP INDEX '+i.name+' ON '+ o.name
- FROM sys.indexes i
- INNER JOIN cte ON cte.index_id=i.index_id AND cte.[object_id]=i.[object_id]
- INNER JOIN sys.objects o ON i.[object_id]=o.[object_id]
- WHERE o.[type] IN ('U','V') AND i.[type]>0;
上面的脚本每条对应一个表的一个索引的删除语句,当然也可以使用如下脚本产生一条语句。
- DECLARE @dbid INT=DB_ID('WideWorldImporters');
- declare @sql varchar(max);
- WITH cte AS(
- SELECT
- [object_id],index_id
- FROM sys.indexes
- EXCEPT
- SELECT
- [object_id],index_id
- FROM sys.dm_db_index_usage_stats
- WHERE database_id=@dbid)
- SELECT @sql=(
- SELECT
- 'drop index '+i.name+' on '+ o.name
- FROM sys.indexes i
- INNER JOIN cte ON cte.index_id=i.index_id AND cte.[object_id]=i.[object_id]
- INNER JOIN sys.objects o ON i.[object_id]=o.[object_id]
- WHERE o.[type] IN ('U','V') AND i.[type]>0
- FOR XML PATH(''),type).value('.','NVARCHAR(MAX)');
- --exec sp_executesql @sql
细心的读者会发现,上面最后一条语句(exec sp_executesql @sql)是注释掉的,直接这样执行是可以最快速的删除所有无用索引。但是,正如我们上面所说的,sys.dm_db_index_usage_stats 记录自上次重启或数据库离线或重置统计信息后使用到的索引,所以其记录的用到的索引可能是不全的(如果我们最近刚重启过数据库服务、数据库所在的服务器或者重置了动态视图),这样可能导致部分有用的索引也被删除掉,切记、切记、切记,生成的脚本不能直接执行。保险的做法是,至少,在数据库服务运行一个月做这样的事情,如果有经常重启维护的数据库服务,可以在数据库重启维护之前收集记录已经使用的索引。经过几个月或一年的记录,最终确定不需要的索引,再进行删除。
联系客服