Determine fragmentation of HEAP table

  • Hi,

    AFAIK, A heap table can be fragmented because of page splits, which results in forward lookups.

    Is there a way to calculate this fragmentation?

    Wilfred
    The best things in life are the simple things

  • As per books online explanation about DBCC SHOWCONTIG heaps fragmentation is never reported. Also, heaps can never be defragged. Only one option is thr to defrag them:

    Drop and recreate the table and load the previous data(before dropping the original table) back into it.

    Manu

  • It's much simpler to add a clustered index... doesn't have to be unique, you know? 😉

    --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)

  • Or you can convert an existing non-clustered index to clustered using the "DROP_EXISTING" option. Note that you cannot convert a clustered index to a non-clustered index.

    SQL = Scarcely Qualifies as a Language

  • I know this can be fixed by a clustered index, but I want to see what the situation is before I add this index (I'm struggeling with application administrators for adding clustered indexes)

    But I found the answer: it's a column in sys.dm_db_index_physical_stats :

    SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED')

    where index_type_desc = 'HEAP'

    and forwarded_record_count > 0

    Wilfred
    The best things in life are the simple things

  • Nice... thanks for that tip!

    --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)

  • First you can create cluster index .

    and run the appropriate operation(Like index defragmentation)

    after that You can drop the Cluster index.

    Anurag

  • Wilfred van Dijk (5/26/2008)


    Hi,

    AFAIK, A heap table can be fragmented because of page splits, which results in forward lookups.

    Is there a way to calculate this fragmentation?

    Heaps don't suffer page splits. Page splits occur when a row must be inserted onto a page (because of index key order) and it won't fit, or if a row on a page increases in size and will no longer fit.

    In both those cases, with an index (clustered or otherwise), the page is split, half the rows moved to a new page and the new page linked into the index chain.

    With a heap, a row never has to be inserted into a specific page, because the order doesn't matter. Rather rows are inserted into any page that has space. If a row grows and no longer fits onto the page, then in a heap, that row alone is moved to a page that does have space, and a forwarding pointer is left in its place pointing to where the row was moved to.

    Forwarding pointers are a problem, because they have to be honoured (and followed) whenever they are encountered. So, if a heap has 500 pages and there is a single forwarding pointer on each page, all pointing to the 500th page of that heap, that page has to be read each time a forwarding pointer is encountered.

    One of the signs of large numbers of forwarding pointers is when logical reads for a read of the entire table increase steadily, with no change to the number of rows.

    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
  • Thanks for your input. Thus: forwarding pointers can only occur on heaps, while page splits only occur on indexes?

    Wilfred
    The best things in life are the simple things

  • Wilfred van Dijk (9/23/2008)


    Thanks for your input. Thus: forwarding pointers can only occur on heaps, while page splits only occur on indexes?

    Pretty much.

    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
  • Are forward pointers always limited to the same physical file, or can you point to a location in another file (within the same filegroup)?

    Wilfred
    The best things in life are the simple things

  • As far as I'm aware, it can be anywhere in the same filegroup.

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

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