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 GO SELECT DB_NAME() AS DatabaseName , ss.