I have come across this interesting issue in which we have the following scenario:
All servers are running SQL 2008
When merge replication is processing certain published articles, we see that the merge agent is blocked, and is being blocked by another spid executing another task coming from the same merge agent.
A close look through a profiler trace, we see:
Spid 59 is blocking on spid 69.
59 runnable 0x0063 562 SELECT PREEMPTIVE_OS_WAITFORSINGLEOBJEC LPC CLAUDY-MERGETEST-MERGETEST-CLAUDY2
69 suspended 59 7 KEY: 6:72057594057916416 (1f02b882c015) 0x0005 296281 UPDATE LCK_M_X LPC CLAUDY-MERGETEST-MERGETEST-CLAUDY2
Spid 69 –> sp_MSuplineageversion
Spid 59 –> sp_msenumchangesdirect
Points to mention:
a. same merge agent for both spids
b. LPC being used
c. Waittype of “preemptive_os_waitforsingleobject” for the blocking spid
–> please read the CSS Blog for more information in regards to this waittype (http://blogs.msdn.com/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx)
d. 0x0063 (for spid 59) indicates AsyncNetworkIO
After looking further in depth of this issue, the workaround to use if you are running into this issue is:
Create an alias (the alias name has to be the same name as the server as merge collects information in regards to the servers involved from system tables)
SQL Server Configuration ManagerSQL Native ClientAliasesCreate a new alias with the SQL Server Name
Doing so will Force Merge agent to use TCP/IP (this can be seen in sysprocesses --> net_library column)
Once this is set up, run merge and verify in the sysprocesses that the spids are now using TCPIP.
** More info on this topic when available **
Cluadia, I'm running into this issue with a publication (1 publisher, around 20 subscribers) every few minutes. Can you explain a little bit more detailed what you mean with "create an alias"? I would like to try this because merge replication occurs to be very slow. Thank you!
Thank you for your post.
It helped me for a similar issue (2 spid locked, one of them by ASYNC_NETWORK_IO).
The workaround you suggested worked fine.