April 5, 2006 at 5:41 am
Hello everyone!
We are having a little debate as to whether or not it is necessary to run reindex or should we just run index defrag. Normally, we run a reindex on most databases weekly. On our large databases (>100GB) we run only a defrag. We are currently looking at running only a defreg on all databases. We are also looking at running a defrag weekly and possible a reindex monthly. This change is not being driven by time constaints. Can anyone out there give me some insights as to what they do and why? Also, what are the ramifications performance wise, defrag versus reindex?
Thanks in advance!!!
April 5, 2006 at 6:52 am
I just use DEFRAG. Basic ifference - DEFRAG is an online operation. REINDEX locks the tables and your users can't access them.
-SQLBill
April 5, 2006 at 6:56 am
SQLBill,
Do you notice, over time, any degradation? I believe the defrag only reorgs the pages that are not intertwined with other indexes.
April 5, 2006 at 12:08 pm
No degradation. Everything runs faster after I've done a defrag.
-SQLBill
April 5, 2006 at 12:12 pm
I have read about some issues such as running twice as long after SP4 has been applied. We are currently running SP3a. Are you SP4 yet?
April 6, 2006 at 5:14 am
Our database is around 200 GB at the moment, and ever since the time when it is over 100 GB we use to reindex at least once a month, quite often every 2 weeks, and there is significant difference in performance before and after reindexing. We also have a job that is running daily, looking for indexes with high fragmentation and running defrag on them.
If we dont run the reindex for more than 4 weeks, we have quite serious problems with some functionalities due to slow speed, blocking and deadlocks.
April 6, 2006 at 8:46 am
can someone provide some examples of how you do the reindex. What we currently do is loop through the table and exec DBCC DBREINDEX for each table. I get an out of memeory error. My DB is only about 50G and largest table has 33 mil rows.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
April 6, 2006 at 9:13 am
We are using maintenance plan, ie. sqlmaint component for the optimization that includes index rebuild (shortly described as "reindexing" in my previous post). For this operation on our database it is necessary to have lots of free space - transaction log can grow past 30 GB (for a 200 GB database) during the process. Maybe this is where you are having problems? Also, it is good not to shrink the log after optimization, so that it doesn't have to grow again next time - our standard transaction log size is 30 GB, of that less than 1 GB is used most of the time.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply