Index Management and Maintenance

Index Management and Maintenance

Because the data within an index is stored in sorted order, over time, values can move around within the index due to either page splits or changes in the values. To manage the fragmentation of an index over time, you need to perform periodic maintenance.

Index Fragmentation

            Files on an operating system can become fragmented over time due to repeated write operations. Although indexes can become fragmented, index fragmentation is a bit different from file fragmentation.

               When an index is built, all the values from the index key are written in sorted order onto pages within the index. If a row is removed from the table, SQL Server needs to remove the corresponding entry from the index. 

• The removal of the value creates a “hole” on the index page. SQL Server does not reclaim the space left behind because the cost of finding and reusing a hole in an index is prohibitive.
• If a value in the table that an index is based on changes, SQL Server must move the index entry to the appropriate location, which leaves behind another hole. 

When index pages fill up and require a page split, you get additional fragmentation of the index. Over time, a table that is undergoing large amounts of data changes has the indexes become fragmented.

               

To control the rate of fragmentation of an index: 

• Use an index option called the fill factor. 
• Use ALTER INDEX statement to remove the fragmentation.

FILLFACTOR

          The FILLFACTOR option for an index determines the percentage of free space that is reserved on each leaf-level page of the index when an index is created or rebuilt. The free space reserved leaves room on the page for additional values to be added, thereby reducing the rate at which page splits occur. 
The FILLFACTOR is represented as a percentage full. For example, a fill factor of 75 means that 25 percent of the space on each leaf-level page is left empty to accommodate future values.

Defragmenting an Index

ALTER INDEX { index_name | ALL }

ON <object>

{ REBUILD

[ [ WITH ( <rebuild_index_option> [ ,...n ] ) ]

| [ PARTITION = partition_number

[ WITH ( <single_partition_rebuild_index_option>

[ ,...n ] )] ] ]

| DISABLE | REORGANIZE

[ PARTITION = partition_number ]

[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]

| SET ( <set_index_option> [ ,...n ] ) }[ ; ]

  

 You can defragment indexes using either the REBUILD or REORGANIZE options.

REBUILD

           The REBUILD option defragments all levels of an index. Unless the ONLINE option is specified, a REBUILD acquires a shared table lock and block any data modifications.

REORGANIZE

            The REORGANIZE option defragments only the leaf level of an index and does not cause blocking.

Disabling an index

   An index can be disabled by using the ALTER INDEX statement as follows:

ALTER INDEX { index_name | ALL }

ON <object>

DISABLE [ ; ]

      To enable an index, it must be rebuilt to regenerate and populate the B-tree structure. You can accomplish this by using the following command:

ALTER INDEX { index_name | ALL }

ON <object>

REBUILD [ ; ]