Sql Server无用索引查询

首先我们来看一下如何查询无用的索引。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]>1 AND is_primary_key=0 ;

 

因为我们只考察用户创建的表或者索引视图，最后我们只筛选出sys.objects 中type为“U”（用户创建的表）和“V”（用户创建的视图索引）。sys.indexes 中type=0是堆，type=1是聚集索引，所以也排除，同时我们排除主键索引。下面给出生成删除索引的脚本：

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]>1 AND is_primary_key=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   + CHAR(10)-- CHAR(10) 换行
       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]>1 AND is_primary_key=0
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)');
--exec sp_executesql @sql