Fill Factor versus Record Length

Hi Friends,

Almost all of us knows about Fill Factor in SQL Server. For those who don’t know about Fill factor, Book online defines Fill Factor as mention below:

“The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.”

In Short we can say the fill factor defines the leaf level page fullness during index creation and rebuild. The default value for fill factor is 0. For fill factor value 0 and 100 are same. Means leaf level page can be full to its capacity. We also know that available space on a page to store row is 8060 bytes.

Come back to our topic Fill Factor versus Record Length. Did you ever check, what will happen if fill factor define from your side is lower than record length?

Let say, I have defined the fill factor at the instance level (using the link)  to 70% (i.e. 70% of 8060 = 5642 bytes) and I also have one table for which the record length is greater than the fill factor as mention below:

Create table xttest
(
Id int identity(1,1) PRIMARY KEY,
Fname char(3000),
Lname char(3000)
)
GO
Insert into xttest values(‘prince’,’rastogi’)
GO 8

As per the above table, record length will be greater than 6000 bytes which is greater than 5642 bytes (fill factor – 70%). Now the below question will come into the mind:

Is that means my record break and stored on two pages? Let’s check it using sys.dm_db_index_physical_stats dmv.

From the above image output, you can see that storage is IN ROW DATA and the total number of leaf pages are 8. Number of records are 8. The page fullness is almost 75%. That means one record stored per page.

Finally the outcome is, if you specify the fill factor, lower than the record length, then fill factor will be overridden by the record length.

Reference

HAPPY LEARNING!

Regards:

Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook