Post reply

DBCC DBREINDEX and Logical Fragmentation

  • May 2, 2008 at 3:32 pm

    #380305

    Greetings,

    Running DBCC SHOWCONTIG WITH TABLERESULTS showed that I had several indexes with logical fragmentation in the 40's and a few at 100. When I ran DBCC DBREINDEX on these tables the ones in the 40's dropped to 0, some of the ones with 100 dropped to zero, some of the ones at 100 remained at 100 and some that were 0 rose to 100. How/why did reindexing change from 0 to 100?:blink:

    Thanks,

    Tim

  • May 4, 2008 at 3:30 pm

    #810483

    How big are those tables? 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

  • May 5, 2008 at 12:36 pm

    #810843

    They are very small tables, 1 page and a dozen to 2 dozen rows. I doubt they are a performance issue, but why does the Logical Fragmentation go to 100?

    Thanks,

    Tim

  • May 5, 2008 at 12:59 pm

    #810863

    Because fragmentation has very little meaning on such small tables. Logical fragmentation is defined as the number of out of order pages. With only 1 page in the table it's meaningless.

    Don't worry about it.

    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

  • May 5, 2008 at 1:03 pm

    #810867

    Thanks, it was more curiosity not worry 😀

  • May 5, 2008 at 1:41 pm

    #810897

    GilaMonster (5/5/2008)

    Because fragmentation has very little meaning on such small tables. Logical fragmentation is defined as the number of out of order pages. With only 1 page in the table it's meaningless.

    Don't worry about it.

    How large do tables have to be to start showing effects of fragmentation (in rows or pages)?

    http://90.212.51.111 domain

  • May 5, 2008 at 2:16 pm

    #810908

    I can't remember offhand. I did read it somewhere.

    I think it's around 100 pages, but don't quote me on that. I'll see if I can find the source article tomorrow.

    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

  • May 5, 2008 at 3:56 pm

    #810967

    Thanks

    http://90.212.51.111 domain

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

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