Database Reference
In-Depth Information
It is better to reorganize rather than rebuild indexes after a shrink operation is completed. An index rebuild
creates another copy of the index, which increases the size of the data file and defeats the purpose of the shrink.
As the alternative to the shrink process, you can create a new filegroup and recreate indexes by moving objects
there. After that, the old and empty filegroup can be dropped. This approach reduces the size of the database similar
to a shrink operation without introducing fragmentation.
Summary
There are two types of index fragmentation in SQL Server. External fragmentation occurs when logically subsequent
data pages are not located in the same or adjacent extents. Such fragmentation affects the performance of scan
operations that require physical I/O reads.
External fragmentation has a much lower effect on the performance of index seek operations, when just a
handful of rows and data pages need to be read. Moreover, it does not affect performance when data pages are cached
in the buffer pool.
Internal fragmentation occurs when leaf-level data pages in the index have free space. As a result, the index uses
more data pages to store data on disk and in memory. Internal fragmentation negatively affects the performance of
scan operations, even when data pages are cached, due to the extra data pages that need to be processed.
A small degree of internal fragmentation can speed up insert and update operations and reduce the number of
page splits. You can reserve some space in leaf-level index pages during index creation or index rebuild by specifying
the
FILLFACTOR
property. It is recommended that you fine-tune
FILLFACTOR
by gradually decreasing its value and
monitoring how it affects fragmentation in the system.
The
sys.dm_db_index_physical_stats
data management function allows you to monitor both internal and
external fragmentation. There are two ways to reduce index fragmentation. The
ALTER INDEX REORGANIZE
command
reorders index-leaf pages. This is an online operation that can be cancelled at any time without losing its progress.
The
ALTER INDEX REBUILD
command replaces an old fragmented index with its new copy. By default, it is an offline
operation, although the Enterprise edition of SQL Server can rebuild indexes online.
You must consider multiple factors when designing index maintenance strategies, such as system workload and
availability, the version and edition of SQL Server, and any high-availability technologies used in the system. You
should also analyze how fragmentation affects the system. Index maintenance is very resource intensive and, in some
cases, the overhead it introduces exceeds the benefits it provides.
The best way to minimize fragmentation, however, is by eliminating its root-cause. Consider avoiding situations
where row size increases during updates, do not shrink data files, do not use AFTER triggers, and avoid indexes on the
uniqueidentifier or hashbyte columns that are populated with random values.
