Issue in reducing index fragmentation

  • Hi,

    I have created a non-unique, non cluster index in one of the table. But when I run the query in my database

    select * from sys.dm_db_index_physical_stats(db_id('Inventory'),null,null,null,null)

    I can see that there is avg_fragmentation_in_percent of 75 for the newly created index. Ideally it should not have any fragmentation. Even after rebuilding and reorganizing the index also, the fragmentation is not getting reduced. It is showing the same.

    Can anyone please explain me the reason?

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • how many rows are in the table?

  • How big's the table? How many pages?

    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
  • Also,

    Object have cluster index ?

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • muthukkumaran (2/16/2010)


    Object have cluster index ?

    It's not required that a table have a clustered index in order to defragment a nonclustered index.

    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
  • GilaMonster (2/16/2010)


    muthukkumaran (2/16/2010)


    Object have cluster index ?

    It's not required that a table have a clustered index in order to defragment a nonclustered index.

    Yes I agreed Gail.

    The OP create a Non clustered index,so i guess the table was Heap.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • muthukkumaran (2/16/2010)


    The OP create a Non clustered index,so i guess the table was Heap.

    How do you conclude that?

    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
  • Just guess....:-):-P

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Hi All ,

    sorry for the late reply. Actually the table is very small it has only 399 rows and size is 234MB. Previously it was a heap table. Just for testing purpose I created a non-unique, non-clustered index on that table.

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • You shouldn't worry about fragmentation on a table with that few rows.

    MS recomends that you should not be concerned with fragementation of indexes with less than 1000 pages

  • 234 MB with only 400 rows? Sure it's not 234 kb? How many pages does the index (not the table) have?

    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
  • Ok. But can you explain me why the avg_percent_in_fragmentation is not getting reduced for this scenario. For the same column if I create a clustered index in stead of non clustered index, I dont find any fragmentation.

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • Hi Gail,

    Sorry. It is 234KB. I typed wrongly.

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • The index storage space is 55KB.

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • Your index is less than 1 extent (8 pages) in size. There's no point in rebuilding that and, considering the way SQL allocates pages for very very small indexes, rebuilding it won't change the fragmentation anyway.

    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 15 posts - 1 through 14 (of 14 total)

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