I had an issue with replication that I posted in the replication forum, but I don't think it is specific to replication as much as SQL2012 and indexing issues. Here is the other post, not sure if anyone in this forum has experienced this before?
Basically, the merge process that enumerated changes in batches (sp_MSenumchanges_belongtopartition) executed a query to join the rowguid of the changes to the PK/rowguid in my user table. This should have been a super fast query to seek the clustered index of my user table, but instead looking up a 100 row batch was taking around 10 minutes (sometimes significantly longer) and pinning the CPU. If I ran the same query manually, outside of sp_MSenumchanges_belongtopartition, it would return in a matter of milliseconds.
Index usage stats looked like sp_MSenumchanges_belongtopartition was executing a scan on the table, instead of doing a clustered index seek like it would if I executed the query manually. I have no idea why the SP would be executed such a bad query plan, and not sure how to confirm that was the issue or how to more-permanently resolve it?
For now, I created a second nonclustered index on the PK, effectively duplicating the clustered index. Now replication is fine and sp_MSenumchanges_belongtopartition executes in <1s. When I look at the query plan, it shows sp_MSenumchanges_belongtopartition using my new index to locate the PK of the row. Then it joins that result to a clustered index seek to pull the whole row of data. So it's not as efficient as just a clustered index seek, but is working.
Any ideas? Index corruption a possibility? What should I look at next?