Post reply

Index Fragmentation

  • August 30, 2012 at 5:27 pm

    #280911

    Hi,

    Can anyone please confirm which is correct...

    After Rebuild indexes, still some of the table indexes fragmentation level shows high.

    We basically shouldn't bother with index fragmentation unless the table has more than 1000 pages as it will most probably be entirely contained in cache already and so fragmentation is irrelevant.

    Rebuild the index only if there is atleast 1000 pages affected. If < 1000 pages, they say fragmentation will not really be affected and potentially would remain high.

    [OR]

    Actually, when we create a table and start inserting rows, SQL Server initially allocates pages from mixed extents until it has enough data to deserve a full extent, then SQL Server will allocate a uniform extent to it. Similarly if you build an index on a table that have fewer then eight pages SQL Server will allocate pages from mixed extents for storing the index data. And if these mixed extents are not located side by side then database management view sys.dm_db_index_physical_stats will show HIGH external fragmentation. So no need to worry about fermentation of clustered index of small tables which have fewer then eight pages.

    Thanks..

  • August 31, 2012 at 1:06 pm

    #1532613

    Can anyone please reply to this post..

  • August 31, 2012 at 2:04 pm

    #1532628

    Both are correct.

    p.s. We're volunteers. We answer your questions in our spare time. Please be patient. Bumping your post after 4 hours is unnecessary

    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

  • August 31, 2012 at 4:21 pm

    #1532659

    Thanks allot for your reply Gail.

    Still i am in confusion,

    In the first case, less than 1000 pages--> we can ignore

    second case : less than 8 pages ---> we can ignore

    **************

    Second case :

    when we create a table and start inserting rows, SQL Server initially allocates pages from mixed extents until it has enough data to deserve a full extent, then SQL Server will allocate a uniform extent to it. Similarly if you build an index on a table that have fewer then eight pages SQL Server will allocate pages from mixed extents for storing the index data. And if these mixed extents are not located side by side then database management view sys.dm_db_index_physical_stats will show HIGH external fragmentation. So no need to worry about fermentation of clustered index of small tables which have fewer then eight pages.

    What about in between 8 pages to 1000 pages ?

    **************

    Finally if the Page_Count is less than 1000...then we can ignore the fragmentation level.

    Is that correct?

  • September 1, 2012 at 3:37 am

    #1532669

    As I said, they're both correct. Once says ignore < 8, one says ignore < 1000. Since they're both correct and one just gives a higher threshold, then use the higher threshold.

    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

  • September 1, 2012 at 12:26 pm

    #1532696

    vamshi.sql (8/30/2012)

    Hi,

    Can anyone please confirm which is correct...

    After Rebuild indexes, still some of the table indexes fragmentation level shows high.

    We basically shouldn't bother with index fragmentation unless the table has more than 1000 pages as it will most probably be entirely contained in cache already and so fragmentation is irrelevant.

    Rebuild the index only if there is atleast 1000 pages affected. If < 1000 pages, they say fragmentation will not really be affected and potentially would remain high.

    [OR]

    Actually, when we create a table and start inserting rows, SQL Server initially allocates pages from mixed extents until it has enough data to deserve a full extent, then SQL Server will allocate a uniform extent to it. Similarly if you build an index on a table that have fewer then eight pages SQL Server will allocate pages from mixed extents for storing the index data. And if these mixed extents are not located side by side then database management view sys.dm_db_index_physical_stats will show HIGH external fragmentation. So no need to worry about fermentation of clustered index of small tables which have fewer then eight pages.

    Thanks..

    To be honest, I think that a lot of people spend way too much time trying to figure out what to rebuild and what to reorganize. If you have the Enterprise Edition, most indexes (those not having blobs or a couple of other things) can be rebuilt online. Rebuilding indexes will also update statistics so there's another thing that you wouldn't have to worry about.

    It's not a big database but, as an example, it only takes 30 minutes to rebuild all of the indexes on one of our 50GB databases at work in an online fashion. Figuring out which indexes and statistics need to be rebuilt or reorganized takes a good 10-12 minutes anyway. Admittedly, I have a "quiet" time on the database on Sunday evenings that many folks may not have but that allows me to just rebuild everything whether it needs it or not.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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