SQL Server Performance

INDEXDEFRAG vs DBREINDEX

Discussion in 'Performance Tuning for DBAs' started by ykchakri, Sep 8, 2003.

  1. ykchakri New Member

    Hi,

    Are there any significant advantages with DBREINDEX over INDEXDEFRAG ? Assuming so, I've decided to do INDEXDEFRAG once every week and DBREINDEX once every month. But, What I'm observing is, with DBREINDEX the logical fragmentation of all indexes is jumping close to 100% and the Scan density of some of the indexes does not show any improvement at all. Whereas after an INDEXDEFRAG, the Scan Density of all indexes is close to 100% and the logical fragmentation is close to 0%.

    Do you know why DBREINDEX is doing bad to indexes instead of doing any good ? And if this is the way it will do, I don't see any reasons why I should take any extra pains of performing DBREINDEX instead of INDEXDEFRAG.

    Before DBREINDEX:
    DBCC SHOWCONTIG scanning 'FinAct' table...
    Table: 'FinAct' (1993058136); index ID: 1, database ID: 14
    TABLE level scan performed.
    - Pages Scanned................................: 340175
    - Extent Switches..............................: 67409
    - Scan Density [Best Count:Actual Count].......: 63.08% [42522:67410]
    - Logical Scan Fragmentation ..................: 2.50%
    DBCC SHOWCONTIG scanning 'FinAct' table...
    Table: 'FinAct' (1993058136); index ID: 4, database ID: 14
    LEAF level scan performed.
    - Pages Scanned................................: 66090
    - Extent Switches..............................: 8557
    - Scan Density [Best Count:Actual Count].......: 96.54% [8262:8558]
    - Logical Scan Fragmentation ..................: 0.70%

    After DBREINDEX:
    DBCC SHOWCONTIG scanning 'FinAct' table...
    Table: 'FinAct' (1993058136); index ID: 1, database ID: 14
    TABLE level scan performed.
    - Pages Scanned................................: 335182
    - Extent Switches..............................: 42893
    - Scan Density [Best Count:Actual Count].......: 97.68% [41898:42894]
    - Logical Scan Fragmentation ..................: 90.63%
    DBCC SHOWCONTIG scanning 'FinAct' table...
    Table: 'FinAct' (1993058136); index ID: 4, database ID: 14
    LEAF level scan performed.
    - Pages Scanned................................: 66620
    - Extent Switches..............................: 9268
    - Scan Density [Best Count:Actual Count].......: 89.85% [8328:9269]
    - Logical Scan Fragmentation ..................: 56.25%
  2. gaurav_bindlish New Member

    BOL says - "Unlike DBCC DBREINDEX (or the index building operation in general), DBCC INDEXDEFRAG is an online operation. It does not hold locks long term and thus will not block running queries or updates."

    DBCC REINDEX is better in defragmentation than INDEXDEFRAG. For the problem that you have mentioned, can you specify the options for DBCC REINDEX that you are using?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. ykchakri New Member

    quote:Originally posted by gaurav_bindlish

    BOL says - "Unlike DBCC DBREINDEX (or the index building operation in general), DBCC INDEXDEFRAG is an online operation. It does not hold locks long term and thus will not block running queries or updates."
    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard


    Yes, and this is the reason that I'm able to run it once every week without affecting our 24x7 environment. And not only that, it takes up less free space for INDEXDEFRAG than DBREINDEX.

    Here is the syntax that I'm using for DBREINDEX:
    sp_MSforeachtable @command1 = 'print ''?'' DBCC dbreindex(''?'','''',90)'
  4. Luis Martin Moderator


    I suggest: default for cluster (90%) and 70 or 80 for non cluster.




    Luis Martin
  5. ykchakri New Member

    quote:Originally posted by Luismartin_ar@yahoo.com


    I suggest: default for cluster (90%) and 70 or 80 for non cluster.




    Luis Martin

    Thanks Luis, And do you think changing the fill factor to 70 or 80% can help me solve my actual problem with DBREINDEX.
  6. sqljunkie New Member

    Does the filegroup that contains these objects have multiple files?
  7. vbkenya New Member

    I don't think the change in the fill factor percentage will make significant changes DBREINDEX's performance in your case. This will only affect the initial internal page density.

    After DBREINDEX you seem to be reclaiming a bit of space (although your output is rather incomplete) as a result of extent deallocations and page migration but with no positive change in external fragmentation. My guess is that the database has a lot of free (deallocated) and scattered extents and/or freed pages that are being used to recreate your indexes during the DBREINDEX operation.

    I would suggest running a DBCC SHRINKFILE on the file that contains your table and indexes before performing the DBCC DBREINDEX. This way it is more likely that reindexing will allocate physically contigous extents (and pages) for the clustered index and the dependent non-clustered indexes.

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  8. ykchakri New Member

    No, I have three filegroups (with 1 file each) under this database. All the non-clustered indexes of 'FinAct' table are under 'INDEX' filegroup which is intially having only one file. But, during the DBREINDEX I'm adding another file to this filegroup (due to space constraints) and emptying and deleting it after the DBREINDEX.
  9. jasper_smith New Member

  10. ykchakri New Member

    Thanks for that excellent link Jasper. The following para from this article kept me thinking:
    With not enough free space in the data file(s), DBCC DBREINDEX may be unable to rebuild the indexes, or the indexes may be rebuilt with logical fragmentation values above zero.

    It's true that I don't have enough space for DBREINDEX on the disk where my indexes are. But then, as I told before, I'm adding an additional file on a different disk just before starting the DBREINDEX and I can see that the space on this disk being used during the DBREINDEX. But, maybe it's already doing the harm of increasing logical fragmentation, by the time it actually started using the extra space. Any comments from anyone on this ?
  11. ykchakri New Member

    Also, after DBREINDEX I'm emptying the additional file and deleting it. Can this be a possible reason for this ? Because while emptying, it is going to migrate all the data from this additional file on to the existing one which may be causing some fragmentation.
  12. satya Moderator

    I don't think so it would cause another level of fragmentation, being DBREINDEX will handle to correct the required fragmentation.

    BTW are you running INDEXDEFRAG on the database all the times?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  13. ykchakri New Member

    Hi Satya,

    No, I'm running INDEXDEFRAG once a week and thats too only on the indexes that are too much fragmented. And since DBREINDEX is better than INDEXDEFRAG in some cases, I want to do it atleast once a month and that's where I'm running into the above problems.
  14. satya Moderator

    So do you have window to run DBREINDEX once a week, rather depending on INDEXDEFRAG.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  15. ykchakri New Member

    No, I don't have enough window to run DBREINDEX every week. But, I can afford to get a window once a month to run DBREINDEX. So, I want to run INDEXDEFRAG once a week and then compensate anything that is missed by INDEXDEFRAG, by running DBREINDEX once every month.
  16. satya Moderator

    How big is the table in terms of rows?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  17. ykchakri New Member

    47810899 in rows and 15186896 KB in size.

  18. satya Moderator

    How long its taking time for DBREINDEX?
    Anyway as you said its not possible to get window to run every week, try to fine tune the queries or archive the current database.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page