0

I am setting up a new OLTP database and expect to write to the database about 70% of the time and read from it around 30% of the time.

Given this criteria, I'm wondering what would be good practice in setting the default fill factor for this database as well as other best practices I may want to look into applying given this criteria.

Any input would be greatly appreciated.

Thanks

0

The answer is, unfortunately, it depends. Your database might be 70/30 write/read, but every table in the database won't be 70/30. Some tables, such as reference tables and lookups, may only be written to once during initial deployment then very rarely after that. Those tables should have a high fill factor.

You transactional tables that have the 70/30 write/read split may be candidates for lower fill factors, but again, it depends on the index. For example, filtered indexes that may actually only have a 50/50 read/write ratio may be okay with a higher fill factor, but your clustered indexes may want a lower fill factor to prevent excessive page splits.

You need to analyse your tables and indexes individually and make the call based on the expected read/write split for that specific index rather than trying to set it for your database as a whole.

Another consideration is your number of transactions per minute/hour/day. If your OLTP system has to handle millions of transactions a day, then fill factor is an important design consideration, if your system doesn't need to scale beyond a few thousand transactions per day, then fill factor is less important due to the low volumes of data ingress.

If you're architecting this database from scratch, then take the time to correctly identify the required fill factor per index, and ensure any new indexes\tables have this analysis performed as well, rather than creating them with default fill factors.

EDIT: As per Erik Darling's comment, another thing to be aware of is that your fill factor only applies when you create, rebuild or reorganise an index. There is a great article on Brent Ozar's site about fill factor which should help.

  • 2
    You may want to remind OP that fill factor is only enforced when an index is rebuilt or reorganized, and not when data is modified via inserts, updates, or deletes. – Erik Darling Feb 1 at 3:00
  • Let me simply, let's assume this database has only 1 table and this 1 table will be written to about 30% of the time and read from around 70% of the time. What would I look to set the fill factor for the indexes on this table? Also, what other considerations or settings should I consider changing or setting up differently at the server level for this type of workload vs lets say a server that I know may be read only? Thanks again for everyone's input. – equipe9 Feb 1 at 16:42
  • It still depends, for example, your clustered index should be a unique, incrementing, narrow key, so it is safe to set that fill factor quite high as it will only ever add records at the end of the index. Free space throughout that index is a waste. You may have other non-clustered indexes that will have records added throughout the index leaf level pages, so these may benefit from a lower fill factor. Also, your questions RE: server settings for that workload type is too broad. Provide specific indexes and server configs you need help with for this scenario and you'll get better help. – HandyD Feb 3 at 21:25
0

I am setting up a new OLTP database and expect to write to the database about 70% of the time and read from it around 30% of the time.

Given this criteria...

This is NOT a criteria for choosing FF.

You said nothing about data types of key fields, and this is important.

For example, if all your PK are guidsgenerated with newid(), your fragmentation will always be high because of random inserts, on contrary, if your PKs are always sequential because they are made on identity, or date always increased, inserts will cause no page splits at all.

Is your data often updated? What are data types of updated fiels? If updated fields are of fixed length your updates will not cause page splits, and if they are of variable length the updates will.

setting the default fill factor for this database

There is no default fillfactor for database. There is a default FF for server, but it affects all the databases on your server, even master. It's not a googd idea to change it at the server level.

So in any case you should set the FF for every index manually, and it can be different for every table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.