Post reply

Re-Index all tables in Database.

  • November 24, 2009 at 5:25 pm

    #73405

    I recently ran a DBCC SHOWCONTIG on one of my databases and the SCAN Density was really low, 14%-20% on a bunch of tables. I was wondering what command would re-index all my my tables.

    I was planning on using...

    EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'

    I know this is a undocumented stored procedure, but it has worked well for me in the past, I have only used it on SQL 2005 databases, but noticed it is there under my SQL 2000 system stored procedure under master.

    My other option is to use DBCC DBREINDEX (tablename, '',70) for each table, correct?

    Thanks.

  • November 24, 2009 at 5:41 pm

    #1083753

    For 2000 use dbcc dbreindex and for 2005 use rebuild option under alter table command(http://www.mssqltips.com/tip.asp?tip=1367).

    MJ

  • November 24, 2009 at 5:51 pm

    #1083758

    Any reason I can not use EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")' ?

    After I run SHOWCONTIG they look fine afterwards.

    I backed up and restored to a test server. I am not second guessing you, I am asking to learn.

    What is the difference between the two? One obviously allows me to select the fill value, anything else?

    I am writting the procedure as I type using DBCC DBREINDEX, so I am doing the perferred method. I am just curious what the difference is.

  • November 30, 2009 at 2:03 am

    #1085274

    TechJunky (11/24/2009)

    Any reason I can not use EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")' ?

    After I run SHOWCONTIG they look fine afterwards.

    I backed up and restored to a test server. I am not second guessing you, I am asking to learn.

    What is the difference between the two? One obviously allows me to select the fill value, anything else?

    I am writting the procedure as I type using DBCC DBREINDEX, so I am doing the perferred method. I am just curious what the difference is.

    Yes there is a very good reason: sp_msForEachTable is not documented, and may change at any time.

    You can write a forward only cursor to loop through the tables and run a DBCC REINDEX on the more fragmented ones. In fact, there is a sample in Books Online to do that.

  • November 30, 2009 at 4:07 pm

    #1085580

    If you look under the scripts section of this site, there are lots of Index Management scripts you can use.

  • December 1, 2009 at 12:22 am

    #1085663

    Good tip, Steve! I completely forgot about that. I'm going to have a look myself. 🙂

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

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