SQL Server Performance

dbreindex /indexdefrag vs. clustered/non-clustered

Discussion in 'Performance Tuning for DBAs' started by crichardson, Jun 29, 2004.

  1. crichardson New Member

    I pretty much understand the differences between DBCC DBREINDEX and DBCC INDEXDEFRAG. However, I need the forums help to understand a few specific issues relating to clustered/non-clustered indexes and the advantages/disadvantages of running the DBCC DBREINDEX/INDEXDEFRAG against the table or against each specific index...

    "If a table has a clustered index, it's only necessary to re-index the clustered index because any non-clustered indexes on that table will be automatically re-indexed as well."

    I think the above statement is true for DBCC DBREINDEX but is the following statement true for DBCC INDEXDEFRAG:-

    "If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."

    Following on from the above, is there any advantage with an index maintenance strategy to individually running DBCC DBREINDEX against each specific index as opposed to running it against the table and letting SQL sort out the underlying indexes? Does the same apply to DBCC INDEXDEFRAG?


  2. Luis Martin Moderator

    From BOL

    Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built. Another advantage is that with DBCC INDEXDEFRAG, the index is always available, unlike DBREINDEX. A large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.


    Luis Martin

    All postings are provided “AS IS” with no warranties for accuracy.

  3. rohit2900 Member

    We have a table with 100 column and around 4 million records in production db, with 1 clustered and 4 nonclustered indexes. My question is in order to defrag all these indexes what should be the order. I mean to say first I should defrag clustered then non clustered or vice-versa or I should defrag only clustered as other will automatically defrag. But when I'm defraging clustered and it give message like
    Pages Scanned Pages Moved Pages Removed
    ------------- ----------- -------------

    with values in each column, now if just after completing it if I again run it, it gives value in first and zero in rest two columns that implies that its fully defragmented, now if I rerun the same query on non clustered index it also give values in all three columns and if I run it on clustered index again after defragmenting non clustered index then it again gives values in all 3 columns....I'm not understanding this as I just defragmented the clustered then hwo come it become fragmented.....

  4. satya Moderator

    What is the update & insert rate on this table, how frequently the data is updated?
    Also confirm the service pack level on SQL Server.
  5. rohit2900 Member

    SQL Server 2000 SE SP4, in normal week days aroung 15000 new records been added and around 25000 records get updated apart from this....I tried it on Saturday.....and their is very less load on weekends. adn apart forn this I did this one by one and at the max their will be 50-100 inserts and 100-150 updates at the max....at that time..... Then why I'm seeing the values in last two columns (Pages Moved, Pages Removed) given that I just defragmented the indexes 2 mins back.....and suggestions...
  6. rohit2900 Member

    We have a table with 15 million records and having non unique clustered index on two columns combined together one is datetime and other is int. I've rebuild the clustered index with 20% fill factor with pad index in last week's maintenance, now if on a scheduled basis I'll defrag this index and several other non clustered indexed then after defragmentation will they be in their original position i.e. 20% free space in each data and intermediate pages.
    What I'm trying to understand is irrespective of doing DBCC reindex (using the same fillfactor which was used earlier) or DBCC indexdefrag lead to same status of the data in data pages?
    I hope I've cleared as what I'm trying to understand...

Share This Page