Method1 ,
CREATE
PROCEDURE [dbo].[IndexSize]
@TableName
NVARCHAR(256),
@IndexName
VARCHAR(256)
AS
BEGIN
DECLARE @index_id INT
DECLARE @index_size BIGINT
SET @index_size = 0
SELECT @index_id = index_id
FROM sys.indexes
WHERE object_id
= OBJECT_ID(@TableName)
AND name
= @IndexName
SELECT
@index_size
= @index_size +
(avg_record_size_in_bytes * record_count)
FROM sys.dm_db_index_physical_stats
(DB_ID(),
OBJECT_ID(@TableName), @index_id
, NULL,
'DETAILED')
SELECT @index_size as IndexSizeBytes
END
Method2 ,
SELECT i.name
AS IndexName,
SUM(s.used_page_count)
* 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats
AS s
JOIN sys.indexes
AS i
ON s.[object_id]
= i.[object_id]
AND s.index_id = i.index_id
WHERE s.[object_id]
= object_id('dbo.TableName')
GROUP BY i.name
ORDER BY i.name
Method3 ,
SELECT
i.name
AS IndexName,
SUM(page_count *
8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.TableName'),
NULL, NULL,
'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id]
= i.[object_id]
AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name