Post reply

Serious Index fragmentation questions

  • March 14, 2015 at 3:01 pm

    #305762

    I have a almost 2 TB database in the database, there is a huge table 'Mytable' around 850 GB. i have a Index rebuild job setup " > 30% Rebuild" using sys.dm_db_physical_statas "null" mode, which is I believe 'Limited' and I think "limited" only scan 'leaf' level pages.

    When I right click on the index to check fragmentation of the table using GUI, it shows the Fragmentation is less than 1%, BUT when I run sys.dm_db_physical_stats using 'detailed' I get 100% on the same index. I think 'detail' scans 'root' and 'intermediate' level.

    My questions are:

    1. Should I keep using 'limited' even though it doesn't show the level of the fragmentation which 'detailed' would have shown?

    2. Is it just OK to just rebuild 'leaf' level fragmentation?

  • March 14, 2015 at 8:17 pm

    #1784080

    What's in this table? Is it by any chance something like a log, audit, or invoice detail table?

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

  • March 15, 2015 at 5:13 am

    #1784091

    Hi Jeff,

    The table contains records which uses, xml, LOB, uniquidentifier.... datatypes.

  • March 15, 2015 at 2:30 pm

    #1784130

    Understood. Any kind of a table can have those things. What I'm trying to find out is what is the "business reason" for the table. In other words, what is the table used for? Is it a "mostly static" table like an audit, log, or invoice detail table where older rows are never changed or is it an active OLTP table where any and all rows could change?

    The reason I'm asking is because it will make a huge difference in anything that I think should be a recommendation for such a large table. And, to be sure, such large tables usually fall into the "mostly static" category where old rows are not updated and only those rows in the last 30 days or so need to be updated.

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

  • March 16, 2015 at 6:29 am

    #1784231

    Hi Jeff,

    The table is dynamic and active OLTP.

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

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