This blog post first appeared on SQLMaestros
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 to postpone the fragmentation. And of course, we should specify the percentage after due testing, otherwise, we will run into different kinds 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 GO SELECT DB_NAME() AS DatabaseName , ss.[name] + '.' + so.[name] AS TableName , si.name 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 WHERE si.name IS NOT NULL 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.
