Post reply

Index Maintenance Question SQL Server 2005

  • September 20, 2006 at 9:58 am

    #153194

    Hi

    I am getting strange results from some index maint and wondered if other folks might have seen the same thing.  Environment is Windows 2003 SP 1 server with SQL Server 2005 ent edition

    Have a table a with a clustered index i1 and a nonclustered index i2.  i1(clustered index) has a fragmentation level of 28.5 and i2(Non-Clustered) has a fragmentation level of 87.5

    I have tried all the index maintenance that I can think of and nothing is changing the fragmentation levels of the indexes.  I tried the reorganize on the non-clustered and tried rebuild on all and nothing changed.  I have also ran the 2000 versions and did the DBCC DBReIndex on the clustered index and tried running a defrag on the non-clustered index.  I have also dropped and re-created the indexes but they show up with the same fragementation levels.

    I wasn't sure if has anyone else seen issues like this or I am just missing something obvious.

    Any help appreciated

    Erich

  • September 21, 2006 at 8:18 am

    #661755

    Sounds really strange. With what method did you collect fragmentation data : sys.dm_db_index_physical_stats or DBCC ShowContig ? What I can suggest is if you used one of these methods then try another one. However in my case both worked fine.

    About defragmentation, you can try the last resort :

    select *

    into another_table

    from existing_table

    with renaming them afterwards.

     

  • September 25, 2006 at 9:05 am

    #662292

    Hi Erich,

    What's the fill factor on the clustered index?

    (btw, you don't know me, but I'm sitting at your old desk

    Lezza

  • September 25, 2006 at 9:14 am

    #662293

    I have tried fill factor of 60, 75%, 90%, and currently 0.

    at 0 fragmentation is 25% after rebuild, at 90% it was over 30%, and 75% it was 28%.

    Did I leave anything good behind?  How about another hint?  🙂

  • September 25, 2006 at 9:21 am

    #662295

    There appears to be a problem with this, as it's been reported on the Microsoft tech forums as well. I've reposted the original request to see if an MVP will pick it up again.

    As for hint, VET really needs to go on a diet.

  • September 25, 2006 at 9:33 am

    #662301

    Thanks for the help.

    It looks like you have been working with some things I was messing around with.  Hopefully it hasn't been too bad cleaning up my messes.

  • March 26, 2007 at 12:48 pm

    #696970

    Did you solve this issue, dm_db_index_physical_stats is NOT getting the latest info ?

    I'm having the same problems with sql 2005 sp1.

  • March 26, 2007 at 3:32 pm

    #697004

    Hi Erich,

    1. How many pages does your index occury ?

    2. Have you tried select * into some_table from your_fragmented_table with creating same inedexes on some_table?

     

     

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply