So we have a table that keeps going absolutely nuts. About once every 1-2 months it basically loses it's index and the fragmentation on that index is listed in the high %'s. I don't know if this is because it is @ 100% and not functioning correctly... The table is 2 columns and about 1200 rows(not big by any standards). It is realatively easy to remove any locks on the table and rebuild the index which bumps it back to 0% but how would i avoid this situation all together. Please note is a highly, highly used table and is queried quite a bit. Our DB is poorly designed and not my design so currently it is not feasable to rework anything. Also the index is currently non-clustered. I don't know if that has any effect or if i should be changing it or not. Heck i don't even know if we should have an index on this table it is quite small. Anymore info needed let me know. My SQL troubleshooting is quite minimal so even if i could be pointing in the direction i can do the work and report the results. Thanks!!! Nicholas W
by integrity you mean keys, contraints etc right? If so it has 2 columns that are not null, 1 char and one int. No keys, contraints, triggers and the index is :USE [Scangas] GO/****** Object: Index [indx_webclientsaccountno] Script Date: 02/01/2010 15:34:03 ******/CREATE NONCLUSTERED INDEX [indx_webclientsaccountno] ON [dbo].[WebClients] ([AccountNo] ASC) ON [PRIMARY]
Nick...Can you post the table definition along with the indexes and as Preeti suggested what is the amount of data updated in this table or is it just for select something like lookup table...Yeah it's true that table with 1200 records is not at all big, but we're just trying to understand as how this table is being use.. which will enable us to give more accurate suggestions.