Index rebuild and fragmentation level

  • I ran the following command to rebuild an index.

    alter index inx_charges1 on charges

    rebuild

    Then I ran the following command to check the statistics.

    Why do I get a fragmentation percentage of 100% ( which is bad ) after rebuilding the index ?

    select index_id,avg_fragmentation_in_percent, page_count,

    index_level, index_depth,

    avg_page_space_used_in_percent,object_id,*

    FROM

    sys.dm_db_index_physical_stats( DB_ID('Ngprod'), Object_ID('Charges'),

    NULL,NULL, 'DETAILED' )

    WHERE

    index_id <> 0

    I just copied a portion of the output for index_id=2

    Question: So why is it at index_level 2 we have a

    fragmentation percent of 100% ( very bad )

    index_id avg_fragmentation page_count index_level

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

    2 4.33088369903497 38237 0

    2 100 233 1

    2 0 1 2

    (3 row(s) affected)

  • Hi,

    Because that sounds so very strange I just have to ask you; is inx_charges1 really indid=2 of that table?

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • Yes

    This table has about 18 indexes. I tried several of them ( Tried the Alter Index command using other indexes ) and some of them returned the same result. That is, at the INDEX LEVEL 2 the fragmentation percent is 100% ?

    By th way, we do use a raid system However, the table is not a partiioned table ( for sure ).

  • Did you try DBCC and check for more information on fragments?

  • Not sure how to use the DBCC command. Any advice ?

    Also this is a large table I don't want to slow down the system

    if this command is resource intensive ?

    Anyway send us the command please ?

  • use this : DBCC SHOWCONTIG(tablename)

    In the results check for the logical scan fragmentation, which ideally should be 0.01%

    Thanks!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • Yes it may slow down the performance, as it uses parallel sessions to execute the DBCC.

    So execute it after peak hours. It took 5 mins in my system for the big table.

    DBCC SHOWCONTIG (tablename)

  • use DBCC SHOWCONTIG(tablename) WITH FAST .

    This will not lock the table while execution and also gives you results quicker(in seconds).

    Then check logical scan fragmentation .It should be less than 1% for an index which was just rebuilt.

  • It may be that your indexes are so small, the rebuild process doesn't do anything to help with fragmentation. Below a certain point, fragmentation is essentially disregarded because the entire index will end up being loadd in memeory anyway.

    Paul Randal (who's the resident authority on the matter) mentioned something like anything less than 1,000 pages in the index probably wouldn't defrag.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • #1 The table is very large ( well above a million rows ).

    #2 If the page count in small ( less than 1000 ) you said it would not fragment. So why does it then give a 100% ?

    Take a look at the output. At the index level 1 it is 0%

    This is clear since there is only one page involved.

    If you say that since the page count is small ( less than 1000 ) why did it say 100% ? It could have said 0% as well?

    Also before I ran the "Alter index command... " I didn't see 100% on any of the indexes at any level ?

    This is confusing ?

  • 1. If I'm reading your results correctly - your index is only 233 pages big. So - while the table might be large - your index is apparently not.

    2. I said that if it's below 1000, it would not DEfragment. Meaning - it will ALWAYS show as fragmented. The way the algorithm works (according to Paul again), it will figure out that there will be no benefit in defragmenting it, so it won't.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Hello

    The index is not small and not just 233 pages. Take a look. Only level 2 of the index has 233 pages. Level 3 has 38237 pages.

    So it is a large index ( because it has several levels )

    Take a look at the OUTPUT that is pasted below.

    Ok, so you are saying when the # pages is less than 1000, the algorithm does not even bother about defragmenting.

    Fine! Well then, before I ran the "Alter index command.." why did it

    show a value between 0 and 100 ( can't remember the exact value ) ?

    Here is the output

    index_id avg_fragmentation page_count index_level

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

    2 4.33088369903497 38237 0

    2 100 233 1

    2 0 1 2

    (3 row(s) affected)

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

  • Hi,

    I can see how it would be true for a handful of pages (less than 8) but since the index is created from scratch, I for one cannot see why the pages would be allocated from all over the place.

    You mentioned it happens for ALL tables (with more or equal than 3 levels) and always index level 2. Is it so that if there are more levels, it is still just level 2? I suppose some of them have clustered indexes and some are heaps, or?

    I am still very puzzled, do you get the same result if you do CREATE INDEX .... WITH DROP_EXISTING?

    Which service pack level are you on?

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • Hello

    This is a production database. I am not allowed to drop indexes and create them from scratch.

    I guess the previous reply is correct to assume. He said when the

    number of pages is less than 1000, SQl server does not bother to defragment the index ( I mean that part of the index that sits on level 2 )

    I am going to do a few more ( I mean index rebuilds ) today and find out whether the results are consistent with what he says.

  • Look below for the difference in the way you use the dbcc showcontig command

    --DBCC SHOWCONTIG(TABLE_PROD_RES)

    DBCC SHOWCONTIG scanning 'TABLE_PROD_RES' table...

    Table: 'TABLE_PROD_RES' (1241211622); index ID: 1, database ID: 5

    TABLE level scan performed.

    - Pages Scanned................................: 748041

    - Extents Scanned..............................: 93513

    - Extent Switches..............................: 93526

    - Avg. Pages per Extent........................: 8.0

    - Scan Density [Best Count:Actual Count].......: 99.98% [93506:93527]

    - Logical Scan Fragmentation ..................: 0.01%

    - Extent Scan Fragmentation ...................: 10.43%

    - Avg. Bytes Free per Page.....................: 351.9

    - Avg. Page Density (full).....................: 95.65%

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    --DBCC SHOWCONTIG(TABLE_PROD_RES) WITH FAST

    DBCC SHOWCONTIG scanning 'TABLE_PROD_RES' table...

    Table: 'TABLE_PROD_RES' (1241211622); index ID: 1, database ID: 5

    TABLE level scan performed.

    - Pages Scanned................................: 748041

    - Extent Switches..............................: 93526

    - Scan Density [Best Count:Actual Count].......: 99.98% [93506:93527]

    - Logical Scan Fragmentation ..................: 0.01%

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    The WITH FAST doesn't mention the Avg. Page density which is needed if you want to REINDEX with a FILL FACTOR other than 100%, this is applicable if you table is updated quite often and is not static.

    If not then 'WITH FAST' is much faster..thanks for showing this QUICK CHECK kinda thing..:)

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Viewing 15 posts - 1 through 15 (of 25 total)

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