Index Review Scripts

-- Index Usage Statistics Analysis
-- This query provides comprehensive index usage patterns and performance metrics

SELECT 
    OBJECT_SCHEMA_NAME(ius.object_id) AS schema_name,
    OBJECT_NAME(ius.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    i.is_unique,
    i.is_primary_key,
    
    -- Usage statistics
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    
    -- Total reads (seeks + scans + lookups)
    (ius.user_seeks + ius.user_scans + ius.user_lookups) AS total_reads,
    
    -- Read vs Write ratio
    CASE 
        WHEN ius.user_updates = 0 THEN 'Read Only'
        WHEN (ius.user_seeks + ius.user_scans + ius.user_lookups) = 0 THEN 'Write Only'
        ELSE CAST(ROUND(
            (ius.user_seeks + ius.user_scans + ius.user_lookups) * 1.0 / ius.user_updates, 2
        ) AS VARCHAR(10))
    END AS read_write_ratio,
    
    -- Last usage timestamps
    ius.last_user_seek,
    ius.last_user_scan,
    ius.last_user_lookup,
    ius.last_user_update,
    
    -- Index size information
    ps.used_page_count,
    ps.reserved_page_count,
    CAST(ps.used_page_count * 8.0 / 1024 AS DECIMAL(10,2)) AS size_mb,
    
    -- Fragmentation info (optional - can be resource intensive)
    -- ps.avg_fragmentation_in_percent,
    
    -- Usage classification
    CASE 
        WHEN (ius.user_seeks + ius.user_scans + ius.user_lookups) = 0 THEN 'UNUSED'
        WHEN ius.user_seeks > (ius.user_scans + ius.user_lookups) THEN 'SEEK_HEAVY'
        WHEN ius.user_scans > (ius.user_seeks + ius.user_lookups) THEN 'SCAN_HEAVY'
        WHEN ius.user_lookups > (ius.user_seeks + ius.user_scans) THEN 'LOOKUP_HEAVY'
        ELSE 'MIXED'
    END AS usage_pattern

FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps 
    ON ius.object_id = ps.object_id AND ius.index_id = ps.index_id
WHERE ius.database_id = DB_ID()
    AND OBJECT_NAME(ius.object_id) IS NOT NULL
    AND i.type_desc != 'HEAP'  -- Exclude heaps, focus on indexes

-- Order by total usage (most active indexes first)
ORDER BY (ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates) DESC;

-- Additional query to find potentially unused indexes
-- (indexes with no reads and minimal writes)
/*
SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    COALESCE(ius.user_seeks, 0) AS user_seeks,
    COALESCE(ius.user_scans, 0) AS user_scans,
    COALESCE(ius.user_lookups, 0) AS user_lookups,
    COALESCE(ius.user_updates, 0) AS user_updates,
    CAST(ps.used_page_count * 8.0 / 1024 AS DECIMAL(10,2)) AS size_mb
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id 
    AND i.index_id = ius.index_id 
    AND ius.database_id = DB_ID()
LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps 
    ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.type_desc != 'HEAP'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND OBJECT_NAME(i.object_id) IS NOT NULL
    AND (ius.user_seeks IS NULL OR ius.user_seeks = 0)
    AND (ius.user_scans IS NULL OR ius.user_scans = 0)
    AND (ius.user_lookups IS NULL OR ius.user_lookups = 0)
ORDER BY size_mb DESC;
*/