Rebuilding index is very bad practice !

Question

Hi Team,

Today when I go through some of the SQL Server performance videos, found one interesting video (Please find the link below), where he said that rebuilding indexes is a very expensive thing and it will clear the cache every time an index rebuild happens, (It means that we indirectly killing the SQL Server everything we rebuild index).

If it is true, why do many organizations create index maintenance jobs periodically?

watch

Answer 1

I recently blogged about index rebuild and also did some measurements of "improvements" after a rebuild: https://sqlblog.karaszi.com/index-fragmentation-revisited/

Answer 2

Yes, rebuilding indexes is an expensive thing. That is why it should be done after hours and not every 5 mins. Have outdated stats and fragmented indexes also is expensive. It is a trade off you need to weigh.

The simplest solution is to use something like Ola's scripts, which have a threshold between "rebuild" and "reorganize".

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Answer 3

Also see:
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

Answer 4

As for clearing the cache, there are two things here:

1) All queries referring to the table will be marked for recompile.
2) If the table is big, this can push out other entries from the buffer cache.

As for why we should rebuild indexes, many do it out of habit. In the days we had spinning disk, there were certainly to gains to make by making sure that the double-linked list in a clustered index were in physical order. This is less critical with SSDs. However, still important is that your pages should not have too much empty space on, because in that case you are wasting memory on vacuum.

The normal thresholds for fragmentation are based on the internal fragmentation (that is, the first type above). But this parameter is also a lot cheaper to to determine than page fullness. But you don't have to go with DETAILED. SAMPLED may be good enough.

Answer 5

Hi VijayKumar768,

In addition, Index reorganization is a process where the SQL Server goes through the existing index and cleans it up. It leaves the system state as it is without locking-out affected tables and views, and is always an online operation. This means long-term object-level locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX ... REORGANIZE operation.

Rebuilding an index drops and re-creates the index. Depending on the type of index and the Database Engine version, a rebuild operation can be done offline or online. An offline index rebuild usually takes less time than an online rebuild, but it holds object-level locks for the duration of the rebuild operation, blocking queries from accessing the table or view. An online index rebuild does not require object-level locks until the end of the operation, when a lock must be held for a short duration to complete the rebuild.
Please refer to https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15 and https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver15 for more details.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.