Index Fundamentals Part 5

Today we have a look at index maintenance. Indexes get fragmented over the time by inserting, deleting and updating indexed rows.
To handle this fragmentation indexes have to be reorganized/rebuilded regularly.
What is the difference between rebuild and reorganize?

Rebuild:
- Rebuild creates the index completly new (single threaded in Standard Edition multithreaded in Enterpriese Edition)
- Rebuild can be done online in Enterprise Edition (not on tables with large object types) but needs a Schema lock at the start that can be a problem on high frequented tables
- In Rebuild you can set a new fillfactor
- Rebuild is a transaction if you cancel it, there is a rollback and it's done offline

Reorganize:
- Reorganize is allways online
- Reorganize can be canceled without a rollback
- Reorganize doesn't need a schema lock there is no problem on high frequented tables

How can I check the fragmentation of an Index?
For checking the percentage of fragmentation use the following script from microsoft:
Microsoft Index Checkscript

Rebuild or Reorganize?
Microsoft Best practice says reorganize for fragmentation <= 30% and rebuild for fragmentation > 30% but in 24/7 environment there is the problem that a rebuild on the Standard Edition is offline and the index is not available. There can be great performance impacts for that issue. So if you have a environment with a maintenance window you can surely rebuild your indexes. For big tables and often used indexes you should use reoarganize.

In the next post I will show you a maintenance script for indexes.