79352692

Date: 2025-01-13 15:35:10
Score: 0.5
Natty:
Report link

If the purpose of Rebuilding index is performance then you can first try in these following order on table:

  1. Updating Statistics - Resource cost of updating statistics is minor compared to index reorganize /rebuild, and the operation often completes in minutes. Index rebuilds can take hours.

    UPDATE STATISTICS mySchema.myTable;

  2. Reorganize Indexes - Reorganizing an index is less resource intensive than rebuilding an index. For that reason it should be your preferred index maintenance method, unless there is a specific reason to use index rebuild.

    ALTER INDEX ALL ON mySchema.myTable REORGANIZE;

  3. Rebuild Indexes (offline) - 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.

    ALTER INDEX ALL ON mySchema.myTable REBUILD;

  4. Rebuild Indexes (online) - 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.

    ALTER INDEX ALL ON mySchema.myTable REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 10);

Notes:

Ref:

Reasons:
  • Long answer (-1):
  • No code block (0.5):
  • Low reputation (1):
Posted by: Harsh Varde