Oversize index

Copper Contributor

I have a table with 220 rows. It has about 20 columns. One of the non-clustered indexes has a datetime as the key value and includes a unique identifier and a varchar(128). The clustered PK is a bigint.

The datetime for every row is updated very frequently (40 times a second).

If I rebuild the index it is about 200KB but after a couple of hours, it will have grown to about 50MB. (And it appears to keep growing). Given how often it is accessed, scanning 50MB compared with 200KB is hurting a lot. SQL 2016 SP2 13.0.5201.2

Can anyone explain, or point me to something that explains, how an index with only 3 columns, on a 200 row table can get to be 50MB? And even better anything I can do about this? (The 40 updates a second is not something happy with but I haven't been able to convince anyone that this is excessive yet..).

Thanks,

Bruce

2 Replies
Hello Bruce,
Is that a clustered index? Then fragmentation can be the cause for the high index size.
Defrag the index daily or change it to a non-clustered index.

Olaf

Hi @olafhelper , Nope, it is a non-clustered index. The primary key is the clustered index.