SQL Server Performance

Index fragmentation 3,333.33%?????

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by nicholasW, Feb 1, 2010.

  1. nicholasW New Member

    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
  2. Luis Martin Moderator

    Welcome to the forums!
    Did you check integrity for that table?
  3. nicholasW New Member

    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]
  4. preethi Member

    Do you have any updates happen against clustered index keys?
  5. Luis Martin Moderator

    Nope, I mean: DBCC checktable.
  6. rohit2900 Member

    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.

Share This Page