|
|
0
0
0
It is a difficult question, and it is very tough to answer without analyzing the workload of the system.
· If your data system is static and just read only, a default Fill Factor value of 100 or 0 would be ideal. Why is it so? It is because of no insert, update or delete, having all the pages filled up to make it a right way.
· If the data system has a big amount of OLTP transactions, do have a lowering Fill Factor between 70 and 90 which provides a better outcome.
· It may give an opinion to anyone that I’m not giving any clear-cut answer, I regret for not giving a straight forward answer, but ethically, I won’t able to give an answer to the question, what would be the righteous Fill Factor. In point of fact, the answer is fully relied on a given data system. Complete analyzing the given data system would the ultimate factor to judge the suitable Fill Factor that fits a Data system.
Fill Factor substance in two granular levels
1. Server level
2. Table/index level.
Let us see both the levels in sequence below.
Prior I begin the project, I will account and log the counter
“SQLServer:Access Methods:Page Splits/Sec.” The next step, I would lower the Fill Factor values to 95, 90, 85 and 80 and will monitor the counter for some days. This process has to rebuild all the indexes which are not a recommended standard. When I find the desirable (smallest) value for the counter, I determine that particular value as the optimal value for my Fill Factor. I would say, do make a note this as it is not the best practice but something everyone should know.
Up to now, all the conversations center point was on the server level, and people assume that change in one server level setting will fix all the issues. But it is not. There are much more things to be dealt at the object level.
Here, we are targeting an optimal Fill Factor which lines up us to a low number of page splits and preserves the space usage too. The method said earlier works good at the server level when mixed with the perception of the index-level Fill Factor.
Now, let me roughly categorize the user tables into the following:
· Static tables – There are consistently some tables which are always static and won’t get updated quite often, e.g., system tables.
· Tables updated not so much often – There are a lot few tables known as summary tables which are not updated many times. However, at systematic interim, these tables have new entries in them.
· Regular updated Tables – The majority are the usual tables in OLTP system. These tables have updates in high volume frequently.
· Clustered index on the identity column Tables – This case, insertion of all new rows take place at the end of the table.With the above-said categories in mind, I recommend analyzing the whole set of database tables. After categorization of the objects, I recommend to setting the Fill Factor.
Since these tables do not change and never receive updates, I would recommend setting the Fill Factor at 100 which would be the best option as it preserves the space. There is no room for fragmentation exist in static tables.
These tables are to a definite extent have similar attributes of static tables. As these tables do not receive updates often, they do not end up with performance issues. To make room for the updates, I would recommend setting the Fill Factor to 95. In general, when indexes rebuilt at regular intervals, the suggestion would be Fill Factor of 95.
When setting Fill Factor at table level, I would suggest to start from 90 and observe the table for some time. If you notice the recurring issue again with page split, it gives room for fragmentation, so I recommend setting the fill factor to 70 with an interval of 5 at a time. Fill factor has to keep the balance between reads/writes.
These are usual tables exist in an OLTP system. Clustered index is on identity columns for many tables. In this type, all the new data is definitely inserted at the end of the table and there is no new row will be inserted in the mid of the table. This case, the value of Fill Factor does not have any impact. So it is advisable to have the Fill Factor set to 100.
As a studying factor, the DBA and Developers in industry, have quite often come across a plain misconception/confusion in setting Fill Factor. It is a well-known point that “page split” occur only when the page is full and, new rows are inserted into the page. The DBAs gets confused in split ratio many times. Most are under the assumption that split happens because of the Fill Factor. The truth is that split is always 50-50. Fill Factor is only applicable during new index creation or re-build.
It gives an interesting study about rebuilding indexes. Rebuilding index will re-arrange every page and data [tidy up the space] based on the Fill Factor. Every page will have precisely the same amount of the data as that defined in the Fill Factor.
The question frequently asked would be how often a DBA should rebuild indexes. It is a difficult question to answer without examining the workload. The best practice is to rebuild “fragmented” big indexes at least once a week based on a widely used algorithm.
Of the given two methods, a DBA should have understood a lot about Fill Factor, and now I hope you have the clarity that there is no right answer for it. When I look at the set value in configuration, my opinion is right away below.
When the Fill Factor is 100, I would say, reduce the value of it for the tables which are frequently updated. If Fill Factor is lesser than 100, I would say, it needs setting to 100 for identity columns and master tables.