Initial connection to SQL Server Connection Is Slow. Why?

Question

I have run into a situation with a C# application installed at two sites where the initial connection to SQL Server is extremely slow. I wrote a test application to verify where the slowdown occurs and it is on the first SQLConnection.Open statement. It was taking about 41 seconds to establish a connection to the server via named pipes. We thought it might be a DNS issue but its just as slow using a TCP/IP connection. After the initial connection is made the connection is pooled and the application responds normally. Both the workstation and the server are decent machines running Windows 7 Pro, Core 2 Duo 3.16 Ghz with 4 gig of Ram. I did find the following article on a microsoft forum:

http://social.msdn.microsoft.com/Forums/en/windowscompatibility/thread/f295994c-5812-4e46-8ac9-f05471d4dd54

Turning off the LLMNR protocol did cut the initial connection time in about half to 21 seconds. However, this is still a long time to get an initial connection to SQL Server. The only things slightly different from our norm is that DNS in this case is done thru a router and not an actual server. This has occurred at only two places so far, others run with no issues. Any help would be appreciated.

Thank You, Dennis

Answer 1

In front of the server on the connection string, add np:

This becomes Server=np:server\instance and forces the Named Pipes instead of the default of TCP.

I could of probably changed the priority to use Named Pipes before TCP... but I did not want to mess with that on the server.

Answer 2

I saw similar problem but not sure it's the same as yours. In my case, it's not just the C# program being slow to make the SQL connection. It's any tools connecting to SQL server also experiencing the slowness. Also, once the initial connection made to the SQL server, any subsequent connections are fine for a period of time.

The reason was that SQL server was using a number of managed assemblies. It's trying to verify the cerfiticates assigned to the assemblies. It's connecting the crl.microsoft.com. My SQL server didn't have the internet connection. So, it waits for the timeout.

The solution was to make my SQL server to have internet access or disable the CRL checking. You can go to the SQL server machine. Select Tools > Internet Options > Advanced. Check if the publisher's certificate revocation under the secuity node is checked or not. If it's checked, uncheck it.