Choosing a FillFactor

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Choosing a FillFactor

18 posts, Page 1 of 2

1 2 »»

Choosing a FillFactor

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Thursday, August 13, 2009 9:18 PM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

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

EdVassie

Posted Friday, August 14, 2009 2:56 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:57 AM
Points: 2,619, Visits: 2,749

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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,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

Ben Moorhouse

Posted Friday, August 14, 2009 5:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 09, 2012 2:36 AM
Points: 171, Visits: 442

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

jcrawf02

Posted Friday, August 14, 2009 6:59 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:36 PM
Points: 2,549, Visits: 18,880

Nice picture choice, Steve BigGrin

---------------------------------------------------------
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

Andy Lennon

Posted Friday, August 14, 2009 7:06 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:59 AM
Points: 1,386, Visits: 823

there's only one real choice for Phil Factor... BigGrin

Post #770858

jcrawf02

Posted Friday, August 14, 2009 7:11 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:36 PM
Points: 2,549, Visits: 18,880

I actually thought it was a Guest Editorial until I got to the bottom and saw Steve's name on it. Hehe

Post #770864

Larry Aue

Posted Friday, August 14, 2009 7:30 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 142, Visits: 523

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? Tongue

). 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

nelsonj-902869

Posted Friday, August 14, 2009 8:40 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, March 29, 2013 7:52 AM
Points: 1,483, Visits: 619

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

Post #770995

Noel McKinney

Posted Friday, August 14, 2009 9:31 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, April 05, 2012 2:35 PM
Points: 2,007, Visits: 767

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

Dan Guzman - Not the MVP

Posted Friday, August 14, 2009 9:47 AM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, April 26, 2013 11:57 AM
Points: 708, Visits: 660

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 »

18 posts, Page 1 of 2

1 2 »»

Permissions