One of the cool things about Merge Replication is that it has the capability of revealing very interesting information regarding applications and their associated statements ran against published articles. We can learn quite a bit about an application just by the sheer number of data changes that are replicated as a result of the statements it executes. This can reveal not only which tables an application changes the most, but may even be useful in identifying flaws in application statements when the amount of data changes being replicated is more than anticipated.
Merge Replication tracks data changes through triggers and system tables in the publication and subscription databases. The replication system tables contain metadata that indicates which data changes should be propagated. One of Merge Replication system tables, MSmerge_genhistory, contains one row for each generation (collection of changes) that is delivered to a Publisher or Subscriber. MSmerge_genhistory contains a column changecount, which can be used to determine which table articles contain the most data changes. So to get an idea for which articles contain the most data changes within the retention period, we can do something like this:
USE MyPublicationDB GO SELECT name AS [Object Name], SUM(changecount) as [Change Count] FROM dbo.MSmerge_genhistory WITH (NOLOCK) INNER JOIN dbo.sysmergearticles WITH (NOLOCK) ON art_nick = nickname WHERE genstatus = 1 OR genstatus = 2 GROUP BY name ORDER BY [Change Count] DESC
In this scenario, the articles in my test Merge topology that have been changed since my retention period are Customer, SalesOrderDetail, Address, Product, and SalesOrderHeader, which have had 847, 542, 450, 295, and 32 data changes respectively. If you find yourself wondering which articles in your Merge topology are exhibiting the most data changes, the changecount column in MSmerge_genhistory is what you're looking for.