4

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?

Devart
  • 119,203
  • 23
  • 166
  • 186
Jersey
  • 151
  • 1
  • 7

3 Answers3

1

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

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • Good explanation of Fill Factor and it did get me to think about an upcoming change where we will be doing odd numbers on publisher and even numbers on subscriber. In this event I believe I would leave the fill factor around 90 or 80 percent to start. Thanks! – Jersey May 24 '16 at 18:35
1

FILLFACTOR is mainly used for Indexing.

Since you want to change the Fill Factor to 100.Its mean you need to drop and recreate the Index on the merge tables with Fillfactor 100.

And if in your merge replication, 'Copy Clustered Index' and 'Copy Non Clustered Index' is TRUE For all article properties, then once you recreate Index on the publisher, it will also get replicate on other subscriber.

So, if you have heavy merge tables with Index, I would recommend to implement it during offhours because Index creation will take time to replicate on subscriber.

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
Ragesh
  • 740
  • 3
  • 9
  • Great! That was what I was looking for. I figured that something had to be done, but do not have the hands on for this situation. Thanks! – Jersey May 24 '16 at 18:33
1

You can check the fill factor by this query too. Yes, but as @Ragesh said, whenever we change the fill factor (Replication) will impact the performance.

Fill Factor is directly related to Indexes. Every time we all here the word ‘Index,’ we directly relate it to performance. Index enhances performance ‑ this is true, but there are a several other options along with it.

SELECT *
FROM sys.configurations
WHERE name ='fill factor (%)'

Here is good article and explanation of your query.

http://sqlmag.com/blog/what-fill-factor-index-fill-factor-and-performance-part-1

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ef72506-f1b0-4700-b836-851e9871d0a6/merge-table-indexes-fill-factor?forum=sqlreplication

Ajay2707
  • 5,690
  • 6
  • 40
  • 58