SQL Server Performance Forum – Threads Archive
Column filters in merge replication
I recently discovered through SQL Profiler that updated data is still replicated to Subscribers for those columns that are filtered within a published article. However, since the schema of the replicated article at the Subscriber does not contain the filtered column, the data is not actually applied at the Subscriber. This seems to contradict SQL Server Books Online’s statement regarding Column Filters, which says, "Column filters can reduce the time it takes to propagate data updates to Subscribers…..". I realize that reading the statement carefully leads one to the assumption that Microsoft meant to place focus on the word "can"; however, I was under the impression that data on column filters was ignored by the merge replication process. I’ve checked my publication’s settings, but cannot find anywhere that prevents the transmission of data on a filtered column. We are using SQL Server 2000 with SP 3 installed. I have tested this issue by both adding a new column through the replication user interface (leaving the newly added column unchecked as a part of the publication), and I have also setup a completely separate database and new publication, filtering a specific (non-essential) column from the onset. In both instances, when I perform an update to the filtered column at the Publisher, SQL Profiler shows the individual updates as they are attempting to be applied at the Subscriber during synchronization. At the end of the synchronization, the Subscriber’s job shows the corresponding number of merge updates; however, the data, naturally, is not in the corresponding table, as would be expected. While I can understand this occuring because updates are essentially deletes followed by inserts, it does not seem to work that way for merge replication. From what I can gather, updates are applied at Subscribers on a column level basis. Yes, it is still fundamentally a delete followed by an update; however, the update only makes changes to the columns that were actually updated. It’s not as though merge replication sends the entire row to support the update process, as the overhead for such an implementation would be horrendous. Has anyone else seen this happen? Also, does anyone know of a way to prevent filtered column data from transmitting during merge replication? In my opinion, filtered columns, in any type of replication, should reduce the overhead involved during synchronization, along with reducing data storage at the Subscribers. Thoughts or suggestions most appreciated.Dynamic filters and partitions are a powerful feature of SQL Server 2000 replication. However, even with indexes on the filtered columns, SQL Server must still read each row in the dynamic partition and compare it to the filtered value. Using static filters and partitions reduces the processing time required to complete the merge process. Dynamic and join filters extend the capabilities of merge replication. Are you getting any performance overhead currently between Publisher and Subscriber, if so then try to remove the filtering and re-sync the replication between’em. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>