Heap rebuilds

  • vsamantha35

    SSChampion

    Points: 11038

    Hi All,

    We are seeing heavy fragmentation 99% with page count more than 10000 pages.

    So, rebuilding those HEAP Tables using ALTER table tname REBUILD command.

    My question is, if I have 100 such tables which of the below 2 would perform better in terms of performance (time, transaction log management etc..)

    Database is in FULL recovery model. SQL Server 2012 Enterprise Edition.

    ALTER TABLE T1 REBUILD;

    ALTER TABLE T2 REBUILD;

    ALTER TABLE T3 REBUILD;

    ALTER TABLE T4 REBUILD;

    :

    ALTER TABLE T100 REBUILD;

    ------------- [OR]-----------------

    ALTER TABLE T1 REBUILD;

    GO

    ALTER TABLE T2 REBUILD;

    GO

    ALTER TABLE T3 REBUILD;

    GO

    ALTER TABLE T4 REBUILD;

    GO

    :

    ALTER TABLE T100 REBUILD;

    GO

    Thanks,

    Sam

  • Site Owners

    SSC Guru

    Points: 80380

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Jeff Moden

    SSC Guru

    Points: 994661

    To answer your question, there will be no difference in performance or resource usage.  The only difference is that the first rendition could be created as a stored procedure and the second cannot because of the "GO" batch separators.

    Shifting gears a bit, the real key here is to figure out why you have so much fragmentation and fix it especially on HEAPs and CLUSTERED INDEXes.  Such massive fragmentation on HEAPs is normally due to ExpAnsive Updates.  On Clustered Indexes, such massive fragmentation is usually caused by ExpAnsive Updates and/or Out-of-Order Inserts.

    Another key is to determine if the 99% fragmentation is actually causing you performance issues.  For mostly single row lookups, logical fragmentation just won't matter.  You also need to look at what people refer to as "Physical Fragmentation", which is a misnomer for "Page Density" because it can be a VERY big deal in the form of totally wasted memory and disk space.

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

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