Fill Factor 2

[Work23]

Hello I have this code below. I am confused on what Fill Factor and Pad Index does in this code. Can anyone explain what they actually do in simple terms? I'm confused about their functionality. Thank you very much!

create table #emp
{
sessionid varchar(10),
USERID varchar(10),
SOLDTO varchar(10)
)
CREATE INDEX [ind_1] ON [dbo].[#emp]([sessionid]) WITH FILLFACTOR = 50, PAD_INDEX ON [PRIMARY]

insert into #emp select distinct sessionid, USERID, SOLDTO from #TEMP1 where USERID is not null and SOLDTO is not null

 

[vongrunt]

Both are fine-tuning options that tell server to create index with some unused space. Index gets initially bigger but INSERTs and UPDATEs will likely run faster for some time.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

The fill factor tells the SQL Server that when writting the index data to the disk, only fill the pages on the disk to 50%. When the page is 50% full move on to the next page.

This is done so that when you add new records to the table (and to the index) if the new record should be physically placed within the middle of the index there is room in the page at the middle of the index for the data. If the page at the middle is full then the data is written to a new page.

I'll try to show an example here.

Say we have a table with a single column, and we create an index on that column. The column is called EmpNo (Employee Numbers) and we have a clustered index on the column. The column looks like this.

1
2
3
4
8
9
10
12
15
18
24
...

(Imaging that there is 8k worth of data here to fill the entire page). Now if we have a fill factor of 100% then the page is completly full. So if we enter EmpNo 6 into the table (as well as the index) if can not put the data in the correct page on the disk that it shold be in, so it does what is called a page split and the data is writtin to the next physical page on the disk. If we have a fill factor of say 75% then when the index is created the page is left 25% empty. So when we go to put 6 into the field there is room for in within the correct page on the disk and the data is written in the correct place.

Page splits are bad. They cause performance problems as the disk now has to find the data spread out accross the disk instead of in a nice neat row.

In BOL there is a very cryptic explination of PAD_INDEX

Specifies the space to leave open on each page (node) in the intermediate levels of the index. The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. By default, SQL Server ensures that each index page has enough empty space to accommodate at least one row of the maximum size the index can have, given the set of keys on the intermediate pages. If the percentage specified for FILLFACTOR is not large enough to accommodate one row, SQL Server internally overrides the percentage to allow the minimum.

Frankly I'm not sure how to translate that into English.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[jdgonzalez]

mrdenny,

Thanks for the explanation and a star for you. I've always been a bit confused by this as well. What is a good percentage to set for the fill factor?

 

[mrdenny]

Thanks. The fill factor that you should be using really depends on your database and application.

If the table is never written to, used for read only then set the fill factor higher to save space on the disk. (If the table is written to once per day like in a data warehouse you can still set the fill factor to 100%, just be sure to rebuild the index after the data load).

The data is added to the index mostly in order then you'll want a higher fill factor in the 70-85% range. If data is added all over the index then you'll want a lower fill factor in the 50-70% range.

For tables with lots of changes be sure to keep an eye on your index fragmentation. When a page fills data isn't moved around, the page is split and performance suffers. When your index fragmentation starts to get worse you can defrag the index or delete and rebuild the index (depending on the index size and level of fragmentation it can be faster to remove and recreate the index).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Work23]

Thank you very much for your help. That was a valuable extensive explanation. Thanks again. Take care.

 

[mrdenny]

no problem

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)