Post reply

Index Rebuild or Reorganize not working on Clustered index

  • January 29, 2014 at 11:31 pm

    #396192

    I have one really small table just having 25 rows and in that I have primary column with identity.

    when I seen fragmentation(avg_fragmentation_in_percent) on that column it was 50% ,I tried rebuild but no change in fragmentation, than i tried reorganize index but also not worked.

    Can anyone suggest me what to do with that column how can i reduce fragmentation. and why is not happening.

    P.S : I do have 3 to 4 foreign keys in that table but not index on those columns.

  • January 29, 2014 at 11:54 pm

    #1684535

    BriPan (1/29/2014)

    I tried rebuild but no change in fragmentation, than i tried reorganize index but also not worked.

    Can anyone suggest me what to do with that column how can i reduce fragmentation. and why is not happening.

    Fragmentation's not dropping because

    really small table just having 25 rows

    , waaaaay too small for fragmentation to have any effect or rebuilds to do anything. It's probably a 2 page table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 30, 2014 at 12:40 am

    #1684540

    Thanks I got your point..Indeed it is having only 2 page table .

    i have other table also they were having more than 1000 rows and having clustered index and page count

    3 or max 7 . and they also having same problem.

    is it same situation for all those. because some similar tables are having 0 fragmentation. whats the difference.

  • January 30, 2014 at 9:50 am

    #1684751

    Yes its the same problem, the recommendation is usually to ignore fragmentation on indexes with less than 1000 pages, although I find in practice I am not worried on anything slightly over that either.

  • January 30, 2014 at 10:43 am

    #1684781

    BriPan (1/30/2014)

    i have other table also they were having more than 1000 rows and having clustered index and page count

    3 or max 7 . and they also having same problem.

    Yup.

    waaaaay too small for fragmentation to have any effect or rebuilds to do anything.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

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