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
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).
Unfortunately, in-memory is not an option. Nor is changing the code.
Possibly related info - Server is not in an availability group. Allow Snapshot Isolation is false but is_read_committed_snapshot_on is true.