[MSSQL] 6. 모든 인덱스 용량 조회

1. 특정 DB의 인덱스 용량만 조회할 경우

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    CASE i.index_id WHEN 1 THEN 'Clustered Index' ELSE 'NonClustered Index' END AS IndexType,
    SUM(au.used_pages) * 8 AS 'Indexsize(KB)'
FROM
    sys.indexes AS i
    INNER JOIN sys.partitions AS p
       ON p.OBJECT_ID = i.OBJECT_ID
      AND p.index_id = i.index_id
    INNER JOIN sys.allocation_units AS au
      ON au.container_id = p.partition_id
WHERE
    i.index_id != 0                               -- not heap
    and OBJECT_SCHEMA_NAME(i.object_id) != 'sys'  -- not system table
GROUP BY
    OBJECT_SCHEMA_NAME(i.object_id),
    OBJECT_NAME(i.OBJECT_ID),
    i.OBJECT_ID,
    i.index_id,
    i.name
ORDER BY
	SUM(au.used_pages) * 8  desc,
    OBJECT_SCHEMA_NAME(i.object_id),
    OBJECT_NAME(i.OBJECT_ID),
    i.index_id
GO

 

2. 장비 내 전체 DB의 인덱스 용량을 전부 조회할 경우

DROP TABLE IF EXISTS #temp2
GO

SELECT TOP 0 
	DB_ID() AS DBID,
	DB_NAME() AS DBName,
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    CASE i.index_id WHEN 1 THEN 'Clustered Index' ELSE 'NonClustered Index' END AS IndexType,
    SUM(au.used_pages) * 8 AS 'Indexsize(KB)'
INTO #temp2
FROM
    sys.indexes AS i
    INNER JOIN sys.partitions AS p
       ON p.OBJECT_ID = i.OBJECT_ID
      AND p.index_id = i.index_id
    INNER JOIN sys.allocation_units AS au
      ON au.container_id = p.partition_id
WHERE
    i.index_id != 0                               -- not heap
    and OBJECT_SCHEMA_NAME(i.object_id) != 'sys'  -- not system table
GROUP BY
    OBJECT_SCHEMA_NAME(i.object_id),
    OBJECT_NAME(i.OBJECT_ID),
    i.OBJECT_ID,
    i.index_id,
    i.name
ORDER BY
	SUM(au.used_pages) * 8  desc,
    OBJECT_SCHEMA_NAME(i.object_id),
    OBJECT_NAME(i.OBJECT_ID),
    i.index_id
GO


DECLARE @command varchar(MAX) 
SELECT @command = 
    'USE [?] INSERT INTO #temp2
SELECT 
	DB_ID() AS DBID,
	DB_NAME() AS DBName,
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    CASE i.index_id WHEN 1 THEN ''Clustered Index'' ELSE ''NonClustered Index'' END AS IndexType,
    SUM(au.used_pages) * 8 AS ''Indexsize(KB)''
FROM
    sys.indexes AS i
    INNER JOIN sys.partitions AS p
       ON p.OBJECT_ID = i.OBJECT_ID
      AND p.index_id = i.index_id
    INNER JOIN sys.allocation_units AS au
      ON au.container_id = p.partition_id
WHERE
    i.index_id != 0                               -- not heap
    and OBJECT_SCHEMA_NAME(i.object_id) != ''sys''  -- not system table
GROUP BY
    OBJECT_SCHEMA_NAME(i.object_id),
    OBJECT_NAME(i.OBJECT_ID),
    i.OBJECT_ID,
    i.index_id,
    i.name
ORDER BY
	SUM(au.used_pages) * 8  desc,
    OBJECT_SCHEMA_NAME(i.object_id),
    OBJECT_NAME(i.OBJECT_ID),
    i.index_id' 

EXEC sp_MSforeachdb @command
GO

SELECT *
	FROM #temp2
	ORDER BY [Indexsize(KB)] DESC
GO