[FIXED] Why is the index size so unjustifiably large?

Issue

I have a table with 280k rows. It has a clustered primary key. When I look at the size of the primary key index, it reports 5.8 GB. Far too big for 280k rows.

SELECT i.name AS IndexName, SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.SessionSignIn'), NULL, NULL, 'DETAILED') AS s
    JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE i.name = 'PK_SessionSignIn'
GROUP BY i.name

So I ran the Index Physical Stats report from SSMS and got the following:

It looks like there are 2 parts to this index and the latter part appears to take all the space. I tried rebuilding the index, then reorganizing it, but it didn't change anything.

What is the portion that is taking up all the space? How do I get rid of it?

Solution

Clustered index is the table.

You can have a look at this article to see how the size is calculated:

https://learn.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-clustered-index?view=sql-server-ver16

Answered By - Alex

Answer Checked By - Katrina (PHPFixing Volunteer)