SQL installation work flawless on hostname, but very poorly on IP connection.

Question

I've recently upgraded SQL 2012 installation from SP1 to SP4 and ran into very specific problem.

All services works perfectly good when they're connected using hostname of SQL server. Its not named instalation so hostname of server is SQL installation name aswell. For few years it works both on hostname and IP address.

After recently SP4 upgrade connecting via IP is terrible bad. It works but it takes like 30 sec even to connect via SMSS. Same connection but with hostname usage is flawless and works just in a blink.

Tried to restart both SQL services and machine.

Thanks for Your help and time. Best regards.

Answer 1

Perhaps the TCP connection is failing and falling back to named pipes, which takes time.

Run this query to identify named-named pipes connections:

SELECT session_id, client_net_address, net_transport  
FROM sys.dm_exec_connections;