Indexes play a crucial role in optimizing the performance of SQL Server databases. They allow for faster data retrieval by creating a separate data structure that organizes and sorts the data in a table. However, it is important to monitor the size of indexes to ensure efficient storage utilization.

In this article, we will discuss how to find the size of indexes on a specific table in SQL Server. We will explore two potential solutions that have been tested and proven to provide accurate results.

Solution 1: Using sys.dm_db_partition_stats

The first solution involves using the sys.dm_db_partition_stats system view to gather information about the indexes on a table. Here is an example query:

DECLARE @OBJECT_NAME VARCHAR(255) = 'HumanResources.Shift';
DECLARE @temp TABLE (
    indexID BIGINT,
    objectId BIGINT,
    index_name NVARCHAR(MAX),
    used_page_count BIGINT,
    pages BIGINT
)

-- Insert into temp table
INSERT INTO @temp
SELECT P.index_id, P.OBJECT_ID, I.name, SUM(used_page_count), SUM(
    CASE
        WHEN (p.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
        ELSE lob_used_page_count + row_overflow_used_page_count
    END
)
FROM sys.dm_db_partition_stats P
INNER JOIN sys.indexes I ON I.index_id = P.index_id AND I.OBJECT_ID = P.OBJECT_ID
WHERE p.OBJECT_ID = OBJECT_ID(@OBJECT_NAME)
GROUP BY P.index_id, I.Name, P.OBJECT_ID;

SELECT index_name INDEX_NAME, LTRIM(STR((
    CASE
        WHEN used_page_count > pages THEN (used_page_count - pages)
        ELSE 0
    END) * 8, 15, 0) + ' KB') INDEX_SIZE
FROM @temp T;

This query retrieves the index information from sys.dm_db_partition_stats and calculates the size of each index based on the used_page_count and pages columns. The result is displayed in kilobytes (KB).

Solution 2: Using sys.indexes and sys.dm_db_partition_stats

The second solution involves joining the sys.indexes and sys.dm_db_partition_stats system views to obtain the index size. Here is an example query:

DECLARE @objname NVARCHAR(776), @id INT, @dbname sysname
SELECT @objname = 'HumanResources.Shift', @dbname = ISNULL(PARSENAME(@objname, 3), DB_NAME()), @id = OBJECT_ID(@objname)

SELECT i.*, CASE
    WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages)
    ELSE 0
END * 8 indexsize
FROM sys.indexes i
INNER JOIN (
    SELECT OBJECT_ID, index_id, SUM(used_page_count) usedpages, SUM(
        CASE
            WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END
    ) pages
    FROM sys.dm_db_partition_stats
    WHERE OBJECT_ID = @id
    GROUP BY OBJECT_ID, index_id
) ps ON i.index_id = ps.index_id
WHERE i.OBJECT_ID = @id;

This query retrieves the index information from sys.indexes and sys.dm_db_partition_stats and calculates the index size based on the usedpages and pages columns. The result is displayed in kilobytes (KB).

Both solutions provide accurate results in determining the size of indexes on a specific table. However, it is important to note that there may be slight variations in the results due to rounding up logic.

Monitoring the size of indexes is essential for maintaining optimal database performance. By regularly checking the index size, you can identify any potential storage issues and take appropriate actions to optimize the database.

If you have any alternative scripts or suggestions for finding the index size, please feel free to share them with us.