Fill Factor in sql server performance tuning server level, index/table level

Fill Factor:

Working in performance tuning area, one has to know about Index and Index Maintenance. For any Index the most important property is Fill Factor. Fill Factor is directly related to Indexes.

Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of Page with size 8K.

A page is the basic unit of data storage in SQL server. Its size is 8KB(8192 bytes). Data is stored in the leaf-level pages of Index. The percentage of space to be filled with data in a leaf level page is decided by fill factor. The remaining space left is used for future growth of data in the page. Fill factor is a number from 1 to 100. Its default value is 0, which is same as 100. So when we say fill factor is 70 means, 70% of space is filled with data and remaining 30% is vacant for future use. So higher the fill factor, more data is stored in the page. Fill factor setting is applied when we create/rebuild index.

Page Split:

Page split occurs, when there is no more space to accommodate data in leaf-level pages. Page split is movement of data from one page to another page, when data changes in the table.

Fill factor and its effect on performance:

From above, we have gathered some idea about fill factor. Now let see how fill factor can performance. From above discussion we can say if we have higher value of fill factor, we can save more data in a page. By storing more data in less space, we can save memory, resource uses(like IO, CPU).

But downside of it is page split. Page split hamper performance.

Now let us discuss about low value of fill factor. By setting low value of fill factor, page split can be avoided. But it will need more memory(although memory is cheap now), more resource uses. Let say we have a table where the data was fit in 50 pages when the fill factor setting was 100 or 0. Now let say we reduced the fill factor to 50. So it will take 100 pages. When we need to read all the rows, the number of read is doubled now.

How to decide fill factor?

What is correct value of fill factor, we need to set for better performance ? There is no specific answer. It depend upon your application. You are the best person to decide its value. Below is the criteria you need to consider while choosing fill factor.

1:For static/look-up table: This type of tables have static data means data changes very rarely in the table. So we can set high value of fill factor means 100 or 0.

2:For Dynamic table: In this type of table, data get changes(inserted/updated) frequently. So we need to set low fill factor, between 80 to 90.

3:For Table with Clustered Index on Identity Column: Here the data is inserted at the end of the table always. So we can have higher value of fill factor between 95 to 100.

How to set Fill factor ? We can set fill factor in 2 ways 

1) Server level:

A generic fill factor setting is applied at the server level for all table/index. To see what is the current current default fill factor set at the server level, you can use below script.

 
Hide Copy Code​EXEC [sys].[sp_configure] 'fill factor' GO

You can set a server level default fill factor by using sp_configure with a parameter as below script. Here are setting a fill factor value of 90.

EXEC sys.sp_configure 'fill factor', 90 

GO 

RECONFIGURE WITH OVERRIDE 

GO

2) At Index/Table level: 

While creating/rebuilding index we can set a specific fill factor. We can use below script to set fill factor while rebuilding index.

USE YourDatabaseName 

GO 

ALTER INDEX YourIndexName ON [YourSchemaName].[YourTableName] 

REBUILD WITH (FILLFACTOR = 80); 

GO

Points of Interest

Index play an important role to increase the performance of the query. But it is not only the magic pain killer. As fill factor is related to Index, so it has some role on performance improvement. Before setting a fill factor, analyses your requirement, do experiment with different fill factor value and finally set correct value.

Measuring Counters

All of the above discussions lead us to think that a higher Fill Factor and high transaction server implies higher page split. However, Fill Factor can help us to reduce the number of the page splits as the new data will be accommodated in the page right away without much difficulty and further page splits. We can measure the page split by the watching performance monitor counter

“SQLServer:AccessMethods:Page Splits/Sec”.

Additionally, you can measure the same using the following sys query.

SELECT cntr_value FROM MASTER.dbo.sysperfinfo WHERE counter_name ='Page Splits/sec' AND OBJECT_NAME LIKE'%Access methods%'

Fill factor is usually measured at the server level as well as table level. Below we have the scripts for the same.

Here is the script to measure the Fill Factor at the server level:

SELECT * FROM sys.configurations WHERE name ='fill factor (%)'

And, here is the script to measure the Fill Factor at the table/index level:

USE YourDatabaseName; SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factor FROM sys.indexes

SQL SERVER – Set Server Level FILLFACTOR Using T-SQL Script

SQL Script to set Server level FILL FACTOR to 90

EXEC sys.sp_configure 'show advanced options', '1'

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure 'fill factor (%)', '90'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure 'show advanced options', '0'

RECONFIGURE WITH OVERRIDE

GO




For Example Using a table named tblTransactionTable.

The image below displays the sp_spaceused command and the output from the command to display table information such as space used for the table and the underlying indexes. This table has 11.7 million rows and there are no indexes currently on this table.

Step 1 - High Fill Factor Value

From SQL Server Management Studio, I am creating a non-clustered index with one column, CustomerID, as the Index Key.

I then move to the Options page and set the Fill Factor to 100 %. If the value for the fill factor is not set, the index is created using the value from the default index fill factor value set at the instance level. Remember that a value of 0 and 100 are both same and the page will be completely filled. For this example we will ignore the other options on this page. I finish creating the index by clicking OK.

After creating the index, I run sp_spaceused again to view the table information and find that the index size is now 327040 KB, where before it was 72KB before we created the index.

You might choose a high fill factor value when there is very little or no change in the underlying table's data, such as a decision support system where data modification is not frequent, but on a regular and scheduled basis. Such a fill factor value would be better, since it creates an index smaller in size and hence queries can retrieve the required data with less disk I/O operations since it has to read less pages.

On the other hand if you have an index that is constantly changing you would want to have a lower value to keep some free space available for new index entries. Otherwise SQL Server would have to constantly do page splits to fit the new values into the index pages.

Step 2 - Low Fill Factor Value

Next, I drop the index and create the same index with a change in the fill factor value set to 50. When I created the index earlier (in Step 1), I chose to script the create index task which is used in this step (image below) which will change the fill factor value to 50%.

The resulting index will be created with pages being 50% full. I check and confirm this by running sp_spaceused again which displays the table details. The image below is the result set displayed when the query is executed. Notice that the index size is now 651640 KB which is approximately twice the size of the index created earlier using fill factor as 100. Which makes sense since we left the pages half empty.

Using a fill factor value of less than 100 would be necessary in situations where data is added to the underlying indexes more frequently. With new data records added, the index pages need to have sufficient space to take the new entries. When there is not enough space a page split needs to occur which could impact performance depending on how frequently page splits need to occur.

Some points to be remembered

• When 0 or 100 is specified, the page is filled to the maximum possible, not 100 % since a page may not accommodate the Index Key the final time and results in leaving that much empty space unused.
• The size of the index is proportional to the columns that are used in the index, hence more columns (the limit being 16) will create a larger index and therefore require more index pages to store the index
• Since indexes need storage space, creating an appropriate index along with the fill factor should be well planned.
• Before finalizing a fill factor value, it should be tested and analyzed before deploying on the actual SQL Server.