1

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!

Unihedron
  • 103
  • 4
frupfrup
  • 853
  • 3
  • 13
  • 27

2 Answers2

2

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:

squillman
  • 37,618
  • 10
  • 90
  • 145
0

What Squillman said, but also, you might want to consider using something like Ola Hallengren's scripts, which only do as much work as necessary. You can customize the amount of fragmentation in an index before it's rebuilt or reorganized, and this cuts down on the amount of work involved in reindexing.

But yes. It depends on your environment, on the type of load (mostly select, mostly insert/update/delete), etc.

Katherine Villyard
  • 18,510
  • 4
  • 36
  • 59
  • 1
    Yes, Ola Hallengren's scripts are money. They're mentioned in the posts which I why I didn't specifically point to them. – squillman Oct 02 '14 at 19:49