I have been trying to solve the puzzle of “How to evaluate my current Fill Factor setting?”, and there have been certain hurdles that came along the way to understand the relationship between Fill Factor and Page Splits.
Page Splits/Sec counter in windows is something we all know about. It will tell us the number of Page Splits happening in the entire SQL Server instance. Relying on Page Splits/Sec on production environment was more based on assumptions rather than on some concrete data.
One way was to capture increasing values of leaf and non-leaf allocation counts from sys.dm_db_index_operational_stats and relate it to page counts in sys.dm_db_index_physical_stats.
Using sys.dm_db_index_physical_stats in large databases (like my production environment) can be quite a task and may take tons of valuable time to retrieve information related to total pages and rows in the table. Based on my analysis (also shown below), we can refer to sys.dm_db_partition_stats for information related to pages used\total rows in each index.
I am going to use three scripts that will cover different fill factor settings and capture its impact on the page splits that occur. The scripts are executed on tempdb.
NOTE: An attempt has also been made to establish a relationship between allocation counts and page counts in different dmv’s as explained later in the article.
FILL FACTOR SET TO 60
Run the below script with fill factor 60
The query results are in the below screenhot. It shows the leaf and nonleaf page allocation data after the index has been created. Also, take note page count and total rows. We’ll see the relevant data increase after executing scripts to cause page splits.
With the baseline data available, run the below script.
The query results
There is an increase in the page allocation data of the index. It actually refers to the additional number of pages used by the index (Page Splits). Take note, the page count is affected by the page allocation counts.
FILL FACTOR SET TO 85
For comparative analysis, let’s increase the fill factor setting to 85 and measure its impact on page splits. We’ll change the fill factor on both the indexes.
On comparing the results with when the fill factor was set to 65, the main difference is the change in the page count related data. The in_row_data_page_count has reduced from 334 to 228 for the clustered index and 228 to 162 for the nonclustered index. The page count reduces as the amount of free space in pages while creating the index has been reduced from 40 (fill factor 60) to 15 (fill factor 85).
The script results show that increasing the fill factor from 60 to 85 resulted in an increase in the allocation count for each index which represents page splits.
FILL FACTOR SET TO 95
After inserting the same number of records, there is a further increase in the allocation count and page count numbers in both dynamic management views sys.dm_db_index_operational_stats and sys.dm_db_partition_stats. By reducing the free space to 5% there is an expected increase in the allocation counts. Take note in the above scenarios, after every second set of inserts, the leaf_allocation_count and nonleaf_insert_count returns same values.
TakeAways:
The variation in page allocation and page counts is not very significant in the above mentioned scenarios. However, if we do capture this information on a regular basis to identify trends in tables that accommodate millions of rows and have an input (INSERT\UPDATE) of significantly less numbers over a period of time.
Another valuable tip can be to keep in mind as the number of rows in table grows from thousands to millions and the DML operations on the same table have not increased significantly over a period of time, you can increase the fill factor setting accordingly. E.g. the number of rows increased from 100000 to 1000000 in a year and the data inflow is still around 2000-3000 rows a day. The fill factor setting required during the initial part of the year should be different as compared to the setting required by the end of it to control page splits better.
DISCLAIMER:
The above scenarios might not hold true in all environments. The above scenarios did not take into account tables where frequent updates\deletes are carried out. The recommendation is to capture data on a regular basis and analyze.
Related Articles:
