February 8, 2010 at 2:59 am
Hi Everybody,
In FillFactor i set 90 %
now how much data can i enterd in that page. Once 90% page space is over next i insert new records what will happen, Then page is splitting or the same page insert the record. Once page is splitting what is the use of FillFactor.
February 8, 2010 at 3:14 am
The fill factor has no effect at all when you insert the data. This means that if your fill factor is 90 percent, and you insert data into a page that is already more then 90 percent full, the data will be inserted into this page. Page split will occur only when you’ll try to insert data into a page that can’t doesn’t have enough space to hold this data. The fill factor has effect only when you rebuild or reorganize the index. When you’ll use one of this operations, your index will have free space in it according to the specified fill factor.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2010 at 3:35 am
Hello Adi,
Your explanation is very much correct and I guess it's with respect to non clustered index. What about clustered index?
Have a nice day!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 8, 2010 at 4:38 am
The explanation is valid for both - Clustered and None clustered indexes.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2010 at 5:16 am
addition to the info:
- with a clustered index: correct for the data level of the pages.
however the intermediate lvl is controlled by pad index. if it's off it will be filled max and on a rebuild and leave only 1 record room in the indexpages.
with a non clustered index there is no data lvl of the pages as it has pointers to the clustered index or the heap page. heap page has no order so fillfactor is not relevant here. for the combo non clusted with padindex=on i dont know what would happen.
February 8, 2010 at 5:32 am
Filling up of a page is totally depend on the row size of that particular table.
Just to add sql server can store only 8060 bytes of data on a single page.
February 8, 2010 at 8:36 am
Eduard (2/8/2010)
addition to the info:- with a clustered index: correct for the data level of the pages.
however the intermediate lvl is controlled by pad index. if it's off it will be filled max and on a rebuild and leave only 1 record room in the indexpages.
with a non clustered index there is no data lvl of the pages as it has pointers to the clustered index or the heap page. heap page has no order so fillfactor is not relevant here. for the combo non clusted with padindex=on i dont know what would happen.
I have to admit that we are in a small disagreement here. Both clustered and none clustered index have a leaf level. For clustered index the leaf level is the data itself. For a none clustered index the leaf level is a pointer to a record in the table (different pointers in case of heap or clustered table, but this is not relevant for this issue). In both cases (clustered index and none clustered index), the index is sorted. You can use fill factor when you create none clustered index and it will effect that index only (e.g. other indexes won’t be effected and data won’t be effected). On both index types (clustered and none clustered) the fill factor effects the leaf level only. If you specify with pad_index = on, then the fill factor will be used in the intermediate index page as well as the leaf level.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2010 at 9:34 pm
Thanks Adi...
February 9, 2010 at 1:40 am
an article with more info:
Cluster That Index! Part Two
http://www.sqlservercentral.com/articles/Indexing/clusterthatindexparttwo/1094/
February 10, 2010 at 3:39 pm
Fill Factor should be chosen depending data modification on the table so that page splits won't occur often resulting in fragmentation. It is just percentage of page you want to allocate for data filling initially, unused resevered space will be used for index expansion after data modification.
EnjoY!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply