Simple fill factor question

Question

Simple fill factor question

Siten0308

SSCarpal Tunnel

Points: 4409
More actions
February 19, 2016 at 10:33 am

#308230

Hello everyone,
hope everyone is doing well, I have a stupid question, probably thinking into this too much but its about fill factor, i read it on:
https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/
love the site, but i have a question when they say:
“Fillfactor” is a setting for indexes in SQL Server. When you create or rebuild an index, you can tell SQL Server what percentage of each 8K data page used in the “leaf” level of the index it should fill up.
In other words, if you set a 90% fillfactor when you rebuild a clustered index, SQL Server will try to leave 10% of each leaf page empty. The empty space will be present on the page as it’s written on disk, and it’ll take up the same amount of space as it sits in memory.
so my 2 questions are:
1. so in the page, do you specify how much of the index will be on the page? and the rest is for the data row on the page?
2. since you specify how much percentage of 8k of the index should fill up, the index holds obviously the data that has been indexed correct?
thanks in advanced.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You might be missing the fact that clustered indexes are actually the data from the table.

Fill factor is a setting for building or re-building indexes. So it will only affect on those operations and not on the common use of the indexes. The percentage free on the pages is meant to be used by new inserts or updates and prevent page splits. A static, ever-increasing clustered index shouldn't have any page splits in previously written pages. When the index is not ever-increasing and you might write on previous pages, then the available space left after an index rebuild is used to be able to write on those pages. If there's no space, a page split occurs.

Those this solves your questions?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 2

It's also worth mentioning that each index has its own set of pages that are not the table's data pages(other than the clustered index). the only data on those would be the columns in the index and any included columns.