we have three MSSQL 2005 servers (database is set to compatibility level 80 for legacy reasons) which we have just set up Transactional Replication w/ Updateable Subscriptions on. Two which are located in the US, and 1 located in Asia. Two of the 3 servers are busy servers and 1 is not. The one which is not is the Distributor and Publisher.
What we are seeing is that the table which is part of the Replication finds itself being locked by the sp_MSupd_dbo stored procedure. We notice this because other very simple select statements (just a lookup of the PK) are blocked during the execution time. This stored procedure is doing a simple UPDATE statement. This table has approx. 120,000 records in it. When running the sp_MSupd_dbo stored procedure manually, it seems to run very fast without issue. Additionally, the select statements which seem to get blocked are also very quick. We are seeing some locks as much as 20-30 seconds long.
Also, the majority of the traffic are these quick select statements which do a lookup straight from the primary key.
Has anyone else seen anything like this before?
Best Answer
That procedure is what replication uses to update the table (you probably figured this out already). I'd take a look at the execution plan and the waits for the procedure when it is running to see what the waits are showing.
I haven't specifically seen that before, but I can imagine it when the procedure is being run from a very remote distributor with a crappy network link in between.