Script: Find FillFactor of All Indexes in a Database

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.

   

Leave a Reply

Your email address will not be published.