Fill properties for INDEX: FillFactor and Pad_index

When you create INDEX, you must carefully consider the settings for properties FillFactor and Pad_index, which only work when CREATE INDEX or rebuild index , which represents the index The Fill level of page (Leaf-level or Intermediate-level). If you set fillfactor=90, SQL Server does not use the index page to run out of space when you create index or rebuild index, but only uses a page space close to 90%, reserving 10% of free space. The space inside this reserved page is called Internal fragmentation. Reserving a certain percentage of fragmentation for a certain purpose, reduce page split, can improve the performance of data updates, and fragmentation too much, will reduce query performance.

Index is a btree structure, divided into Leaf-level page and Intermediate-level page (that is, Nonleaf-level page).

The FillFactor property specifies the fill Percent,ms recommended fillfactor=90 for index leaf-level pages.

The Pad_index property is a Boolean type that specifies whether FILLFACTOR is used to populate INDEX Intermediate-level Pages.

An explicit FILLFACTOR setting applies if the index is first created or rebuilt. When inserting data, SQL Server uses all of the page's space as much as possible, regardless of the padding properties.

1,pad_index = {on | OFF}

Specifies index padding. The default is OFF.

on  the percentage of free space that's specified by f Illfactor is applied to the Intermediate-level pages of the index.

OFF or fillfactor is not specified         ;            

The Intermediate-level pages is filled to near capacity, leaving sufficient space for at least one row of the maximum size th E index can has, considering the set of keys on the intermediate pages.

The PAD_INDEX option is useful if FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLF ACTOR. If the percentage specified for FILLFACTOR are not large enough to allow for one row, the Database Engine internally Overri Des the percentage to allow for the minimum. The number of rows on a intermediate index page is never less than and regardless of how low the value of fillfactor .

2,fillfactor =FILLFACTOR

Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during in Dex Creation or rebuild. fillfactor must be a integer value from 1 to 100. If FillFactor is a, the Database Engine creates indexes with a leaf pages filled to capacity. The default is 0.

An explicit FILLFACTOR setting applies if the index is first created or rebuilt.

The FILLFACTOR setting applies only if the index is created or rebuilt. The Database Engine does not dynamically keep, the specified percentage of the empty space in the pages. To view the fill factor setting, use the Sys.indexes catalog view.

Important
Creating a clustered index with a FILLFACTOR less than affects the amount of storage space the data occupies because T He Database Engine redistributes the data when it creates the clustered index.

3,specify Fill Factor for an index

The Fill-factor option is provided for fine-tuning index data storage and performance. When a index is created or rebuilt, the Fill-factor value determines the percentage of space in each leaf-level page To is filled with data, reserving the remainder in each page as free space for future growth. For example, specifying a fill-factor value of $ means that percent of each leaf-level page would be is left empty, provid ing space for index expansion as data are added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

The Fill-factor value is a percentage from 1 to $, and the server-wide default is 0 which means that the Leaf-level page S is filled to capacity. Fill-factor values 0 and same in all respects.

4,performance Considerations

Page splits

A correctly chosen Fill-factor value can reduce potential page splits by providing enough space for index expansion as Data is added to the underlying table. When a new row was added to a full index page, the Database Engine moves approximately half the rows to a new page to make The new row. This reorganization is known as a page split. A page split makes the new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. When frequent page splits occur, the index can is rebuilt by using a new or existing fill-factor value to redistribute the Data. For more information, see Reorganize and Rebuild Indexes.

Although a low, nonzero fill-factor value could reduce the requirement to split pages as the index grows, the index would req Uire more storage space and can decrease read performance. Even for a application oriented for many insert and update operations, the number of database reads typically outnumber D Atabase writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can decrease database read performance by an amount inversely p Roportional to the Fill-factor setting. For example, a fill-factor value of $ can cause database read performance to decrease by the times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required To retrieve the data.

Adding Data to the End of the Table

A nonzero fill factor other than 0 or more can good for performance if the new data is evenly distributed throughout the Table. However, if all the data was added to the end of the table, the empty space in the index pages won't be filled. For example, if the index key, column was an IDENTITY column, the key for new rows are always increasing and the index rows a Re logically added to the end of the index. If existing rows is updated with data that lengthens the size of the rows, use a to fill factor of less than 100. The extra bytes on each page would help to minimize page splits caused by extra length in the rows.

5,an Example to specify a fill factor in an existing index

Script1

---with a fill factor of the HumanResources.Employee table.   ALTER INDEX   on with (FILLFACTOR = +);

Script2

CREATE INDEX   on with = on FILLFACTOR =

Reference Documentation:

https://msdn.microsoft.com/en-us/library/ms188783 (v=sql.110). aspx

https://msdn.microsoft.com/en-us/library/ms177459 (v=sql.110). aspx

Fill properties for INDEX: FillFactor and Pad_index