Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Choosing a FillFactor Expand / Collapse
Author
Message
Posted Thursday, August 13, 2009 9:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:53 PM
Points: 33,063, Visits: 15,179
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
Post #770643
Posted Friday, August 14, 2009 2:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:50 AM
Points: 2,854, Visits: 3,174
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 2014, 2012, 2008 R2, 2008 and 2005. 29 May 2014: now over 29,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #770757
Posted Friday, August 14, 2009 5:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:11 AM
Points: 171, 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.
Post #770804
Posted Friday, August 14, 2009 6:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 2,656, Visits: 19,187
Nice picture choice, Steve

---------------------------------------------------------
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."
Post #770853
Posted Friday, August 14, 2009 7:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:27 PM
Points: 1,386, Visits: 824
there's only one real choice for Phil Factor...
Post #770858
Posted Friday, August 14, 2009 7:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 2,656, Visits: 19,187
I actually thought it was a Guest Editorial until I got to the bottom and saw Steve's name on it.

---------------------------------------------------------
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."
Post #770864
Posted Friday, August 14, 2009 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:14 PM
Points: 148, Visits: 655
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? ). 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.



Post #770886
Posted Friday, August 14, 2009 8:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 19, 2014 8:57 AM
Points: 1,897, Visits: 655
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.....
Post #770995
Posted Friday, August 14, 2009 9:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
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.
Post #771051
Posted Friday, August 14, 2009 9:47 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:42 PM
Points: 711, Visits: 677
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
Post #771067
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse