In this lesson, you will learn how to perform essential index maintenance without shutting down your database or blocking your users.
As we discussed in the previous module on index maintenance, indexes can become fragmented over time due to frequent inserts, updates, and deletes. When fragmentation becomes high, your Balanced Tree structure becomes less efficient, leading to slower query performance. To fix this, we need to rebuild the index. However, the way you rebuild that index determines whether your application stays online or grinds to a halt.
The Offline Rebuild
When you perform an Offline Rebuild, the database places an exclusive lock on the table. Think of this like closing a shop for renovations: while the builders are working on the shelves, no customers can enter to browse or purchase items.
- Pros: It is generally faster because the database engine doesn't have to worry about incoming changes while rearranging the tree.
- Cons: Any application attempting to read from or write to that table will experience a timeout or a "hang" until the process completes.
The Online Rebuild
An Online Rebuild allows the database to stay open for business. The engine builds a new version of the index in the background while simultaneously tracking changes being made to the original index.
- Pros: High availability. Your users can continue to query and modify data without interruption.
- Cons: It requires more system resources (CPU and memory) and takes longer to complete, as the engine must synchronize the "new" index with the "live" changes happening in the "old" index.
Choosing Your Strategy
In most high-traffic production environments, the Online approach is preferred, even if it takes longer, because downtime is usually more expensive than additional background resource consumption. You might initiate this process using syntax similar to:
-- Online rebuild (high availability)
ALTER INDEX idx_user_account REBUILD WITH (ONLINE = ON);
-- Offline rebuild (faster, but blocks access)
ALTER INDEX idx_user_account REBUILD;
If your application has a low-traffic window, an offline rebuild can be a quick and efficient way to clean up your indexes. However, if your database must be available 24/7, you should prioritize online operations.
Key Takeaway: Choose Offline Rebuilding for speed during maintenance windows, and use Online Rebuilding to maintain high application availability at the cost of longer execution times and higher resource usage.