November 18, 2011 at 5:57 am
Dev (11/18/2011)
the read to write ratio is normally biased towards reads.
Actually "It depends"
Also, some tables are likely to have high volumes of inserts/updates which could be possible candidates for a lower fill factor if they don't have an incremental value.
i.e. OLTP Transaction tables
Other tables might be relatively static and a low fill factor won't help with performance in any significant way.
i.e. OLTP Master / Type Tables. Usually very small 5 - 5000 rows.
Ideally it should be looked at on an index by index basis.
Ideally Yes. Practically NO.
The main point I was making is that just because you have an OLTP system doesn't mean you should set your fill factor to 85% etc. Also, even in OLTP systems you will generally find that the read to write ratio is still biased towards reads. This is a generalization and systems can vary enormously. Even if your systems have tables with high volumes of inserts/updates it doesn't necessarily mean they are going to benefit from a lower fill factor other than 100. If the index is based on an ever increasing value, a lower fill factor probably won't help.
I agree with the point that tables with relatively static data tend to be quite small. These should ideally have a fill factor of 100 but it's not likely to make any practical difference to performance either way if the tables are small. Some tables with relatively static data might actually be quite large though. e.g. Geographical lookup data.
If people know more about how the fill factor affects performance - where it helps and where it hurts, they are then free to make more informed choices. A one size fits all approach isn't always the best option (Although it might work well enough in some cases).
DBA Dash - Free, open source monitoring for SQL Server
November 18, 2011 at 6:40 am
Thanks to everyone for the help.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply