Get index size and usage

I had a team reach out to me, asking if we could clean-up some indexes on a table. This is the script I ended up with, to get and document the usage of the indexes, making it visible to the team what indexes could be removed or should be kept.

-- Index usage statistics with size information
SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    
    -- Size metrics
    ps.[row_count] AS [RowCount],
    (ps.used_page_count * 8.0) / 1048576.0 AS IndexSizeGB,  -- 8KB pages -> GB
    (ps.reserved_page_count * 8.0) / 1048576.0 AS ReservedSizeGB,
    
    -- Usage stats
    ISNULL(ius.user_seeks, 0) AS UserSeeks,
    ISNULL(ius.user_scans, 0) AS UserScans,
    ISNULL(ius.user_lookups, 0) AS UserLookups,
    ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0) AS TotalReads,
    ISNULL(ius.user_updates, 0) AS UserUpdates,
    
    -- Read/Update Ratio
    CASE 
        WHEN ISNULL(ius.user_updates, 0) = 0 THEN NULL
        ELSE CAST((ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0)) AS DECIMAL(18,6)) 
             / CAST(ius.user_updates AS DECIMAL(18,6))
    END AS ReadUpdateRatio,
    
    ius.last_user_seek AS LastSeek,
    ius.last_user_scan AS LastScan,
    
    -- Index columns
    STUFF((
        SELECT ', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
        FROM sys.index_columns ic
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        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(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS KeyColumns,
    
    -- Included columns
    STUFF((
        SELECT ', ' + c.name
        FROM sys.index_columns ic
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id 
          AND ic.index_id = i.index_id
          AND ic.is_included_column = 1
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS IncludedColumns
FROM sys.indexes i
INNER JOIN sys.dm_db_partition_stats ps 
    ON i.object_id = ps.object_id 
    AND i.index_id = ps.index_id
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()
INNER JOIN sys.objects o
    ON i.object_id = o.object_id AND o.type = 'U'
--WHERE i.object_id = OBJECT_ID('dbo.Posts')
--  AND i.name IN (
--      'IX_Creation_date',
--  )
ORDER BY 
    OBJECT_SCHEMA_NAME(i.object_id), 
    OBJECT_NAME(i.object_id), 
    (ps.used_page_count * 8) / 1024.0 DESC;

Ended up picking some of the datapoints and send an email, with a table much like this:

Making it easy for the team to have an idea of the index usage.

This could be extended to be gathered over time (eg. with RedGate or a homemade setup) to more accurately depict the usage, as this is just since the last database startup.