Index fragmentation not cutting down to 0

  • WonderPra

    SSCommitted

    Points: 1624

    hey people,

    I am a db trainee at my work n i was trying to execute the reindexing maintenance scripts manuallly on a databases.. When i run alter index all on one of the index (which is unknown index and i cant see that on the table also, dont know where it went) Its not defragmenting to 0 or even little less. It is still 93% and i am horrified to see that. Please help.

    --Pra:-):-)--------------------------------------------------------------------------------

  • george sibbald

    SSC Guru

    Points: 104200

    is the table a heap (indid = 0). rebuild would not be able to defragment a heap.

    ---------------------------------------------------------------------

  • WonderPra

    SSCommitted

    Points: 1624

    thanku i just came to know that.. Thanks for the quick response 🙂

    --Pra:-):-)--------------------------------------------------------------------------------

  • george sibbald

    SSC Guru

    Points: 104200

    no problem

    ---------------------------------------------------------------------

  • Grant Fritchey

    SSC Guru

    Points: 395449

    Or if the data is less than an extent in size (8 pages), then it won't fragment either. Generally you shouldn't even bother defragmenting an index until it's at least 100 pages in size. Some recommendations are to wait until it's at least 1000 pages in size.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 994523

    On the other hand, I've found that it almost takes the same amount of time to filter out such tables as it is to try to defrag them because they are so small.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Jeff Moden (1/20/2014)


    On the other hand, I've found that it almost takes the same amount of time to filter out such tables as it is to try to defrag them because they are so small.

    However filtering out tables doesn't contribute towards log backup size, log shipping times, mirroring or always on log transfers.

    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
  • Jeff Moden

    SSC Guru

    Points: 994523

    GilaMonster (1/21/2014)


    Jeff Moden (1/20/2014)


    On the other hand, I've found that it almost takes the same amount of time to filter out such tables as it is to try to defrag them because they are so small.

    However filtering out tables doesn't contribute towards log backup size, log shipping times, mirroring or always on log transfers.

    Gimme a minute while I try to get the egg off my face. :blush: That's absolutely true. Thanks, Gail.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • george sibbald

    SSC Guru

    Points: 104200

    of course, it depends how many small tables you've got. 😉

    ---------------------------------------------------------------------

  • Jeff Moden

    SSC Guru

    Points: 994523

    Heh... I know this is an old post but I've found out through experimentation since Nov 2017 that if you're using REORGANIZE for large tables, then you have much bigger things to worry about when it comes to log backup size, log shipping times, mirroring or always on log transfers. 😉  REORGANIZE on a 146GB table suffering only 12% Logical Fragmentation (well within supposed "Best Practices" of reorging between 10 and 30%) caused the log file to explode from 20GB to 227GB.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

Viewing 10 posts - 1 through 10 (of 10 total)

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