Non-clustered index bigger than clustered index in SQL Server

Question

I have a question regarding an issue I am having.

I have a table that has 2 indexes - a clustered index and one non-clustered index.

The clustered index key column is of type INT
The non-clustered is on a varchar(80) column

After I insert rows to the table, when I check the index stats, I see that the non-clustered index is bigger than the clustered index in terms of size.

When I do an index rebuild, it gets smaller.

Then when I add more rows, it gets bigger again. Why is that?

Note that it's a new table with 0 deletes, is it fragmentation? Is it because of the index key types?

I set fill factor to 0 to reduce fragmentation

It's unsurprising the clustered index is larger, it contains every column in the table, while a non clustered index will only contain the columns it's ordered on and are explicitly INCLUDEd. — Thom A
– Thom A ♦, Commented May 1, 2024 at 13:39
sorry for sorry for the typo, its the other way around, the non clustered is bigger then the clustered, after a Rebuild of the non clustered index it gets normal. and no not using DATA_COMPRESSION — DavDBA
– DavDBA, Commented May 1, 2024 at 14:12
"when I check the index stats" - can you include the means by which you're doing this in your question? Ideally a query of some sort but if it's through a GUI (like SSMS), that's better than nothing. — Ben Thul
– Ben Thul, Commented May 1, 2024 at 14:43
Hello Ben, I Use Brent Ozars sp_BlitzIndex wich gives me the stats but also: SELECT i.[name] AS IndexName ,SUM(s.[used_page_count]) * 8 / 1024.0 AS IndexSizeMB FROM sys.dm_db_partition_stats AS s INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.[index_id] = i.[index_id] WHERE i.[name] IN ('Index_Name Here', 'Index_Name Here') GROUP BY i.[name] — DavDBA
– DavDBA, Commented May 2, 2024 at 6:55

David Browne - Microsoft · Accepted Answer · 2024-05-01 15:24:32Z

1

see that the non-clustered index is bigger than the clustered index in terms of size. ... When I do an index rebuild, it gets smaller.

Probably fragmentation driven by page splits as the nonclustered index gets inserts at many different insert points in the index key ordering.

These will cause leaf-level page splits, causing many or all of the leaf pages to be only a little over 50% full.

answered May 1, 2024 at 15:24

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DavDBA Over a year ago

Thank you David, is there any way of reducing the space so the difference wont be so high other then an Index other then rebuild? I tried lowering the fill factor but it doesn't make a big difference

David Browne - Microsoft Over a year ago

Not really. If you rebuild it, it will just re-fragment.

Collectives™ on Stack Overflow

Non-clustered index bigger than clustered index in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related