September 20, 2011 at 11:04 am
What would be the best fill factor for the following scenario?
I have a table with 35 columns. Out of which one column is identity column, This is also the primary key column with clustered index on it. We have 22 other columns with non-clustered indexes on them. Remaining columns do not have any indexes.
Note: On the table:
1) Inserts: About 40K per day (Twice a day at 9AM and 9PM)
2) Updates: About 100 updates per day
3) Deletes: May be
4) Variable length columns: We have lots of varchar fields in the table
My research shoed 100% fill factor is best if u have an identity column with Clustered index. But my teams concern is what about the Non-Clustered indexes on the table? Don't we need to consider them before setting up the fill factor?
Thanks,
Kumar
September 20, 2011 at 11:18 am
onlygoodones (9/20/2011)
My research shoed 100% fill factor is best if u have an identity column with Clustered index.
No usually. If the fill factor is 100% (and the rows do actually fill the page) then any update that increases the size of the row will cause a page split
But my teams concern is what about the Non-Clustered indexes on the table? Don't we need to consider them before setting up the fill factor?
Fill factor should be set per-index, not globally.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2011 at 11:36 am
Thanks Gail Shaw for the quick reply.
I have read the article below and concluded 100% fill factor on Tables with Clustered Index on Identity Column: So, I thought of setting up 100% FF on all indexes. Please let me know if that is not the ideal way.
pinal says:
Tables with Clustered Index on Identity Column – Set Fill Factor at 100.
This is very often seen in an OLTP system. Many tables have the identity column as a clustered index. In this case, all the new data is always inserted at the end of table and a new row is never inserted in the middle of the table. In this situation, the value of Fill Factor does not play any significant role and it is advisable to have the Fill Factor set to 100
Thanks,
Ramesh
September 20, 2011 at 11:39 am
I hate to say it, but Pinal is wrong. If you set 100% fill factor on a clustered index and the rows really do fill the page then any update that increases the row size will split the page.
100% is great for insert-only tables with an identity column, not necessarily for anything else. You can, but with updates you may end up splitting the page.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2011 at 11:46 am
GilaMonster (9/20/2011)
I hate to say it, but Pinal is wrong. If you set 100% fill factor on a clustered index and the rows really do fill the page then any update that increases the row size will split the page.100% is great for insert-only tables with an identity column, not necessarily for anything else. You can, but with updates you may end up splitting the page.
+1.
I had to use a jackhammer approach here to solve this page split issue (18 000 indexes here on 1 ERP).
I checked the avg page space used for all tables and it was around 93% with 100% FF
So I went ahead and reduced the FF to 85% all around (knowing it would cost only 1 GB per year in growth + 800 MB now). This was more than acceptable for us.
I then saw my daily index maint. job go from 30 minutes and 8 GB tlogs to 2-5 minutes and 500 mb.
I kept monitoring and reduced the FF even further on tables that were constantly getting reindexed.
September 20, 2011 at 11:49 am
Yep, based on your scenario (updates & variable length columns) I'd leave some room in an attempt to minimizing page splits as Gail pointed out. The only time I'd use 100% fill factor is for static tables and things like audit tables where there are only ever inserts.
_____________________________________________________________________
- Nate
September 20, 2011 at 11:54 am
RP_DBA (9/20/2011)
Yep, based on your scenario (updates & variable length columns) I'd leave some room in an attempt to minimizing page splits as Gail pointed out. The only time I'd use 100% fill factor is for static tables and things like audit tables where there are only ever inserts.
Same here, I have a small-ish price history table here. My last command is alter index all rebuild...
Not the smartest way to do this, but since this is read-only except for the reload it saves a lot of useless page reads.
September 20, 2011 at 12:03 pm
Thanks Gail, SScoach and Nate. I will choose 95 as my initial FF.
If I see any page splits down the road, I would go down to 90 and so on.
Thank you all
September 20, 2011 at 2:19 pm
onlygoodones (9/20/2011)
Thanks Gail, SScoach and Nate. I will choose 95 as my initial FF.If I see any page splits down the road, I would go down to 90 and so on.
Thank you all
How did you choose 95%?
What's the Current avg % used???
September 20, 2011 at 2:26 pm
onlygoodones (9/20/2011)
Thanks Gail, SScoach and Nate. I will choose 95 as my initial FF.If I see any page splits down the road, I would go down to 90 and so on.
Thank you all
If you find the index is becoming fragmented often and you need to frequently defragment/reindex it, it is a good indication you need to lower the fill factor.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply