Unexplained Latency in the Distribution Agent using Named Pipe

Just wanted to give a heads up about a SQL Replication Distribution Agent Latency problem we observed today.  The Distribution Agent to 1 subscriber was only delivering about 500 cmds/sec while other subscribers were getting 4000 cmds/sec.

We noticed the Distribution Agent was running under a different PacketSize, made that change, no noticeable improvement.

While checking the PacketSize settings, the customer noticed the Distribution Agents were using different Net_Library settings.  The fast Subscriber was running TCP/IP, while the slower was using NamedPipe.  Using the SQL Server Configuration Manager we determined the SQL Server Distributor was enabled to listen on both Named Pipes and TCP/IP.

--Look for Named pipe connections
SELECT * from sys.dm_exec_connections c
join sys.dm_exec_sessions s
on c.session_id = s.session_id
where net_transport = 'Named pipe'

--Look at connections for a given Publication (example: AW_Tran_Orders)
SELECT * from sys.dm_exec_connections c
join sys.dm_exec_sessions s
on c.session_id = s.session_id
where program_name like '%AW_Tran_Orders%'

To understand why the Distribution Agent switched between TCP/IP and Named Pipe, you can look in the Distribution Database MSrepl_errors table for any indication of network problem.

SELECT DISTINCT SUBSTRING(error_text, 1, 1000) AS Error FROM distribution.dbo.MSrepl_errors WITH (NOLOCK) WHERE Error_text LIKE '%named pipe%' OR Error_text LIKE '%tcp%'  


Named Pipes Provider: Could not open a connection to SQL Server [64]. 
Named Pipes Provider: The specified network name is no longer available.
Named Pipes Provider: There is a time and/or date difference between the client and server.

Named Pipes Provider: Timeout error [258].

Solutions:

1.  Disable Named Pipes at the SQL Server (requires a restart of SQL Server
2.  Use SQL Server Configuration Manager and create a Client Alias to the Distribution SQL Server and set protocol to TCP/IP.

Lesson of the day: If you see unexplained latency in your Distribution Agent, not blocking on the subscriber for example, look at the net_transport in dm_exec_connections and see if you’re using “Named Pipe”.  If you are, create a Client Alias for TCP/IP and restart the Distribution Agent.

One of our customer’s replied with a great link which further explains differences between Named Pipe v. TCP/IP traffic.

http://msdn.microsoft.com/en-us/library/ms187892.aspx

“For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it starts to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.”