SQL Server ->> 查找没用的索引
2021-09-28 16:49:21

SELECT 
(SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS sqlserver_start_time,
DB_NAME(diu.database_id) AS DatabaseName,
       s.name + '.' + QUOTENAME(o.name) AS TableName,
       i.index_id AS IndexID,
       i.name AS IndexName,
       CASE
           WHEN i.is_unique = 1 THEN
               'UNIQUE INDEX'
           ELSE
               'NOT UNIQUE INDEX'
       END AS IS_UNIQUE,
       CASE
           WHEN i.is_disabled = 1 THEN
               'DISABLE'
           ELSE
               'ENABLE'
       END AS IndexStatus,
       o.create_date AS IndexCreated,
       STATS_DATE(o.object_id, i.index_id) AS StatisticsUpdateDate,
       diu.user_seeks AS UserSeek,
       diu.user_scans AS UserScans,
       diu.user_lookups AS UserLookups,
       diu.user_updates AS UserUpdates,
       p.TableRows,
       'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(diu.object_id))
       + ';' AS 'Drop Index Statement'
FROM sys.dm_db_index_usage_stats diu
    INNER JOIN sys.indexes i
        ON i.index_id = diu.index_id
           AND diu.object_id = i.object_id
    INNER JOIN sys.objects o
        ON diu.object_id = o.object_id
    INNER JOIN sys.schemas s
        ON o.schema_id = s.schema_id
    INNER JOIN
    (
        SELECT SUM(p.rows) TableRows,
               p.index_id,
               p.object_id
        FROM sys.partitions p
        GROUP BY p.index_id,
                 p.object_id
    ) p
        ON p.index_id = diu.index_id
           AND diu.object_id = p.object_id
WHERE OBJECTPROPERTY(diu.object_id, 'IsUserTable') = 1
      AND diu.database_id = DB_ID()
      AND i.is_primary_key = 0 --排除主键索引
      AND i.is_unique_constraint = 0 --排除唯一索引
      AND diu.user_updates <> 0 --排除没有数据变化的索引
      AND diu.user_lookups = 0
      AND diu.user_seeks = 0
      AND diu.user_scans = 0
      AND i.name IS NOT NULL --排除那些没有任何索引的堆表
ORDER BY (diu.user_seeks + diu.user_scans + diu.user_lookups) ASC,
         diu.user_updates DESC;