SQL Server index can be considered as a double-edged sword. When the index is designed and maintained well, it can be used to speed up the data retrieval operations from the database tables, by providing swift access to the table rows, with no need to perform a full scan on the table’s data. On the other hand, the index may cause performance degradation on your system and slow down the data insertion and modification operations, when this index is badly designed.
In this article, we will see how to configure the index Fill Factor, which is considered as one of the most important configuration options that should be configured properly to take advantage from the performance enhancements resulted from the index creation.
Fill Factor Overview
SQL Server index is structured logically in the shape of a B-Tree structure, which consists of three main levels: the top or Root level nodes, the bottom level or Leaf level nodes and the nodes located between these two levels are called the Intermediate level nodes. For more information about the SQL Index structure, check the SQL Server Index Structure and Concept. Physically, the SQL index will be structured in the shape of 8KB data pages, which is the smallest unit of data storage in SQL Server, with each page can contain one or multiple index rows, based on the single row size.
When a new row is inserted, or an existing value is modified, the data should be stored in the correct location, due to the fact that the index is a sorted copy of the underlying table. If the index page is full, or the inserted or modified data does not fit in the available space in that SQL Server index, SQL Server will split the data page into two pages and move about half the data to the new page, then insert the new data in the correct logical place. The page split process is an expensive process that holds a lock on the data pages, and reserves more space to store smaller data size, due to that space remains in the split pages. On the other hand, the split process will generate more pages, about twice the original number, that should be read in order to retrieve the index’s data that results in more I/O operations, higher memory and CPU consumption and overall performance degradation.
When a new index is created, or an existing index rebuilt, the Fill-Factor option value, between 1 and 100, will specify the percentage of space that will be filled with data on each leaf-level page, and the rest free space on each page will be reserved for future page growth. For example, if the index page Fill-Factor value is set to 90, this means that 90% of the leaf-level page will be filled with data, and 10% of each leaf-level page will be left empty, to help in the index expansion when a new data is inserted or the existing values are modified, without the need to split the index page into two pages. In this way, the index Fill-Factor option will help in tuning the SQL Server index data storage and enhance data retrieval performance.
Another important concept that is tightly coupled with the Fill-Factor option is the PAD_INDEX option. When you create a new index or build an existing one, SQL Server will provide you with an option to apply the Fill-Factor value to all index intermediate layers, by setting the PAD_INDEX value to ON. The default value for PAD_INDEX is OFF.
The default value for the index Fill-Factor option is 0. Similar to 100, this value means that the leaf-level pages of the index will be completely filled, with no empty space left for the index growth in the future. For the OLTP systems that are continuously modified, the index should be rebuilt using a non-default value of the Fill-Factor option, in order to reduce the potential and frequency of the page split issue, by providing enough margin space that helps in the index expansion within the same data page. The optimal value for the index Fill-Factor depends on the ratio of the reads operations to the write operations that are performed on the SQL Server index and underlying table. For example, Fill-Factor percentage of 100% will be suitable for a table with low modification percentage, Fill-Factor percentage of 50%-75% is suitable for a table with high modification percentage and 80%-90% Fill-Factor value is suitable for a table with alternating read and write workload.
When setting the Fill-Factor value, you need to consider also the characteristic of the insertion process. For example, if the index is created on an INT or BIGINT IDENTITY column, the key value for any new row will be added to the end of the index. In addition, such IDENTITY columns will be rarely modified or deleted. In this case, setting the Fill-Factor to 0 or 100 that fills the SQL Server index page completely, is the optimal choice, and no need to waste the space of the index pages and affect the read operation performance, due to spreading the data into more pages that requires more I/O operations and extra memory allocation, by setting the Fill-Factor to non-default values.
Fill Factor Configuration
SQL Server provides us with different ways to configure the Fill-Factor value for the SQL Server index at both the SQL Server instance level, and at the SQL index level. To be able to configure the Fill-Factor value, the user should be a member of the sysadmin fixed server role, or db_ddladmin and db_owner fixed database roles.
Instance Level Fill-Factor Setting
Setting the Fill-Factor value at the SQL Server instance level is not the best practice, as this value will be used for any new index regardless of the read and write workload performed on the underlying table. The Fill-Factor value can be configured at the SQL instance level, from the Database Settings page under the Server Properties window, as shown below:
The Fill-Factor can be also changed at the SQL Server instance level, using the sp_Configure T-SQL command below:
EXEC sys.sp_configure N'fill factor (%)', N'90' GO RECONFIGURE WITH OVERRIDE GO
SQL Server Index Level Fill-Factor Setting
The Fill-Factor value of a specific SQL index can be set from the Options page under the Index Properties window, as shown below:
You can see also that, from the same page, you can apply the same Fill-Factor percentage value to the SQL index intermediate levels by turning on the Pad Index options. Take into consideration that, changing the Fill-Factor and Pad-Index values require the index to be rebuilt, as shown below:
The Fill-Factor and the Pad-Index options can be also set using the ALTER INDEX REBUILD T-SQL command below:
USE [AdventureWorks] GO ALTER INDEX [PK_AWBuildVersion_SystemInformationID] ON [dbo].[AWBuildVersion] REBUILD PARTITION = ALL WITH (PAD_INDEX = ON, FILLFACTOR = 90) GO
SQL Server Index Level Fill-Factor Setting Using ApexSQL Defrag
Configuring the Fill-Factor option at the SQL Server instance level is not a best practice as it will be applied on all newly created indexes without being able to customize it easily. In addition, configuring the Fill-Factor at the index level using the Index Properties window requires a big effort to customize the setting based on the best practices discussed previously. From this point, there is a need for a centralized console from where we can view statistical information about all database indexes, group it based on specific criteria and configure the Fill-Factor and the Pad-Index options at one shot.
ApexSQL Defrag tool is a 3rd party SQL index defragmentation tool that can be easily used to check SQL Server index information and usage statistics and perform the proper maintenance task to keep indexes functioning well. It provides us with the ability to analyze and review the fragmentation and usage information at all levels, starting from the SQL Server instance upper level and diving down till the individual index level. From one centralized location, ApexSQL Defrag also allows us to group the database indexes based on specific criteria and configure the Fill Factor setting on the specified list of indexes.
ApexSQL Defrag can be easily downloaded from ApexSQL Download Center and installed to your machine by following the straight-forward installation wizard, in which you will be asked to provide the service account for the ApexSQL Defrag agent, the installation path for ApexSQL Defrag and finally if you plan to create a shortcut icon for the tool in the desktop for faster access, as shown below:
After installing ApexSQL Defrag to your machine, you will be asked at the first run to confirm the creation of the central repository database, in which all indexes information and tool configurations will be saved.
Add New Server
To check and configure the SQL Server index Fill-Factor and Pad-Index options on a specific SQL Server instance, the server should be added first to the ApexSQL Defrag tool. To do that, click on the Add button, under the Home tab, as follows:
A new window will be displayed, in which you will be requested to provide the SQL Server instance name, the authentication mode and the privileged user information to be able to connect to that server and check the indexes information, as below:
If the provided connection information is valid, ApexSQL Defrag will connect to the SQL Server instance, list all instance databases, and display statistical information about all available indexes, as shown below:
Configure Fill-Factor using ApexSQL Defrag
ApexSQL Defrag provides us with the ability to group the SQL Server indexes based on specific criteria, by dragging the name of the column and drop it at the top of the summary window. For example, the previous indexes information can be grouped by the name of the table, as shown below:
Grouping the result by the name of the table helps classifying it based on the workload type performed on that table, which makes it easier to configure the optimal Fill-Factor values per group. In ApexSQL Defrag, the Fill-Factor option can be configured using the Fill Factor button, under the Fragmentation tab, as shown below:
Assume that we plan to change the Fill-Factor option for all indexes under the Address and AddressType tables to 90, to reduce the frequency of the page split operations resulted from inserting new records or updating the existing records with values that do not fit in the available space.
To achieve that, check all indexes under these two tables then click on the Fill Factor button under the Fragmentation tab. In the displayed Set Fill Factor window, provide a new value for the Fill-Factor option, specify to rebuild the index using Online or Offline mode and whether to apply the same percentage on the all index intermediate levels by turning on the Pad-Index option.
To apply the Fill-Factor and Pad-Index changes, the selected indexes will be rebuilt and then you will be notified that the changes are applied successfully. If the changes are not replicated directly to the SQL Server Indexes information window, click on the Refresh button, under the Fragmentation tab, and check the new values for the Fill-Factor option on the page, as shown below:
Another scenario is changing the Fill-Factor value for the highly fragmented SQL Server indexes. To achieve that, group all indexes under your database based on Fill Factor column value then select all indexes with fragmentation value larger than 50%. After checking the database indexes, click on the Fill Factor button under the Fragmentation tab, provide the proper value for the Fill-Factor option, the rebuild mode and to whether to enable the Pad-Index option, under the Set Fill Factor window.
ApexSQL Defrag will rebuild the selected SQL indexes in order to apply the Fill-Factor and Pad-Index changes. Monitor the indexes rebuild status from the progress bar at the right bottom part of the window, where you will be notified when the changes are applied successfully, as shown below:
You can be more accurate and perform the Fill-Factor configuration for the SQL Server indexes with a high fragmentation percentage with index size larger than a specific value. To achieve that, group the indexes information based on the fragmentation percentage and the size column values, select the indexes with high fragmentation percentage and large size then click on the Fill Factor button under the Fragmentation tab. Provide a proper Fill-Factor value, specify the index rebuild mode and if the Pad-Index option will be enabled. The SQL Server will rebuild these indexes and change the Fill-Factor value on these indexes to the new provided value, as shown below:
To be more specific, you can dive deeper and perform the Fill-Factor changes for a specific type of SQL Server indexes with high fragmentation percentage and index size larger than a specific value. To do that, group the indexes information based on the type of the index, Clustered or Non-Clustered, the fragmentation percentage, and the size column values. Check the Clustered indexes with high fragmentation percentage and large size then click on the Fill Factor button under the Fragmentation tab. For the checked indexes, provide a proper Fill-Factor value, the index rebuild mode and whether the Pad-Index option will be enabled or not, under the displayed window. After that, these SQL indexes will be rebuilt to have the Fill-Factor new value applied, as shown below:
For the Non-clustered indexes with high fragmentation percentage and large size, check these indexes and click on the Fill Factor button under the Fragmentation tab to follow the same previous steps to set the proper Fill-Factor value for the checked indexes, as shown below:
Summary
We walked through different aspects of configuring fill factor for SQL Server indexes and hope this article will help you in gaining essential understanding on the topic.
May 29, 2019
