I have identified multiple Identity columns in a database that are set to 80 or 90%. I wish to set them all to 100%.
Does anyone know if changing the fill factor on an identity column using Merge Replication causes any issues?
FillFactor comes into picture only when an index is rebuilt by leaving the Percentage of space free set using FillFactor Setting.
With Merge replication,changes at both the sources are tracked through triggers and they are kept in sync.
When you set fillfactor to 80%,20% of the space can be still used for inserts.If you set at 100% ,you are not leaving any space ,there by you have a chance of page splits.Page splits are very expensive in terms of log growth.so there is a chance your inserts will be slower.
But with identity column,all the values will be increasing,so they will be logically added to the end of page.So setting a value of 0 or 100 should improve performance.But fill factor affects only your leaf level pages and what if you update any of the row which may cause the size to exceed the total length of page..Here is what MSDN says on this case
A nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled. For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index. If existing rows will be updated with data that lengthens the size of the rows, use a fill factor of less than 100. The extra bytes on each page will help to minimize page splits caused by extra length in the rows.
Setting a Good fillFactor value depends on how your database is used..Heavy Inserts(more free should be there and fillfactor value should be less,but selects will be some what costly).Less inserts (leave fill factor at some high value)
simple search yields so many results .but you should test them first and adapt it to your scenario