Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Choosing a FillFactor


Choosing a FillFactor

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36371 Visits: 18759
Comments posted to this topic are about the item Choosing a FillFactor

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
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3156 Visits: 3821
One problem with any fill factor value is that it is linear and disruptive activity is often skewed.

This means you are inserting an I-O overhead into relatively stable portions of the key range in order to reduce the disruptive impact of insert activity in another portion of the key range.

One innovation I saw in a third-party tool for another RDBMS was the ability to have a higher level of free space where the index was most disrupted. Most RDBMS (SQL Server included) really do not care if free space is distributed in a linear fashion or concentrated in one place, they will use whatever is in the free space map.

This particular routine had parameters to give average free space in the index, and highest allowable concentration of free space within a single MB of data. When an index is rebuilt, the routine reading the old index pages would also review the level of fragmentation, and it would insert additional free space where the fragmentation was worst.

The end result was stable portions of the key range had very little free space, and therefore very efficient IO. Portions with a history of disruption would get more free space, allowing a greater number of inserts before fragmentation chains going outside the current extent would build up.

Non-linear free space would not suit all indexes, but for those that have a predictable pattern of skewed inserts it is a technique that can give useful extra performance.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
Ben Moorhouse
Ben Moorhouse
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 444
oooh! that actually sounds really good!
We tend to just add data to the end of tables, so full pages works well for us.
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 19324
Nice picture choice, Steve :-D

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Andy Lennon
Andy Lennon
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1392 Visits: 826
there's only one real choice for Phil Factor...:-D
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 19324
I actually thought it was a Guest Editorial until I got to the bottom and saw Steve's name on it. Hehe

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Larry Aue
Larry Aue
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 668
I'm one of the crazy people who will use a GUID for a PK, because I don't care that much about random inserts. I would never use a fill factor less than 50 (wait! does that mean I couldn't use Phil Factor? :-P). I view fill factor as the mechanism that get you by until the next reindex, as it reduces fragmentation by giving you space to enter additional data into the index without page splitting.

If you need to reindex a table every night, you're doing it too often. Typically, I shoot for reindexing once a week. Most of the systems I've inherited were indexing once a day, some during peek overnight processing hours.



nelsonj-902869
nelsonj-902869
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2731 Visits: 747
A fair question and I have not really thought about fillfactors much. Reason? Because in past history we purchased individual servers speced out to meet the needs of the department that server was going to serve. Our DBAs would gather data on record volumes, data entry volumes, current history requirements, etc and then build or order a server that was speced out to meet 4 or 5 years of data growth for that department. Easy...we then used standard fillfactors (5 to 10%) and we're done.

Now however, I can see things changing. We are moving to a VM environment and I submit that the fillfactor question now becomes really important. Our VM environment, as designed, uses shared storage on the SAN and all vm servers share the SAN. Most of our servers were merely "ported" over to VM, and I'd be willing to bet that none of our DBAs reviewed fillfactors once moved into VM and using the SAN storage for the database, logs and everything else. I don't think I ever used 50% as a fillfactor, but just imagine if only half of our 65 vm servers used that for a fillfactor......Sounds like I need to go back to work.....w00t
Noel McKinney
Noel McKinney
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2017 Visits: 796
Fill factor of 100 with PAD_INDEX = ON for reference (lookup) tables. While the fill factor of 100 is the same as 0 I like that this syntax is more explicit so it sticks out that this is a table with data that isn't going to be changed (if ever). Otherwise I specify a fill factor of 50 or less depending on the table's anticipated activity level.
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Right there with Babe
Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)

Group: General Forum Members
Points: 724 Visits: 736
For me, I didn't know what fillfactor was until I read this, and the other links in the article. I'm thinking my dbs are at 0
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