SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


fillfactor


fillfactor

Author
Message
charipg
charipg
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2184 Visits: 1193
what is fillfactor and what are the advantages and disadvantages?
ps.
ps.
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6963 Visits: 3668
charipg (7/29/2009)
what is fillfactor and what are the advantages and disadvantages?


Refer this BOL link to know about fill factor.
http://msdn.microsoft.com/en-us/library/ms177459.aspx

A fill factor of 30 means 30% of space within the data page will be kept vacant, so that when you insert more rows later, they would be accomodated within this free space. This means sql server will not ask for new pages to accomodate your data every time you insert and hence pages will be contiguous, leading to better performance.



Pradeep Singh
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95671 Visits: 33013
And fill factor is applicable when creating or rebuilding an index. Once an index is operation or it gets reorganized, fill factor is no longer taken into account.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Animal Magic
Animal Magic
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3526 Visits: 13752
ps (7/29/2009)
charipg (7/29/2009)
what is fillfactor and what are the advantages and disadvantages?


Refer this BOL link to know about fill factor.
http://msdn.microsoft.com/en-us/library/ms177459.aspx

A fill factor of 30 means 30% of space within the data page will be kept vacant, so that when you insert more rows later, they would be accomodated within this free space. This means sql server will not ask for new pages to accomodate your data every time you insert and hence pages will be contiguous, leading to better performance.


Just to continue on Pradeeps response, the disadvantage of a low fill factor is that the index takes up more disk space. the space is allocated ready for the data to go into the page/extent but if you have say 10% fill factor then only 10% is acually used, which means your index will take up 10 times more space than the same index will a fill factor of 100 (or 0, which is the same as 100)

The dissadvantage (which you can probably also work out from Pradeeps response) is that if you have a high fill factor and you have a lot of inserts you will have a lot of page splits which will cause fragmentation (the reason we rebuild/reindex indexes).
Steve Jones
Steve Jones
SSC Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: Administrators
Points: 142406 Visits: 19424
This also impacts performance. Lower fillfactor, more reads to get the data.

You have to find a good balance for heavily accessed tables that accounts for reads and writes.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Katmai Stratovolcano
Katmai Stratovolcano
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 166
hi,

the fillfactor effect specifies the percentage of space filled on index data pages when an index is initially created. The default fillfactor setting of zero, which can be altered at the global server level through a configuration option, will cause an index to be almost filled to capacity, with only a small amount of space being left at the upper level region of the index. A 100% setting completely fills each index page.

One important thing to remember is that this amount is not adhered to after the index is first built. An index can be rebuilt and the original fillfactor setting re-instituted with the variety of DBCC index rebuild or ALTER INDEX (SQL Server 2005) commands.

So, what exactly are the considerations with fillfactor? Higher fillfactor settings should result in less index pages which in turn should result in fewer pages read during scan operations. As been mentioned many times already, less I/O generally equates to better performance.

However, high fillfactor settings can also result in page splits for clustered indexes when SQL Server enforces the sort order of the clustered index during INSERT or UPDATE actions. This happens because SQL Server does not have room on an index page for the requested change, so it has to split the page to perform the modification. This can result in performance degradation and can be confirmed by carefully watching the page splits counter, available from the page_splits query below:

select
cntr_value
from
master.dbo.sysperfinfo
where
counter_name = 'Page Splits/sec' and
object_name like '%Access methods%'

If there are tables present that are primarily read only, a fillfactor setting of 100 should be used to reduce the number of produced index pages.

If, however, there are tables present with high rates of INSERT, UPDATE and DELETE activity, lower fillfactor settings of 50-60% should be used. This will need to be coupled with periodic index rebuilds that will re-establish the fillfactor setting to keep DML running smooth through the indexes.

A mixed environment can work well with fillfactor settings in the neighborhood of 75%. One last piece of advice: for small indexes that have few pages, time should not be wasted worrying about fillfactor as it will not be capable of impacting the database’s performance for the worse.
homebrew01
homebrew01
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11930 Visits: 9222
ps (7/29/2009)
charipg (7/29/2009)
what is fillfactor and what are the advantages and disadvantages?


Refer this BOL link to know about fill factor.
http://msdn.microsoft.com/en-us/library/ms177459.aspx

A fill factor of 30 means 30% of space within the data page will be kept vacant, so that when you insert more rows later, they would be accomodated within this free space. This means sql server will not ask for new pages to accomodate your data every time you insert and hence pages will be contiguous, leading to better performance.

I think you meant: A fill factor of 70 means 30% of space within the data page will be kept vacant



ps.
ps.
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6963 Visits: 3668
homebrew01 (7/29/2009)

I think you meant: A fill factor of 70 means 30% of space within the data page will be kept vacant


Thanks for the correction :-)



Pradeep Singh
charipg
charipg
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2184 Visits: 1193
thanks to all........
israelgress
israelgress
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 18
Thanks for the information.
It was useful for me, I was looking for an answer so clear as yours.
Greetings from Mexico. :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search