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 . 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 .
- Disable Named Pipes at the SQL Server (requires a restart of SQL Server
- 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.
“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.”