MSSQL-Server: rebuild vs reorganize Index and Update Statistics

Question

We had a database which was very slow (MS SQL SERVER). Now we figured out, that the indexes were never rebuild oder reorganised. (At the moment it is not clear if this really was the problem but we want to do this frequently now.)

But now the Question: Is it better to rebuild the index or is it better to reorganize it? At the moment we had fragmentation of over 90% so i made a rebuild. But if i want to do it frequently the fragmentation will normally be lower. So would it make more sense to reorganize it instead of rebuild? And would you really make a frequent task for that on every MS SQL Server without exception? Does it make sense on all MS SQL-Servers?

And is there any recommendation how oft this task should run? once a week? once a month?

I also saw in blogs that they make also a "update statistics" after rebuild/reorganisation. Is this recommended or nonsense? (I found both on blogs. -> one said: yes do it -> other: nonsense!)

And what is your experience? Do these tasks (rebuild/reorganzie/statistics) take a lot of performance ?

Many Questions! Sorry!

Solution

There are volumes and volumes of information out there that answer these questions. The answer is: it depends. Regular index maintenance and statistics updates are crucial, that much is certain. You have to do some research and tailoring of how and when to do it based on your environment, though.

As a good start, read these:

• Rebuild or Reorganize: SQL Server Index Maintenance (Kendra Little)
• Update Statistics: the Secret IO Explosion (Kendra Little)
• Index Maintenance (Jonathan Kehayias)
• What Caused That Plan to Go Horribly Wrong - Should You Update Statistics (Kimberly Tripp)
• Understanding When Statistics Will Auto Update (Erin Stellato)

Comments

[squillman]

Yes, Ola Hallengren's scripts are money. They're mentioned in the posts which I why I didn't specifically point to them.