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