August 13, 2009 at 9:18 pm
Comments posted to this topic are about the item Choosing a FillFactor
August 14, 2009 at 2:56 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
August 14, 2009 at 5:02 am
oooh! that actually sounds really good!
We tend to just add data to the end of tables, so full pages works well for us.
August 14, 2009 at 6:59 am
Nice picture choice, Steve 😀
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
August 14, 2009 at 7:06 am
there's only one real choice for Phil Factor...:-D
August 14, 2009 at 7:11 am
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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
August 14, 2009 at 7:30 am
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.
August 14, 2009 at 8:40 am
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:
August 14, 2009 at 9:31 am
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.
August 14, 2009 at 9:47 am
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
August 14, 2009 at 11:02 am
I assign it based on fragmentation speed. For static lookup tables, I build them at 100 and then put them in read-only files. Everything else, I have a script that logs fragmentation, tracks average daily fragmentation, and decides on a defragmentation strategy based on that. It's very heuristic.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2009 at 11:59 am
Dan Guzman (8/14/2009)
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
That is funny and I remember back in the early days of the MCDBA certifications there was one user with the user name IAMGod and it looks like the person is saying I am God but IAMGod actually means Index allocation Mapping God that is the person is an expert of IAM pages. I learned a lot about Indexes from Paul Randal's Microsoft blog and Ken England's book, stuff not covered in the BOL and other books at the time.
http://www.amazon.com/Microsoft-Server-Performance-Optimization-Handbook/dp/1555582419
🙂
Kind regards,
Gift Peddie
August 14, 2009 at 3:18 pm
We just leave the default settings alone for most parts. Including fillfactor. We don't have people dedicated to SQL (they have to cover most of Microsofts product suite like exchange, sharepoint, crm, windows, and other products like San, vmware, etc.) and the chance of selecting the right fillfactor is far less than choosing the wrong one.
August 14, 2009 at 7:43 pm
These days I work with a data warehouse solution, based on partitioned tables. We set it at 100%, reindex the fresh partition at the end of each ETL run, and forget about it.
August 17, 2009 at 6:45 am
Anyone implementing any large growth databases needs to look into fragmentation and page splitting. It is not an issue for sequential data however it is definitely something that can have a major impact on performance if not known. Most of our items use 90%. This buys the customer about 3-6 months between rebuilding their indexes. This also allows the files to grow in larger chunks thus avoiding disk level fragmentation as well.
If you have never looked into your data pages and fill factors I think it would be wise. Even if you are not experiencing issues. Problems are far easier to fix with baselines.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply