Table of Contents
Index fragmentation can cause problems with query performance. Indexes therefore need to be occasionally rebuilt.
Over time, as indexes are subjected to data modifications, index fragmentation can occur in the form of:
- Gaps in data pages – this creates wasted empty space.
- Logical fragmentation – this is a logical ordering of the data that no longer matches the physical ordering of the data.
Gaps in data pages can reduce the number of rows that can be stored in SQL Server's data cache, leading to increased disk I/O. Logical fragmentation can cause extra disk activity as the disk subsystem has to work harder to find the data on disk and move it to the data cache.
The only way to remove wasted space and logical fragmentation is to rebuild or reorganize the indexes on a regular basis. This is one of the most useful and powerful maintenance tasks that you can perform on a database, because the steps it performs can greatly boost database performance.
When and how often should you Rebuild Indexes?
The performance of your indexes, and therefore your database queries, will degrade as your indexes become fragmented. The
REINDEX DATABASE does a very good job of rebuilding indexes to remove logical fragmentation and empty space, and updating statistics. As such, it is very important that you schedule this task to run regularly.
On the other hand, the Rebuild Index task is a resource intensive task. In addition, as an index is being rebuilt, locks will be placed on the index, preventing anyone from accessing it while the rebuilding occurs. Any queries trying to access this index in order to return the required results will be temporarily blocked, until the rebuild is complete. As such, the Rebuild Index task is considered as an offline activity, to be run when as few people as possible are accessing a database. In general, this means during a scheduled maintenance window.
It is quite difficult to offer general advice with regard to when and how often to rebuild indexes.
As it is so dependent on the nature of the data, the indexes and the queries that use them. However, take a look at my general advice with regard to index rebuilding, and then we'll consider the advice in a little more detail over the coming sections:
- Nightly, if required. If your indexes fragment rapidly, and you have a nightly maintenance window that allows you to run the Rebuild Index task, along with all the other maintenance tasks; then do so. Index fragmentation will degrade the performance of your indexes. Assuming that you have a maintenance window, rebuilding every night can't do any harm, and can very well boost the performance of your server.
- Weekly, at minimum. If you can't perform this task nightly, then, at a minimum, it should be run once a week, during a maintenance window. If you wait much longer than a week, you risk hurting your SQL Server's performance due to the negative impact of wasted empty space and logical fragmentation.
- Consider alternatives, otherwise. If you don't have a maintenance window long enough to run this task at least once a week, then you can try implement event on a special SP, which do reindex not of the whole database but reindex of table by table or even field by field. For such task can be used tmp table that keep list of all tables/fields and some order for tasks.