Oversize index

%3CLINGO-SUB%20id%3D%22lingo-sub-2183876%22%20slang%3D%22en-US%22%3EOversize%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2183876%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20with%20220%20rows.%20It%20has%20about%2020%20columns.%20One%20of%20the%20indexes%20has%20a%20datetime%20as%20the%20key%20value%20and%20includes%20a%20unique%20identifier%20and%20a%20varchar(128).%20The%20PK%20is%20a%20bigint.%3C%2FP%3E%3CP%3EThe%20datetime%20for%20every%20row%20is%20updated%20very%20frequently%20(40%20times%20a%20second).%3C%2FP%3E%3CP%3EIf%20I%20rebuild%20the%20index%20it%20is%20about%20200KB%20but%20after%20a%20couple%20of%20hours%2C%20it%20will%20have%20grown%20to%20about%2050MB.%20(And%20it%20appears%20to%20keep%20growing).%20Given%20how%20often%20it%20is%20accessed%2C%20scanning%2050MB%20compared%20with%20200KB%20is%20hurting%20a%20lot.%26nbsp%3BSQL%202016%20SP2%20%3CSPAN%3E13.0.5201.2%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ECan%20anyone%20explain%2C%20or%20point%20me%20to%20something%20that%20explains%2C%20how%20an%20index%20with%20only%203%20columns%2C%20on%20a%20200%20row%20table%20can%20get%20to%20be%2050MB%3F%20And%20even%20better%20anything%20I%20can%20do%20about%20this%3F%20(The%2040%20updates%20a%20second%20is%20not%20something%20happy%20with%20but%20I%20haven't%20been%20able%20to%20convince%20anyone%20that%20this%20is%20excessive%20yet..).%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EBruce%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2183968%22%20slang%3D%22en-US%22%3ERe%3A%20Oversize%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2183968%22%20slang%3D%22en-US%22%3EHello%20Bruce%2C%3CBR%20%2F%3EIs%20that%20a%20clustered%20index%3F%20Then%20fragmentation%20can%20be%20the%20cause%20for%20the%20high%20index%20size.%3CBR%20%2F%3EDefrag%20the%20index%20daily%20or%20change%20it%20to%20a%20non-clustered%20index.%3CBR%20%2F%3E%3CBR%20%2F%3EOlaf%3C%2FLINGO-BODY%3E
New 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.