Fill Factors on Indexes - What does that mean?

In SQL Server, the fill factor is a configuration option that determines how much space is left on each page of an index when it is created or reorganized. The fill factor specifies the percentage of space that will be filled on each page, with the remaining space left as free space.

The fill factor is specified as a percentage value, with a default value of 100. This means that by default, each page of an index will be completely filled with data, with no free space left. However, administrators can specify a different fill factor value to leave some free space on each page.

Leaving free space on each page of an index can provide several benefits, such as:

Improving insert performance:

When a new row is inserted into a page that is already full, SQL Server has to perform an operation known as a page split to make room for the new row. This can impact insert performance, as page splits can be expensive operations. By leaving free space on each page, page splits can be avoided, which can improve insert performance.

Reducing index fragmentation:

When a page split occurs, the data in the index can become fragmented, which can impact query performance. By leaving free space on each page, page splits can be avoided, which can help reduce index fragmentation and improve query performance.

Reducing index size:

When an index is completely filled with data, SQL Server has to allocate additional space to the index to make room for new data. This can cause the index to grow rapidly, which can impact performance and consume disk space. By leaving free space on each page, the index can grow more slowly and use disk space more efficiently.

To specify the fill factor for an index, the administrator can use the CREATE or ALTER INDEX statement, and specify the FILLFACTOR option.

For example:

CREATE INDEX MyIndex

ON MyTable (MyColumn)

WITH FILLFACTOR = 95;

In this example, the fill factor for the MyIndex index on the MyTable table is set to 95%, which means that each page of the index will be filled with data to 95% capacity, with 5% free space left.

By carefully choosing the fill factor value for indexes, administrators can improve performance and use disk space more efficiently. This can help ensure that the database is running smoothly and efficiently.

https://DatabaseHealth.com

Fill Factors on Indexes – What does that mean?