Summarise indexes
Summarise indexes in the current database:
- index type & size;
- index utilisation;
- index & included columns.
- indexes.sql
;WITH cols AS ( SELECT i.object_id , i.index_id , c.name AS ColumnName , ic.key_ordinal , ic.is_descending_key , ic.is_included_column FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON c.object_id = i.object_id AND c.column_id = ic.column_id ) SELECT s.name AS SchemaName , o.name AS ObjectName , o.[type] AS ObjectType , i.name AS IndexName , i.type_desc AS IndexType , i.is_primary_key AS PK , ROUND((CAST(phys.page_count AS FLOAT) * 8) / 1024, 1) AS IndexSizeMB , COALESCE(usage.user_scans + usage.system_scans, 0) AS Scans , COALESCE(usage.user_seeks + usage.system_seeks, 0) AS Seeks , COALESCE(usage.user_lookups + usage.system_lookups, 0) AS Lookups , ROUND(phys.avg_fragmentation_in_percent, 2) AS [% fragmented] , COALESCE(SUBSTRING(( SELECT ', ' + cols.ColumnName + CASE cols.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END FROM cols WHERE cols.is_included_column = 0 AND cols.object_id = o.object_id AND cols.index_id = i.index_id ORDER BY cols.key_ordinal FOR XML PATH(''), TYPE ).value('.','VARCHAR(MAX)') , 3, 5000), '') AS IndexColumns , COALESCE(SUBSTRING(( SELECT ', ' + cols.ColumnName FROM cols WHERE cols.is_included_column = 1 AND cols.object_id = o.object_id AND cols.index_id = i.index_id ORDER BY cols.key_ordinal FOR XML PATH(''), TYPE ).value('.','VARCHAR(MAX)') , 3, 5000), '') AS IncludedColumns FROM sys.indexes i INNER JOIN sys.objects o ON o.object_id = i.object_id INNER JOIN sys.schemas s ON s.schema_id = o.schema_id LEFT JOIN sys.dm_db_index_usage_stats usage ON usage.object_id = o.object_id AND usage.index_id = i.index_id AND usage.database_id = DB_ID() LEFT JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) phys ON phys.object_id = o.object_id AND phys.index_id = i.index_id AND phys.alloc_unit_type_desc = 'IN_ROW_DATA' WHERE i.[type] > 0 -- != heap AND o.[type] IN ('U', 'V') ORDER BY s.name, o.name, i.name