Index Efficiency & Maintenance Advisor

/*
Index Efficiency & Maintenance Advisor
Identifies:
1. Duplicate indexes (same keys/includes)
2. Underutilized indexes (low reads, high writes)
3. Highly fragmented indexes
4. Indexes with high maintenance cost relative to value
*/DECLARE 
    @MinPageCount INT = 1000,          -- Minimum data pages (8KB/page) to consider
    @FragThreshold FLOAT = 30.0,        -- Min fragmentation % to flag
    @UsageDaysThreshold INT = 30,        -- Min server uptime days for usage stats
    @ReadsPerMBThreshold INT = 10,      -- Reads/MB threshold (usage efficiency)
    @UpdateReadRatioThreshold INT = 10; -- Writes vs Reads ratio threshold

WITH IndexData AS (
    SELECT 
        DB_NAME() AS DatabaseName,
        SCHEMA_NAME(t.schema_id) AS SchemaName,
        t.name AS TableName,
        i.name AS IndexName,
        i.type_desc AS IndexType,
        i.is_primary_key,
        i.is_unique,
        i.is_disabled,
        COALESCE(s.user_seeks, 0) AS user_seeks,
        COALESCE(s.user_scans, 0) AS user_scans,
        COALESCE(s.user_lookups, 0) AS user_lookups,
        COALESCE(s.user_updates, 0) AS user_updates,
        s.last_user_seek,
        s.last_user_scan,
        s.last_user_lookup,
        ps.page_count,
        ps.avg_fragmentation_in_percent,
        (ps.page_count * 8.0 / 1024) AS SizeMB,
        COALESCE(STUFF((
            SELECT ', ' + COL_NAME(ic.object_id, ic.column_id) 
            FROM sys.index_columns ic 
            WHERE 
                ic.object_id = i.object_id 
                AND ic.index_id = i.index_id 
                AND ic.is_included_column = 0
            ORDER BY ic.key_ordinal
            FOR XML PATH('')
        ),1,2,''), '') AS KeyColumns,
        COALESCE(STUFF((
            SELECT ', ' + COL_NAME(ic.object_id, ic.column_id) 
            FROM sys.index_columns ic 
            WHERE 
                ic.object_id = i.object_id 
                AND ic.index_id = i.index_id 
                AND ic.is_included_column = 1
            ORDER BY ic.index_column_id
            FOR XML PATH('')
        ),1,2,''), '') AS IncludedColumns
    FROM 
        sys.tables t
        INNER JOIN sys.indexes i ON t.object_id = i.object_id
        LEFT JOIN sys.dm_db_index_usage_stats s ON 
            s.object_id = i.object_id 
            AND s.index_id = i.index_id 
            AND s.database_id = DB_ID()
        CROSS APPLY sys.dm_db_index_physical_stats(
            DB_ID(), 
            i.object_id, 
            i.index_id, 
            NULL, 
            'LIMITED'
        ) ps
    WHERE 
        i.type > 0  -- Exclude heaps
        AND t.is_ms_shipped = 0
        AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
        AND ps.page_count > @MinPageCount
),
DuplicateIndexes AS (
    SELECT 
        DatabaseName,
        SchemaName,
        TableName,
        KeyColumns,
        IncludedColumns,
        COUNT(*) AS DupCount
    FROM IndexData
    GROUP BY 
        DatabaseName,
        SchemaName,
        TableName,
        KeyColumns,
        IncludedColumns
    HAVING COUNT(*) > 1
),
ServerUptime AS (
    SELECT 
        DATEDIFF(DAY, sqlserver_start_time, GETDATE()) AS UptimeDays
    FROM sys.dm_os_sys_info
)
SELECT 
    id.DatabaseName,
    id.SchemaName,
    id.TableName,
    id.IndexName,
    id.IndexType,
    id.is_primary_key,
    id.is_unique,
    id.SizeMB,
    id.avg_fragmentation_in_percent AS FragmentationPct,
    id.user_seeks,
    id.user_scans,
    id.user_lookups,
    id.user_updates,
    (id.user_seeks + id.user_scans + id.user_lookups) AS TotalReads,
    id.KeyColumns,
    id.IncludedColumns,
    COALESCE(di.DupCount, 1) AS DupCount,
    CASE 
        WHEN di.DupCount > 1 THEN 'DUPLICATE'
        WHEN su.UptimeDays > @UsageDaysThreshold 
            AND (id.user_seeks + id.user_scans + id.user_lookups) = 0 
            AND id.user_updates > 0 THEN 'UNUSED (COSTLY WRITES)'
        WHEN su.UptimeDays > @UsageDaysThreshold 
            AND (id.user_seeks + id.user_scans + id.user_lookups) > 0 
            AND (id.user_seeks + id.user_scans + id.user_lookups) < (id.SizeMB * @ReadsPerMBThreshold) 
            AND id.user_updates > (id.user_seeks + id.user_scans + id.user_lookups) * @UpdateReadRatioThreshold 
            THEN 'INEFFICIENT (HIGH WRITE/READ RATIO)'
        WHEN id.avg_fragmentation_in_percent > @FragThreshold THEN 'HIGH FRAGMENTATION'
        ELSE 'ANALYZE MANUALLY'
    END AS IssueType,
    CASE 
        WHEN di.DupCount > 1 THEN 'Consider dropping duplicate indexes'
        WHEN su.UptimeDays <= @UsageDaysThreshold 
            THEN 'Insufficient usage data - server restarted ' + CAST(su.UptimeDays AS VARCHAR) + ' days ago'
        WHEN (id.user_seeks + id.user_scans + id.user_lookups) = 0 
            THEN 'Potential candidate for removal. Verify with business logic first.'
        ELSE 'Evaluate index maintenance/usage patterns'
    END AS Recommendation,
    id.last_user_seek,
    id.last_user_scan
FROM 
    IndexData id
    LEFT JOIN DuplicateIndexes di ON 
        id.DatabaseName = di.DatabaseName
        AND id.SchemaName = di.SchemaName
        AND id.TableName = di.TableName
        AND id.KeyColumns = di.KeyColumns
        AND id.IncludedColumns = di.IncludedColumns
    CROSS JOIN ServerUptime su
WHERE 
    di.DupCount > 1 
    OR (
        su.UptimeDays > @UsageDaysThreshold 
        AND (
            (id.user_seeks + id.user_scans + id.user_lookups) = 0
            OR id.avg_fragmentation_in_percent > @FragThreshold
            OR (id.user_seeks + id.user_scans + id.user_lookups) < (id.SizeMB * @ReadsPerMBThreshold)
        )
    )
ORDER BY 
    id.SizeMB DESC,
    di.DupCount DESC;