October 29, 2009 at 8:07 am
Hello,
i read many articles about indexes and the options Fill_Factor and Pad_Index, but one thing is still not clear for me.
In the intermediate level of clustered indexes, there are indexpages and in the leaf level, there are the datapages.
In the intermediate level of non clustered indexes, there are indexpages, but in the leaf level, there are references to either indexpages of the clustered index or iam-pages of the heap.
So far as good.
I have found in Microsoft, that I can control the size of the content of indexpages with the option Fill_Factor.
With Pad_Index I can control, if the size of the content in the leaf level should also be used for the size of the content in the non leaf level, so in the intermediate level.
And here is my question:
Because of the fact, that the leaf level of clustered indexes contains datapages, can i control with Fill_Factor the size of the content in datapages, too, at least at the clustered index?
Or does Fill_Factor not affect at clustered indexes?
I hope, anyone can help me.
Many thanks in advance.
October 29, 2009 at 9:07 am
yes, fillfactor affects clustered indexes. It is there to either fill the pages for quicker IO (few changes) or make them sparse (low fillfactor) to allow for inserts and prevent page splits.
It's not about controlling size, it's about I/O
October 30, 2009 at 5:04 am
ok.
But another one told me, that Fillfactor also affects at non clustered indexes. Are you sure?
And i read in MSDN, that Fillfactor defines how much space should be left in Datapages. That is the fact, that i wanted to say with the sentence: "With Fillfactor you can control the size of pages."
October 30, 2009 at 8:16 am
With fillfactor you control how many rows, or how much data, goes into the pages. It could be about size, but don't think of it that way. It's a case of deciding how much data to put onto a page.
Think of a series of measuring cups. If you fill them all the way to the top, then you can't put more in. If you need to add more, you need a new cup (page). If it has to go in some place in the middle, you have to move the other cups, add a new one, and then fill it.
but if you choose to fill them halfway (50%fillfactor) then you can add more to any cup if you need to.
That's what fillfactor is. If you need to add to the middle of an index (CI or NCI), you can do it if the fillfactor is lower.
October 30, 2009 at 8:30 am
thats a good description, thank you 🙂
October 30, 2009 at 8:38 am
you are welcome.
A couple other notes. Look at the row size for your table, or index, and then compute how many per page. Look at page splits, and then as you rebuild indexes, try to reduce them with a lower fillfactor if you find that inserts/updates seem to take a long time.
Or you get lots of fragmentation. you can check this with dbcc showcontig.
However the lower fillfactor can mean more reads to get the data in, so you have to watch query times as well.
October 30, 2009 at 8:47 am
yes i´ve already known that.
What i have in my mind:
OLTP-Applications => mostly better lower Fillfactor
OLAP-Applications => mostly better higher Fillfactor
October 30, 2009 at 9:35 am
That's correct. If you have read only data, go 100% and filll the pages completely.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply