SQL Server - 如何确定索引是否未被使用

# 如何确定 SQL Server 中的索引是否未被使用

sql
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM 
    sys.dm_db_index_usage_stats s
JOIN 
    sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE 
    database_id = DB_ID('YourDatabaseName')
    AND OBJECT_NAME(s.object_id) = 'YourTableName';

sql
DECLARE @IndexName NVARCHAR(128);
SELECT @IndexName = [name]
FROM sys.indexes
WHERE object_id = OBJECT_ID('YourTableName') AND [index_id] NOT IN (
    SELECT [index_id] FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID('YourDatabaseName') AND object_id = OBJECT_ID('YourTableName')
);
IF @IndexName IS NOT NULL
BEGIN
    DECLARE @DropIndexSQL NVARCHAR(1000);
    SET @DropIndexSQL = 'DROP INDEX ' + @IndexName + ' ON YourTableName;';
    EXEC sp_executesql @DropIndexSQL;
    PRINT 'Index ' + @IndexName + ' has been dropped.';
END
ELSE
    PRINT 'No unused index found.';