What % Fill Factor is best in this Scenario?

Question

What % Fill Factor is best in this Scenario?

onlygoodones

SSC Eights!

Points: 870
More actions
September 20, 2011 at 11:04 am

#248169

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

onlygoodones SSC Eights! Points: 870 More actions · Answer 2

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.

http://www.sqlmag.com/blog/beginning-sql-server-one-step-at-a-time-blog-43/net-framework/what-is-the-best-value-for-the-fill-factor---index-fill-factor-and-performance--part-2-139448

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

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.

RP_DBA SSCertifiable Points: 5116 More actions · Answer 5

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

@nate_hughes

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

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.

onlygoodones SSC Eights! Points: 870 More actions · Answer 7

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 9

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]