Database Index Size

use <database_name>
go


SELECT tn.[name] AS [Table name], ix.[name] AS [Index name],
SUM(sz.[used_page_count]) * 8 AS [Index size (KB)]
FROM sys.dm_db_partition_stats AS sz
INNER JOIN sys.indexes AS ix ON sz.[object_id] = ix.[object_id] 
AND sz.[index_id] = ix.[index_id]
INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id
GROUP BY tn.[name], ix.[name]
ORDER BY 3 desc

Note:

• Column with varchar(max) column type will take up a lot of space and take longer to create.
• More columns the index cover (include), the more index space it will take up.
• The Non-cluster Index can use up as much space as Clustered Index if the Non-cluster index includes all the columns in the table.

Sources:

https://www.sqlshack.com/how-to-monitor-total-sql-server-indexes-size/