February 8, 2016 at 9:36 am
Hello Guys,
Is it a good practice to change the default fill factor value on Instance level?
Should the Clustered Index-Primary key field (IDENTITY KEY) set to fill factor value of 100?
Any guidance on this is much appreciated.
Many thanks!
February 8, 2016 at 9:48 am
SQL!$@w$0ME (2/8/2016)
hi Guys,Is it a good practice to change the default fill factor value on Instance level?
Should the Primary key field (IDENTITY KEY) set to fill factor value of 100?
Any guidance on this is much appreciated.
Many thanks!
Fillfactor is a CRITICALLY IMPORTANT facet to optimal SQL Server application performance from MANY perspectives!!!!
1) I do not recommend changing the HORRIBLY BAD default fillfactor (0), although I could certainly make a case that 90 or 95 would be WAY better for most systems out there that just leave the default.
2) 100 fillfactor for the ubiquitous clustered PK identity 1,1 (which is stupid to have on many tables, btw, but is done something approaching 100% of all SQL Server tables in existence I bet) is GREAT for some tables - namely the ones that never/very rarely get updated to make rows larger than they were originally (which is another BAD habbit devs do - create a partially "complete" row then update it once or a few times with the remaining data that was initially made NULL or empty string). That gets you perfectly full and non-fragmented tables.
HOWEVER, if you do those types of updates a lot you are screwed with page splits on those updates.
3) If you want optimal performance, optimal database size (which affects backups, checkdb/index/stats mx durations, RAM utilization, IO needs, etc, etc), proper page split ratios, better latch/lock scenarios, etc, you MUST INTELLIGENTLY set the fillfactor on your most important indexes!!! I would say all of them but for many that is too tall a task. You can use Ola.Hallengren.com's index maintenance stuff to create logs you can analyze to determine how fragmented indexes get over the mx interval and then start backing things down to find a balance between too full and not full enough.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 8, 2016 at 9:56 am
I wouldn't change fill factor setting on Instance Level, and its not recommended. It might worsen your performance, so be careful in with this setting.
Now with Fill factor on the indexes.
1) You need to make a note or maintain a log of current indexes, and how frequently they are Fragmented.
2) Change the fill-factor gradually. lowering to 95%, 90%, so on there are instance where the fill factor can be set to 60% as well.
You need to monitor and make this modifications. Let me know if you need more information..
-Pramod
February 9, 2016 at 5:10 am
Instance level doesnt matter.
Each index should be hand crafted, i.e. each index should be <create index blah blah with(fillfactor = X)>
Indexes on identites, which increase monotocically, should be around 100.
Indexes on ever increasing but non-monotonic columns I normally have hovering between 95 and 90.
Indexes on mostly random columns, like userid, I tend to have between 90 and 80, depending on if it is a quickly growing or heavily modified table.
i.e. fillfactor of 100% on a GUID is fine if the table gets inserted into once or twice a day, but will hurt if it gets millions of inserts per day.
This all depends on how often you do index rebuilds, and how often the particular index is included in that rebuilld, and how important it is to not have it be rebuilt daily.
Can you do online rebuilds?
If an index is rebuilt daily due to fragmentation, and you need the index to not rebuild daily, then change that index's fillfactor to 5% less than its existing fillfactor, and iteratively you could get to a good value.
In summary, it all depends, and a blanket instance setting is not the best thing to look at first.
I feel it should be deprecated.
February 9, 2016 at 8:32 am
Absolutely not a setting to be made at the instance level. Routinely check the fragmentation levels, look for any repeat offending indexes that have high fragmentation and then gradually reduce fill factor until you find a sensible level.
'Only he who wanders finds new paths'
February 9, 2016 at 9:05 am
Thanks Kevin!
February 9, 2016 at 9:05 am
Thanks Pramod!
February 9, 2016 at 9:06 am
Thanks
February 12, 2016 at 5:57 am
Fill Factor is an important optimisation option but one that is often not fully understood and could be implemented better within SQL Server.
An important part of DB performance is to minimise the number of pages read from disk and the number of pages that *need* to be in memory. Ideally every byte of every page labuoriously hauled in from disk will be used by the query, but this seldom happens.
When we rebuild an index with Fill Factor 0 or 100 (they both mean the same) we are packing as many rows as possible on to a page. If it is a cluster index then the row information contains the actual DB data, and for a non-cluster index the row contains a pointer to the data. This Fill Factor will give us optimum performance at the time of index rebuild, but we need to consider what happens next.
If the primary key is an Identity value, then new rows will always be added to the last logical page of the table, and performance will be maintained.
If an existing row is updated and its length is increased, then it may not fit on to its original page and a page split occurs. In a page split, half the rows remain on the original page and the rest are moved to a new page. We now have two pages that are just over 50% full, with the result that we need additional I-O and memory to get the data from these pages.
If the primary key is not an Identity, then inserting a new row may in itself trigger a page split. If the primary key is a GUID, then page splits will be very common.
We can minimise the number of page splits by using a Fill Factor of less than 100. If we use a factor of 90 and rebuild the index, then we are intentionally leaving 10% free space on each page. This means that if our data fitted on 100 pages with a fill factor of 100, then it will now need 110 pages with a factor of 90. This obviously impacts I-O and memory and can seem a bad idea. However, it does have a good side to it.
By leaving 10% free space on each page, we may be able to insert and update more than one row without causing a page split. The new row will fit on the existing page (assuming row length is less than 10% of page size) and no page split happens. Better still, we have increased the number of rows that are read when the page is read, so our I-O efficiency has increased. If rows continue to be inserted on to the page then I-O efficiency will increase to a peak, but then page splits will start to occur and eventually the I-O efficiency will drop below what there was immediately after the index rebuild. If the Fill Factor was set to 90 after proper analysis showed this was the best value, then an index rebuild can wait until I-O efficiency nears what would be occur with an 80% or 85% fill factor.
The result of all this is that the default fill factor for the instance is normally best left at 0. Likewise, a fill factor of 0 is best for tables that have a cluster index based in a primary key that uses an Identity value. However, for tables based on other columns, especially tables with a primary key that is a GUID, then a different fill factor can be good.
To analyse the best fill factor for a table using a GUID as the cluster index, you need to consider a few items: 1) Row length and rows per page, 2) Rate of inserts (number per second, minute, hour, day), 3) How frequently will the business let you rebuild the index (a rebuild will severely impact use of the table while it runs) and how long a rebuild will take. If updates are measured in rows per hour and you can rebuild every week, then a fill factor of 0 may still be best. If updates are measured in rows per second, then you need to minimise unwanted I-O, so a Fill Factor lower than 100% should be used. The exact value will depend on row length and how often you can rebuild the index.
Finally, SQL Server could do better in how it handles Fill Factor. Like most RDBMS systems, Fill Factor is applied uniformly across the table. However, I have worked with a different RDBMS and a third-party vendor that had an 'intelligent' rebuild utility. This utility analysed where page splits had occured, and increased the fill factor in key ranges that had page splits, and minimised fill factor in key ranges without page splits. This resulted in maximising the I-O efficiency where data was stable, and minimising page splits where data was more volatile. Sadly SQL Server does not have a index rebuild API that would allow this type of intelligent rebuild, so we are stuck with a fill factor that applies to all pages in the index.
BTW, I have not mentioned Free Pages, so look in BOL if you want to find more about these.
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
February 12, 2016 at 7:41 am
Many thanks Ed. Very well explained!
January 3, 2020 at 11:34 pm
I wouldn't change fill factor setting on Instance Level, and its not recommended. It might worsen your performance, so be careful in with this setting.
Now with Fill factor on the indexes.
1) You need to make a note or maintain a log of current indexes, and how frequently they are Fragmented.
2) Change the fill-factor gradually. lowering to 95%, 90%, so on there are instance where the fill factor can be set to 60% as well.
You need to monitor and make this modifications. Let me know if you need more information..
-Pramod
I know this is an older post but had to point something important out...
You can go down to 1% and still get massive fragmentation if your index is an ever increasing index and it suffers from "ExpAnsive" updates because ALL inserts (except for one very rare case) will automatically try (and succeed) to fill each page to 100% no matter what the Fill Factor is.
Lowering the Fill Factor on an index might be the 2nd worst thing possible depending on the insert/update pattern.
--Jeff Moden
January 3, 2020 at 11:39 pm
p.s. If your indexes are random GUID based, you MUST stop using REORGANIZE on them. REORGANIZE does not work the way most people think it does, especially when it comes to random GUIDs. On such indexes, the only thing that REORGANIZE does is it removes fee space in the critical area between the Fill Factor and 100% full and that, my friends, will cause your indexes to perpetually fragment.
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply