In our previous blog posts, we have seen how to find fragmented indexes in a database and how to defrag them by using rebuild/reorganize.

While creating or rebuilding indexes, we can also provide an option called “FILLFACTOR” which is a way to tell SQL Server, how much percentage of space should be filled with data in leaf level pages.

For example, if we specify ‘FILLFACTOR = 90’, then 90% of page space will be filled with data and 10% of page space will be left unfilled. “FILLFACTOR = 0 or FILLFACTOR = 100” can be used interchangeably to tell SQL Server to fill the page completely. By default, SQL Server tries to use the complete space available, if you do not specify any value for FILLFACTOR.

This option helps in reducing the number of page splits for some time. In simple words, we can think of FILLFACTOR as a mechanism that helps postponing the fragmentation. And of course, we should specify the percentage after due testing, otherwise we will run into different kind of issues (Memory and IO, CPU overhead).

Now, how to find fillfactor for all the indexes in a database? Here is the query.

USE SQLMaestros
SELECT DB_NAME() AS DatabaseName
, ss.[name] + '.' + so.[name] AS TableName
, AS IndexName
, si.type_desc AS IndexType
, si.fill_factor AS [FillFactor]
FROM sys.indexes si
INNER JOIN sys.objects so ON si.object_id = so.object_id
INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id
AND so.type = 'U'
ORDER BY si.fill_factor DESC

See you soon with another script.

This post is part of The Scripts Garage. You can find all scripts at The Scripts Garage.

Data Platform Virtual Summit 2020

Subscribe to SQLMaestros YouTube channel. If you want more learning content in your inbox, subscribe to SQLMaestros Bulletin.

SQLMaestros YouTube | SQLMaestros Bulletin | SQLMaestros Twitter