Post reply

sql table reorganization

  • May 24, 2004 at 4:55 pm

    #108186

    HI. I know you can clean up your indexes by doing a dbcc indexdefrag. Is there anything similiar to this for cleaning up table fragmentation?  I ran a  DBCC SHOWCONTIG on a test database specifying no particular object in the database, so it returned stats on all the tables.  From reading BOL, it appears i have fragmentation on some tables. I didn't know if fragmentation is coming from the indexes or can just the table itself be fragmented. 

    confused,

    Juanita    

  • May 25, 2004 at 5:39 am

    #507619

    Rebuilding the clustered index on your tables will sort it out.

    Either use dbcc dbreindex(table_name, index_name) or create clustered index index_name on table_name(column_list) with drop existing

  • May 25, 2004 at 9:30 am

    #507696

    HI Robert,

    Thanks for the reply. I just read on dbcc dbreindex. how does that differ from dbcc indexdefrag? and is one better than the other? i do see that the dbreindex is an offline operation, while indexdefrag is not.

    Juanita

     

     

  • May 25, 2004 at 9:42 am

    #507702

    No idea offhand - I've always used dbreindex.

    From BOL though, it looks like rebuilding the index is more thorough, but as you say, it's an offline operation.

    I guess it depends on the level of fragmentation you have, and the amount of offline time you have to spare for index rebuilds. I would say that a lot of people consider it essential to rebuild indexes on a very regular basis, but this often isn't the case. It depends on the amount of change which is occurring to your data. As always, the best thing is to try it and see - if you get better performance with frequent rebuilds then fine, but if not then you're just wasting time.

    Bear in mind also that rebuilding a clustered index will require at least double the amount of disk space taken by the base table, since the table is rewritten in index order before the old data is deleted. Following a clustered index rebuild, you should shrink the database to remove all of the empty space left behind.

    Hope this helps

    Rob

  • May 25, 2004 at 9:44 am

    #507703

    HI..

    Got it, this helps alot. Thanks for you input!

    Juanita

     

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

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