I work for a company that provides web based data collection. Currently we have a SQL Server 2012 server as the publisher for 2 subscribers (planning on expanding up to 5+ subscribers), both SQL 2012 Express on machines running Server 2008 R2. The subscriber and publisher servers also have a .NET application that access and writes to the published tables. The filters on our publication are set up to prevent a subscriber from writing to records that exist on different subscriber, but the publisher application can write to all records. When we first went live with this setup (1-2 months ago) there were no issues and everything was working as expected; however, recently (within the week) we have been receiving application errors indicating SQL query timeout when our application executes update statements to specific tables that are replicated. This error is occurring on both the publisher box and subscriber box, and is specific to 1 or 2 tables that are filtered.
We have looked at the fragmentation levels of the publisher/subscription tables as well as the merge tables, and currently there is nothing greater than 30% fragmentation. Note that we have put in place Reorg for Merge_Contents and Merge_genhistory scheduled job that runs every 30 minutes. Looking at the query execution plan also provides no insight into possible causes of the errors. RAM usage cap has also been checked and currently we have it set to a 25 gig max out of about 34 gigs available on the publisher and a similar ratio set up on the subscriber boxes, so we currently do not think that is the cause.
I have been researching this issue for the last few days and have not been able to find anything that has fixed the issue. Are there any probable causes we should look at or is there a good way to determine the cause of the timeout?
Updated (2013/08/07): Yesterday evening we saw the timeout issue again and decided to run exec sp_updatestats (as per discussion comments) hot in production. As soon as it finished the error stopped. Since it seemed to work, we added the task to our hourly reorgs. All seemed well for several hours; however, we got a call about production issues early this morning. The same SQL Timeout issues were happening an hour after running that morning's updatestats. During this same time the SQL process was hitting ram cap, other than that there were no apparent issues. No server connections issues in or out of our server (not sure about client side connections to our application), no deadlocks going on, and no long running process eating up CPU. One interesting thing we did see was that the hourly job seemed to be stuck and was not finishing in the expected time frame. We have contacted Microsoft for support, but still do not seem to have a resolution for this issue.
Update (2013/08/14): We have been seeing the issue consistently over the course of the last week. Currently we have indication that it is caused by merge replication syncs that get "stuck." When we see the issue occurring we can generally find the longest running subscriber sync operation that has still not completed and end the process through the task manager. Upon ending the subscription process, everything begins working as expected for around 2-4 hours when the issue occurs again.
Currently our assumption is that the replication (currently using replmerg.exe) is causing table level locks. If the replication process for some reason begins taking an extended amount of time or gets "stuck" on that table, all updates fail because of that lock. Upon ending the replication that lock gets released and everything begins to work as expected.
The behavior we are seeing is nearly identical to the following: http://us.generation-nt.com/answer/sql-2005-merge-replication-locking-help-97622442.html
Our latest attempted fix was setting the Max_Continuous_Merge option on the publication to 2 in order to limit the number of merges in case a deadlock was occuring between two merge agents; however, that has not fixed the issue.