SQL Server 2008 R2 : SQL Server Index Maintenance (part 2) - Setting the Fill Factor, Reapplying the Fill Factor, Disabling Indexes, Managing Indexes with SSMS

Setting the Fill Factor

Fill factor is a setting you can use when creating an index to specify, as a percentage, how full you want your data pages or leaf-level index pages to be when the index is created. A lower fill factor has the effect of spreading the data and leaf index rows across a greater number of pages by leaving more free space in the pages. This reduces page splitting and dynamic reorganization of index and data pages, which can improve performance in environments where there are a lot of inserts and updates to the data, while at the same time reducing performance for queries because an increased number of pages need to be read to retrieve multiple rows. A higher fill factor has the effect of packing more data and index rows per page by leaving less free space in the pages. Using a higher fill factor is useful in environments where the data is relatively static because it reduces the number of pages required for storing the data and its indexes, and it helps improve performance for queries by reducing the number of pages that need to be accessed.

By default, when you create an index on a table, if you don’t specify a value for FILLFACTOR, the default value is 0. With a FILLFACTOR setting of 0, or 100, the data pages for a clustered index and the leaf pages for a nonclustered index are created completely full. However, space is left within the nonleaf nodes of the index for one or two more rows. The default fill factor to be used when creating indexes is a server-level configuration option. If you want to change the server-wide default for the fill factor, you use the sp_configure command:

sp_configure 'fill factor',N

It is generally recommended that you leave the server-wide default for fill factor as 0 and specify your FILLFACTOR settings on an index-by-index basis. You can specify a specific fill factor value for an index by including the FILLFACTOR option for the CREATE INDEX statement:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
   ON [ [database_name.][schema_name.]] table_or_view_name
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
<relational_index_option> ::=
{ PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism }

The FILLFACTOR option for the CREATE INDEX command allows you to specify, as a percentage, how full the data or leaf-level index pages should be when you create an index on a table. The specified percentage can be from 1 to 100. Specifying a value of 80 would mean that each data or leaf page would be filled approximately 80% full at the time you create the index. It is important to note that as more data gets modified or added to a table, the fill factor is not maintained at the level specified during the CREATE INDEX command. Over a period of time, you will find that each page has a different percentage of fullness as rows are added and deleted.

If you specify only the FILLFACTOR option, only the data or leaf-level index pages are affected by the fill factor. To specify the level of fullness for nonleaf pages, use the PAD_INDEX option together with FILLFACTOR. This option allows you to specify how much space to leave open on each node of the index, which can help to reduce page splits within the nonleaf levels of the index. You don’t specify a value for PAD_INDEX; it uses the same percentage value specified with the FILLFACTOR option. For example, to apply a 50% fill factor to the leaf and nonleaf pages in a nonclustered index on title_id in the titles table, you would execute the following:

CREATE INDEX title_id_index on titles (title_id)
        with (FILLFACTOR = 50, PAD_INDEX = ON)

Reapplying the Fill Factor

When might you need to reestablish the fill factor for your indexes or data? As data gets modified in a table, the value of FILLFACTOR is not maintained at the level specified in the CREATE INDEX statement. As a result, each page can reach a different level of fullness. Over a period of time, this can lead to heavy fragmentation in the database if insert/delete activity is not evenly spread throughout the table, and it could affect performance. In addition, if a table becomes very large and then very small, rows could become isolated within data pages. This space will likely not be recovered until the last row on the page is deleted and the page is marked as unused. To either spread out rows or to reclaim space by repacking more rows per page, you need to reapply the fill factor to your clustered and nonclustered indexes.

In environments where insert activity is heavy, reapplying a low fill factor might help performance by spreading out the data and leaving free space on the pages, which helps to minimize page splits and possible page-locking contention during heavy OLTP activity. You can use Performance Monitor to monitor your system and determine whether excessive page splits are occurring. 

A DBA must manually reapply the fill factor to improve the performance of the system. This can be done by using the ALTER INDEX command discussed earlier or by dropping and re-creating the index. ALTER INDEX is preferred because, by default, it applies the original fill factor specified when the index was created, or you can provide a new fill factor to override the default. The original fill factor for an index is stored in sys.indexes in the fill_factor column. In addition, if you use the ALTER INDEX command to reorganize or rebuild your table or index, it attempts to reapply the index’s original fill factor when it reorganizes the pages.

Disabling Indexes

Another feature available in SQL Server 2008 is the capability to set an index as disabled. When an index is disabled, the definition of the index is maintained in the system catalogs, but the index itself contains no index key rows. Disabling an index prevents user access to the index. Disabling a clustered index also prevents access to the underlying table data.

You can manually disable an index at any time by using the ALTER INDEX DISABLE statement:

ALTER INDEX titleidind ON sales DISABLE

The reasons you might want to disable an index include the following:

• Correcting a disk I/O or allocation error on an index page and then rebuilding the index later

• Temporarily removing the index for troubleshooting purposes

• Saving temporary disk space while rebuilding nonclustered indexes

When you disable an index, the index is not maintained while it is disabled, and the Query Optimizer does not consider the index when creating query execution plans. However, statistics on a disabled nonclustered index remain in place and are updated automatically if the AutoStats option is in effect.

If you disable a clustered index, all nonclustered indexes on the table are automatically disabled as well. The nonclustered index cannot be re-enabled until the clustered index is either enabled or dropped. After you enable the clustered index, the nonclustered indexes must be explicitly enabled unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement. Because the data rows of the table cannot be accessed while the clustered index is disabled, the following operations cannot be performed on the table:

• SELECT, UPDATE, DELETE, and INSERT

• CREATE INDEX

• CREATE STATISTICS

• UPDATE STATISTICS

• ALTER TABLE statements that modify table columns or constraints

After an index is disabled, it remains in a disabled state until it is rebuilt or dropped. You can enable a disabled index by rebuilding it by using one of the following methods:

• ALTER INDEX statement with the REBUILD clause

• CREATE INDEX with the DROP_EXISTING clause

• DBCC DBREINDEX

To determine whether an index is currently disabled, you can use the INDEXPROPERTY function (a value of 1 indicates the index is disabled):

select indexproperty(object_id('sales'), 'titleidind', 'IsDisabled')


-----------
1

Managing Indexes with SSMS

So far, you’ve seen the commands necessary for index management. In addition to these commands, SSMS provides tools for managing indexes.

To reorganize or rebuild an index using SSMS, in the Object Explorer, connect to an instance of the SQL Server 2008 Database Engine and then expand that instance. Then expand Databases, expand the database that contains the table with the specified index, and expand Tables. Next, expand the table in which the index belongs and then expand Indexes. Finally, right-click the index to rebuild and then click Rebuild or Reorganize. To rebuild or reorganize all indexes on a table, right-click Indexes and select Rebuild All or Reorganize All.

You can also disable indexes in SSMS. In the Object Explorer, right-click the index you want to disable and then select the Disable option. To disable all indexes on a table, right-click on Indexes and select Disable All.

You can also use SSMS to modify indexes. In the Object Explorer, right-click the index you want to modify and then click Properties. In the Properties dialog that appears (see Figure 1), you can add or remove columns from the index, change the uniqueness setting, set the index option, set the fill factor, rebuild the index, view the index fragmentation, reorganize the index, and so on.

Figure 1. Setting and viewing index properties in SSMS.