100% Fill Factor. When to use it?

Question

100% Fill Factor. When to use it?

Will1922

SSCertifiable

Points: 7189
More actions
September 20, 2005 at 1:50 pm

#110926

I have a table that's heavily queried and updated. Deletes and inserts are probably less than a half dozen times a year and the system is locked to users at those times. I have three unclustered indexes set at 100% fill factor. Would there be any advantage to reducing the fill factor? It is probably 50/50 split on the #of rows updated and the #of rows queried.
Live to Throw
Throw to Live
Will Summers

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

If table is static, or updates are not Performed on variable length columns then 100% ff is fine.

But if updates are performed on variable length columns (Varchar)then a value of less than 100% is desired, what value depends on your row size, and rows per page. If you can fit say 10 rows per page, and the variable column size is say varchar(100) then 90-95% ff should be okay. but if you can only fit 4 rows per page, and the variable column is varchar(4000) then you will want something like 50-60%.

If there are alot of updates/inserts then you want something in the neighborhood of 70-80% but it all depends on rows/per page, and transactions/min.

FF just minimizes "Torn" pages, Its like having a page in a dictionary. if its full and you have to add a new word (Or make the definition of a word longer), some of the words have to spill onto another page. that takes sql server time to move the information. If there is some space left the word fits on the page, and it doesn't have to move anything. It doesn't effect the reads as much.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

>>I have three unclustered indexes set at 100% fill factor.

Do you have a clustered index ?

Will1922 SSCertifiable Points: 7189 More actions · Answer 3

I do not have a clustered index on this table. This is because the table is heavily updated and selected. There are very few inserts or deletes on the table.

I was trying to optimize the table by limiting the amount of activity that would happen when a key field of the clustered index was modified and the rows would have to be reordered.

I usually have clustered indexes on all my tables, except this one. This is the only one that has this kind of activity on it.

Live to Throw
Throw to Live
Will Summers

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

The major thing about 'updates' and their performance is whether or not they are performed 'in place', i.e. overlaying the data (just writing it out). If you're not performing an 'update in place' the following activities take place:

marking that area on the page deleted
performing a 'space hunt' and possbly a page reorganization
write the newly updated row

The factors that detrmine whether or not an 'update in place' are:

are there varchar column(s) --> if yes, no 'update in place'
are there null-able column(s) --> if yes, no 'update in place'

So in addition to the 'fill factor' the actual column datatypes and attributes of the table also has a great deal to do with performance.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Will1922 SSCertifiable Points: 7189 More actions · Answer 5

I did not know about the 'update in place' rule. It makes sense about the varchar, but why the NULL?

Live to Throw
Throw to Live
Will Summers

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

It has to do with how the row of data is actually constructed and maintained on the data page. In essence, NULL columns are treated as varchar columns. Scan BOL for these 3 articles:

On the Index tab enter: space allocation

select the 3 articles that begin with: Estimating the size of a table ...

The reading is a ad dry, but informative.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."