Reindex versus Index Defrag

  • 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!!!

  • I just use DEFRAG. Basic ifference - DEFRAG is an online operation. REINDEX locks the tables and your users can't access them.

    -SQLBill

  • SQLBill,

    Do you notice, over time, any degradation? I believe the defrag only reorgs the pages that are not intertwined with other indexes.

  • No degradation. Everything runs faster after I've done a defrag.

    -SQLBill

  • 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?

  • 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.

  • 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 ...

  • 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