In this article, we will study in detail about the how SQL Server Index Fill factor works.

Index Fill factor

SQL Server Index Fill Factor is a percentage value to be filled data page with data in SQL Server. This option is available in index properties to manage data storage in the data pages. It plays vital role in Query Performance Tuning. Default value is 0 in SQL Server Index Property with each index of Tables, it prevents 100% storage to be filled in each data page. We can modify that value by performance benchmarking over nature of data and size of data.

E.g. Fill factor value set to ‘100’ will allow 100% data storage occupancy of the data page and ‘90’ will allow 90% storage occupancy of data page and the rest of the space will be free which can be utilized for data modifications on the page and not for new records insert.

Here, performing the small benchmark with an example. I added one table [tbl_user] in [auth] Database.

We will perform benchmark in two stats and analyse the result-set.

• Without Fill factor
• With Fill factor

Without Fill factor

The default SQL Server Index Fill factor value is 0(100%). With the 100% usage of the page could bring the recital issues while performing large sized table or data updates on the table as there will be no space left for data modification/s.

In this situation, how rows will be restructured? will it on the same page or moved to new one? Will be discussed in detail with example and sample data.

Primary key defined on column user_id on the table; check the default values by index Property the Fill Factor value is set to ‘0 (Zero)’, which can be added to Index by SQL command as well while adding index.

1. Query to Get Page Number Allocated to Table:

   Example: 256 rows inserted into table and current Index Fill factor value is 0(100%). The query as below:

   1 2 3 4 5 6 7 8 9

   SELECT a.database_id,     a.object_id,     a.extent_page_id,     a.allocated_page_page_id,     a.next_page_file_id,     a.previous_page_page_id FROM sys.dm_db_database_page_allocations(DB_ID('auth'), OBJECT_ID('tbl_user'),NULL, NULL, 'DETAILED') a INNER JOIN sys.indexes i ON i.object_id = a.object_id AND i.index_id = a.index_id WHERE page_type = 1 --page_type_desc = 'DATA_PAGE'

   

   In query, specified page_type = 1, which returns Data Pages only.

2. We can find number of data pages for the table by DBCC command as well. Query as below:

   1	DBCC IND(auth, tbl_user, -1)

3. Two pages 352 & 384 (Result in the Screenshot above) are allocated to store 256 rows. Now when DBCC PAGE command used it returns the same rows and with this it also returns the Header and Hex dump of data page based on given parameters. Keep in mind before executing Trace Flag ‘3604’ should be enabled in session to get query output in client

   1 2	DBCC TRACEON(3604) DBCC PAGE(db_name/db_id, file id, page_id, 0/1/2/3)

   Fourth parameter will decide the detailed output of this command:

   0 – Page Header only

   1 – Page Header and all rows hex dump with slots

   2 – Page Header and all rows hex dump

   3 – Page Header and row explanation

   1	DBCC PAGE('auth', 1, 352, 1)

   or

   1	DBCC PAGE(9, 1, 352, 1)

Query Output 1.01:

See here, all the field holds quite a meaningful data in PAGE HEADER section. m_slotCnt = 147 represents the number of rows/records in a page and rest of 109 records are placed in second page.

What will happen if we update the rows in first page?

Now let us try to update the data in the existing table by concatenating user_id with email in the email column in table. This will require more space to update the rows on the page, however the Fill Factor is set to ‘100%’ and we have fill the page with 100% data, therefore no free space left on page. Let us update the table and check the source of pages.

After updating table, the list of pages we get for the table using the query returns 3 rows instead of 2 (refer to the previous result-set). Now the data has got distribute in 3 pages and rows are restructured. Even DBCC PAGE command helps to track number of rows in individual page.

Before the update statement run, there were a 147 rows in a table (Refer to Query out-put 1.01) now as the rows got restructured in 3 pages and first page is now filled out with 73 rows. This row split process will take more time to perform this operation and could consume more CPU and Memory resource as well moreover for the heavy transaction-based table, update over the table without SQL Server index Fill Factor could generate locks as well.

Query Output 1.02:

here, the Index fragmentation is also important to notice while working on SQL Server index Fill factor. For this table we have 66.66% Fragmentation count after performing the update on table. Index fragmentation does effects Index performance in SQL Server. Therefore, with higher Fill factor fragmentation value the cost will be high, hence it should be consumed by applying proper amount on this setting. We can check fragmentation by below query.

With Fill Factor:

Fill factor value regulates the percentage of data to be filled in each data page. In this example, SQL Server index Fill factor value defined with 80 on Primary Index means that each page of table will have 20% free space and 80% space will be allocating to store the data. To apply changes in Fill factor will always rebuilt the index.

We have now inserted same 256 rows into same table again to check the allocated number of pages to the table. Data gets split in 3 pages this time whereas it got split in two pages done without Fill factor.

Looking at number of the rows on the first page, we can see 118 rows are stored (Query Output 2.01) as compare with previous result (100% Fill factor) data split in 2 pages on data insertion and 147 rows on the first page (Query Output 1.01).

Here It keeps 20 % room vacant on each page for inserting new row into table according to the Fill factor amount.

Query Output 2.01:

Let us update table again as previous example and check the rows allocation in page structure. Here we find that no changes found in first page and even we can see that more free space has been occupied by existing rows. considered, no pages are reorganized and avoided locks on targeted rows and those page dependent rows.

Query Output 2.02:

Not only the number of the rows remain the same on the page one, even index fragmentation looks good compare to previous one.

Conclusion:

In this example we have used 80% Fill Factor, however it doesn’t make sense to push without any benchmarking for the table. In most cases SQL Server index Fill factor will help to get well performed when Table having large number of rows and frequent update over the rows. Before setting the Fill Factor we need to analyse the Datatype of columns, actual cell size of the rows, Average number of rows in the pages and estimated updated size of row cell. This proper calculation derives an actual Fill factor value which need to be applied on the table.

Without calculation, less Fill factor can degrade the Read operation. unnecessary more pages to find rows in a table consumes more resources and IO. Please be sure to get proper value to be set on Index.

• Author
• Recent Posts

Jignesh Raiyani

Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.

View all posts by Jignesh Raiyani

Latest posts by Jignesh Raiyani (see all)

• Page Life Expectancy (PLE) in SQL Server - July 17, 2020
• How to automate Table Partitioning in SQL Server - July 7, 2020
• Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020