sqlserver 清理无用索引

-- 第一部分：查找3天内未使用的索引
SELECT 
    OBJECT_NAME(i.object_id) AS '表名',
    i.name AS '索引名称',
    i.index_id AS '索引ID',
    s.user_seeks AS '用户索引查找次数',          -- 代表通过索引查找（seek）访问的次数
    s.user_scans AS '用户索引扫描次数',           -- 代表通过索引扫描（scan）访问的次数
    s.user_lookups AS '用户书签查找次数',        -- 代表通过书签查找（lookup）访问的次数
    s.user_updates AS '索引更新次数',            -- 代表索引被更新（增删改）的次数
    s.last_user_seek AS '最后查找时间',          -- 最后一次索引查找（seek）的时间
    s.last_user_scan AS '最后扫描时间',          -- 最后一次索引扫描（scan）的时间
    s.last_user_lookup AS '最后书签查找时间'     -- 最后一次书签查找（lookup）的时间
FROM 
    sys.indexes i
INNER JOIN 
    sys.objects o ON i.object_id = o.object_id
LEFT OUTER JOIN 
    sys.dm_db_index_usage_stats s ON i.object_id = s.object_id 
    AND i.index_id = s.index_id 
    AND s.database_id = DB_ID()
WHERE 
    o.type = 'U'                       -- 只查询用户表（排除系统表）
    AND i.name IS NOT NULL             -- 排除堆表（无索引的表）
    AND i.is_primary_key = 0           -- 排除主键索引
    AND i.is_unique_constraint = 0     -- 排除唯一约束索引
    AND (
        -- 条件1：从未被使用过（所有使用次数为0或NULL）
        ((s.user_seeks = 0 OR s.user_seeks IS NULL)
        AND (s.user_scans = 0 OR s.user_scans IS NULL) 
        AND (s.user_lookups = 0 OR s.user_lookups IS NULL))
        OR 
        -- 条件2：最后一次使用时间在3天前（或从未使用）
        ((s.last_user_seek < DATEADD(day, -3, GETDATE()) OR s.last_user_seek IS NULL)
        AND (s.last_user_scan < DATEADD(day, -3, GETDATE()) OR s.last_user_scan IS NULL)
        AND (s.last_user_lookup < DATEADD(day, -3, GETDATE()) OR s.last_user_lookup IS NULL))
    )
ORDER BY 
    '表名', '索引名称';

-- 第二部分：生成删除语句
SELECT 
    'DROP INDEX [' + i.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(i.object_id) + '];' 
    AS '删除索引语句' 
FROM 
    sys.indexes i
INNER JOIN 
    sys.objects o ON i.object_id = o.object_id
LEFT OUTER JOIN 
    sys.dm_db_index_usage_stats s ON i.object_id = s.object_id 
    AND i.index_id = s.index_id 
    AND s.database_id = DB_ID()
WHERE 
    o.type = 'U'
    AND i.name IS NOT NULL
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND (
        ((s.user_seeks = 0 OR s.user_seeks IS NULL)
        AND (s.user_scans = 0 OR s.user_scans IS NULL) 
        AND (s.user_lookups = 0 OR s.user_lookups IS NULL))
        OR 
        ((s.last_user_seek < DATEADD(day, -3, GETDATE()) OR s.last_user_seek IS NULL)
        AND (s.last_user_scan < DATEADD(day, -3, GETDATE()) OR s.last_user_scan IS NULL)
        AND (s.last_user_lookup < DATEADD(day, -3, GETDATE()) OR s.last_user_lookup IS NULL))
    )