Post reply

What will be the sequence to perform DBREINDEX on 5 Indexes of a Table?

  • August 4, 2010 at 6:36 am

    #225117

    Hi,

    I have SQL Server 2000 version and I have a table with Clustered and 4 non-clusters Indexes. Table size is around 5GB

    I’m running DBCC cmd :

    DBCC SHOWCONTIG ('table_Name') WITH ALL_INDEXES, TABLERESULTS

    Column ScanDensity is having values between 50 and 95. So I have decided to perform DBCC DBREINDEX on all the indexes (cluster and 4 non-cluster)

    but I’m little confuse what will be the sequence of performing DBREINDEX; mean on which index I should perform DBREINDEX first and so on and what will the impact if I first Reindex cluster index and then reindex othere non-cluster.

    SQL Server 2000 didn’t having option to disable index.

    Please suggest.

    Ram
    MSSQL DBA

  • August 4, 2010 at 7:27 am

    #1202079

    In this case, all you need to do is reindex the clustered index - this will force the nonclustered indexes to be reindexed, since they depend on that.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 4, 2010 at 11:59 am

    #1202275

    WayneS (8/4/2010)

    In this case, all you need to do is reindex the clustered index - this will force the nonclustered indexes to be reindexed, since they depend on that.

    DBCC DBREINDEX should not be used anymore, instead one should use ALTER INDEX ... REBUILD. And rebuilding the clustered index will not rebuild non-clustered indexes.

    From BOL (http://technet.microsoft.com/en-us/library/ms188388.aspx)

    Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified.

Viewing 3 posts - 1 through 2 (of 2 total)

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