July 28, 2005 at 6:38 am
I have a SS2K database that I added some indexes to. For the most part, the database is queried as only once a day does any inserting take place. After the daily bulk insert is done, I drop the indexes and recreate them. While rebuilding the indexes has helped the performance, I'm wondering if it's necessary to recreate them or if I could do a rebuild.
Would it be better to rebuild them vs. recreating and what would be the advantages of either method?
Thanks,
Brian
July 28, 2005 at 6:51 am
The main diffrenec is that dbcc dbreindex is an offline operation whereas dbcc indexdefrag is an online operation.
However dbcc indexdefrag will skip any locked pages while it is working so it may not be as complete as dbcc dbreindex.
The other diffrence is that dbcc dbreindex also updates the statistics for the table whereas i believe dbcc indexdefrag does not.
It all really comes down to whether you have a quite period where you can use dbcc dbreindex and no one will notice or whether it is a continually busy server.
I tend to use a mixture of both.
hth
David
July 28, 2005 at 6:53 am
Oops didn't quite read your post correctly no you don't need to drop them you can use one of the two dbcc commands mentioned in previous post
David
July 29, 2005 at 2:02 am
Depending on the data load and the indexed values, sometimes you are better off dropping the index, loading the data, then creating the index. The only way to know is to time the methods and see what is best for your situation.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply