Перелік індексів бази даних, які давно не використовувались

SQL-запит: Невикористані індекси з розміром

SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) AS [Schema Name],
    OBJECT_NAME(i.object_id) AS [Table Name],
    i.name AS [Index Name],
    i.index_id,
    i.type_desc AS [Index Type],
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    i.is_disabled,
    ISNULL(s.user_seeks, 0) AS user_seeks,
    ISNULL(s.user_scans, 0) AS user_scans,
    ISNULL(s.user_lookups, 0) AS user_lookups,
    ISNULL(s.user_updates, 0) AS user_updates,
    s.last_user_seek,
    s.last_user_scan,
    s.last_user_lookup,
    CAST(ISNULL(ps.used_page_count, 0) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Index Size (MB)]
FROM 
    sys.indexes i
LEFT 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()
LEFT JOIN 
    sys.dm_db_partition_stats ps 
    ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE 
    OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.index_id > 0
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND ISNULL(s.user_seeks, 0) = 0
    AND ISNULL(s.user_scans, 0) = 0
    AND ISNULL(s.user_lookups, 0) = 0
GROUP BY 
    i.object_id, i.index_id, i.name, i.type_desc,
    i.is_unique, i.is_primary_key, i.is_unique_constraint, i.is_disabled,
    s.user_seeks, s.user_scans, s.user_lookups, s.user_updates,
    s.last_user_seek, s.last_user_scan, s.last_user_lookup,
    ps.used_page_count
ORDER BY 
    [Index Size (MB)] DESC, [Schema Name], [Table Name];

Andriy Kravchenko

Admin, Writer, File Uploader

28.03.2025 16:27:13