Rebuilding index is very bad practice !

Vijay Kumar 2,016 Reputation points
2022-04-05T19:49:10.293+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,115 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
{count} votes

Accepted answer
  1. tibor_karaszi@hotmail.com 4,306 Reputation points
    2022-04-06T15:13:17.767+00:00

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


4 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-04-05T20:10:36.75+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. Tom Phillips 17,716 Reputation points
    2022-04-05T20:19:15.653+00:00
    0 comments No comments

  3. Erland Sommarskog 104.1K Reputation points MVP
    2022-04-05T21:33:49.517+00:00

    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.

    0 comments No comments

  4. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2022-04-06T02:52:51.207+00:00

    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.

    0 comments No comments