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
Best Answer
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.