SQL SERVER – Size of Index Table for Each Index – Solution

Earlier I have posted small question on this blog and requested help from readers to participate here and provide solution. Please read the original Puzzle here. Readers sent in answers, and two queries show the size of each index quite accurately. Both queries return the size of the index table for each index on a table.

SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table

The puzzle was to write a query that will return the size for each index that is on any particular table. We need a query that will return an additional column in the above listed query and it should contain the size of the index.

So far I have found two potential solutions. I have done some good amount of testing of both the script and they both are really giving accurate results 99.9% of the time. There were couple of instances where it provided incorrect results but for the most of the time, it just worked like charm. Again, it may be a bit of rounding up logic and I will not hold it against the script.

I want to thank Gaurav Sharama and Adam Hutson for their participation.

Solution from Gaurav Sharma

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
GO

Solution from Adam Hutson

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
GO

If you have different script or have suggestions on this script, please let us know.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.