Today I was trying to calculate the size of a specific index in SQL Server and realized it's not obvious to calculate.  I poked around Management Studio and was able to get the size of all indexes for a table, but not for a single index.  Thinking I must have just missed something obvious, I did a quick Live (and then Google) search and didn't get any useful results.  Frustrated, I started digging into the documentation and came upon this handy dynamic management view: sys.dm_db_index_physical_stats.  It provides enough information that you can calculate the size of the index.

Here is the handy stored procedure I wrote to calculate the size of an index in bytes:

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