14

We have some databases with index fragmentation that is > 95%. As best I can tell the indexes have never been rebuilt much less reorganized. In years.

(In fairness, these tables do seem to have auto-updated statistics enabled. Also in fairness, he is diligent about backups: full daily and trx logs hourly.)

When I asked, the DBA said he was reluctant to rebuild or reorg the indexes. When I asked why, he couldn't really articulate it. Eventually he said he was concerned about potential data loss. For instance one of the databases is used by our Great Plains Dynamics accounting application, and he seemed very anxious about that.

I am not a DBA but from what I've read, his anxiety seems ... difficult for me to understand.

I am not sure what do to next. Suggestions how I should proceed?

Greg Hendershott
  • 786
  • 1
  • 7
  • 12
  • Unless that database is hit hard 24/7, and the world will come to an end if it goes offline, there's no excuse for such behavior. I script reorgs and statistics every week on over 12,000 databases without a second thought. In 16 years I've only had one corrupt due to a bad controller. – Brain2000 Apr 06 '16 at 13:10

3 Answers3

22

Rebuilding a database index should not cause any data loss. It will however probably cause a substantial performance degradation as the indexes being rebuilt will normally not be available for use until the rebuild finishes. For that reason it should be done during off-hours when the affected systems are idle.

Paranoia is a Good Thing in a DBA - If they're worried about data loss I would have them do a proper test of the backups (restore them to a separate system and make sure the data is all there), and if they're still concerned then performing a full backup before rebuilding the indexes would be a reasonable precaution to take.

voretaq7
  • 79,345
  • 17
  • 128
  • 213
  • 11
    +1 for Paranoia is Good DBA Trait – Joel Coel May 04 '11 at 16:33
  • I completely understand and appreciate healthy paranoia. Measure twice, cut once. Where I'm flummoxed is this _seems_ to about lack of understanding rather than caution. And rather than "let's determine a way to try it, carefully", it's "yeah not gonna happen". We could (say) spool up a test EC2 instance with a copy of the data, reorg the indexes, time it, delta the results table rows to confirm no data was corrupted. That kind of plan would be caution ... as opposed to inaction? – Greg Hendershott May 04 '11 at 18:38
  • 1
    Just a reminder that index reorganize is always online (all indexes are available during defrag) and index rebuild can be made also online (`WITH (ONLINE=ON)` as long as the index does not contain BLOB columns. – Remus Rusanu May 04 '11 at 21:40
  • @Greg Yeah, the "Let's just not touch he indexes that are so fragmented they're probably HURTING performance" mentality confuses the hell out of me too - The occasional `REINDEX` as "preventative maintenance" on tables where the index contents change a lot is pretty common in my experience (if the index is mostly static it's less of a thing) – voretaq7 May 04 '11 at 21:42
  • @Remus good tip - This lessens the performance impact (you're still going to have high disk I/O, which will slow you down some, but at least things that would use an index can still use it rather than resorting to sequential scans) – voretaq7 May 04 '11 at 21:44
  • Perhaps the DBA is paranoid because he/she has cause application outages in the past by causing the databases' transaction logs to become full during rebuilds, or application concurrency issues if rebuilding offline? – SuperCoolMoss May 05 '11 at 13:50
  • @SuperCoolMoss Filling the transaction log is a legit concern, but if there is enough space on the transaction log device and the transaction log is being routinely backed up & truncated it shouldn't be an issue. (If the transaction log is not being routinely backed up & truncated the DBA needs a good kick in the head…) – voretaq7 May 05 '11 at 14:33
6

There is no risk of data loss from rebuilding or defragging indexes.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • Unless you've already got some degree of data corruption, or there's failing hardware. But in either of those cases, index fragmentation is the least of your worries! – db2 May 05 '11 at 19:57
  • But that wouldn't be corruption from an index rebuild, but from some other problem. – mrdenny May 14 '11 at 06:35
4

Reorganizing the indexes will take less time, and less effort from the SQL server thus they can be done in a weeknight type of instances. If you what you are saying is true, even reorganizing the indexes that have never been, may cause a larger impact on the server as well. Rebuilding the indexes will take a substantial amount of effort from the SQL server since they are dropped and rebuilt. Doing a rebuild on a weeknight isn't worth the risk of the server being busy with indexes and not serving the people using it.

I agree with voretaq7, if he is that worried about working with indexes, try it out on the development or test servers first to see how the react.

RateControl
  • 1,207
  • 9
  • 20
  • Another approach to take may be to explicitly `DROP INDEX` and re-`CREATE INDEX` -- I'm not sure about SQL Server, but I know PostgreSQL sometimes does better blowing away an index and starting from scratch rather than trying to rebuild (`REINDEX`) it. – voretaq7 May 04 '11 at 16:36
  • I am pretty sure dropping and recreating is unnecessary on SQL Server. – Justin Dearing May 04 '11 at 18:47
  • @Justin I'm pretty sure you're right (in fact from my Sybase days I recall that a the reindexing behavior is effectively a drop/create so there's no index-locking oddity like in Postgres) – voretaq7 May 04 '11 at 21:40
  • Reorganizing indexes can take less time. Which one takes longer will depend on the amount of fragmentation of the index. – mrdenny May 14 '11 at 07:06