How to find FILL FACTOR of all indexes in SQL Server

What is fill factor? Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In SQL Server, the smallest unit is a page, which is made of Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100 which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no space left in the page, when the fill factor is set to 100.

Why we need to know? When the fill factor is set to 100 or some higher number for a high transaction table then all the pages in that table have less space to cater update to any existing data hence it will start splitting the pages. We can measure the page split by the watching performance monitor counter “SQLServer:AccessMethods:Page Splits/Sec”


USE master --Change the database name here
GO
SELECT DB_NAME() AS DBName
, sc.name AS SchemaName
, o.name AS TableName
, o.type_desc [Type]
, i.name AS IndexName
, i.type_desc AS IndexType
, i.fill_factor AS [FillFactor]
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
ORDER BY o.name,i.fill_factor DESC

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
28 + 23 =