Initial connection to SQL Server Connection Is Slo

问题:

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

回答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.

回答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.

回答3:

I tried specifying the connection string with integrated security = false (meaning the user id and password are in the connection string) and encrypt = false (just be be 100% sure SSL encryption is not being used). These specifications did not seem to help and I could not get a connection explicitly using the TCP/IP network library (NetworkLibrary = "dbmssocn"). This could have to do with the server firewall and the port not being open. I switched back to named pipes and put the named pipe network library specification in the connection string this time (NetworkLibrary = "dbnmpntw"). After this change, the connection was made instantaneously.

回答4:

Yeah when you're using integrated security, Active Directory can be the one to blame, also the overall network since it all depends on it. Another thing I could think of is the edition of SQL Server you're using.

Also, when SQL Server is not being used by long periods of time, it behaves similar to IIS, putting the worker process to sleep, so when you contact the server again, depending on the machine (we can see that these have desktop machines configurations), it will take some time while the worker process gets back to life and is ready to work.

回答5:

You did check the obvious I take it? That UDP port 1434 is open on the firewall and the browser service is running .... would take about 40 sec to authenticate otherwise.

回答6:

Establishing an integrated security connection to SQL Server using the IP address (instead of a host name), will prevent Kerberos authentication being used. In this scenario check the connection between the SQL Server and the domain controller.

If you connect using the host name (not the IP Address) Kerbos is in play, in this case you need to check the client machine's connection to the domain controller.

回答7:

there are other methods of creating connections to SQL databases. Try finding a tutorial which uses the

sqlconnection myCon = new sqlconnection(details);

myCon.Open()

instead of creating an object to instance the connection.

回答8:

I don't have a concrete answer but have you tried running SQL Profiler to see what is going on from SQL's point of view?

Also have you tried connecting to SQL using the same credentials as your connection?

On the other hand it may all be much lower level but I always do the easy to check stuff first.

Good Luck.

回答9:

It sounds like either name resolution is taking a while or authentication is taking a while. After the initial resolution or authentication's occured, then details of endpoints are cached by the server so it doesn't need to perform the lookups again until caches expire.

As an experiment, try ping'ing the server from the client box -- if this takes a long time to resolve the hostname, then you've found the culprit: hostname lookups (DNS or NBNS). Another alternative is to use the host IP address rather than name. So if you've got a SQL Server named instance of bob on server sql2005-01, and this server has an IP of 192.168.200.12, then try connecting to 192.168.200.12\bob instead of sql2005-01\bob.

Authentication's more awkward to resolve, but you might be able to test it with runas on the SQL server box (e.g., runas /user:domain\user cmd to see if you can open up a command prompt as the user you're attempting to authenticate as.

回答10:

It could easily be a problem with either the connection or the authentication, it is normal that the first connection takes longer as ADO.NET has connection pool to avoid that long connection time.

There are many factors that can influence the speed: - TCP/IP configuration - Routers on the server-side - etc.

回答11:

We had the same problem, and it turned out that our remotely-hosted Active Directory server was to blame.

We created a site-local Active Directory server to replicate the remotely hosted AD master and then all of our slow SQL Server integrated security authentication performance issues went away.

I hope that helps.

回答12:

Still if you are facing the issue, please see blow resolution:

Root Cause

The issue which we were seeing on Win7 VDIs could be due to the Network hardware device connected with the machine. If TCP/IP scaling is not supported by the network device then the performance will be slow.

Solution

Disable auto tuning level of the TCP. Please follow below steps: 1) Open command Prompt with admin right (Run as Admin) 2) Type “netsh interface tcp set global autotuninglevel=disabled” 3) After running above command restart the machine.

For other information on this command, visit link “http://support.microsoft.com/kb/935400”

回答13:

I had the same problem. After vigorous research on google and stackoverflow, i changed the client computer's hosts file (In windows,located at C:\Windows\System32\drivers\etc).I entered my host's ip address and server name in this file and Viola!. Things became superfast! Like everybody in stackoverflow said, it was the computer looking up the servername's address in the DNS service and getting a timeout. The steps i did follows.Give it a try if nothing else works.

HOW TO ADD AN ENTRY IN HOSTS FILE

---

1.Open the command prompt and ping your server,where the remote database is located. To do this enter the following command:

ping servername

Here my remote computer name was Juno. So i should ping like this.

ping Juno

This command will ping my server and returns ip address like this.

Pinging Juno [192.168.0.3] with 32 bytes of data:

As you can see the ip address of the server is between the brackets. Copy the ip address.

2.Now Open the Hosts file with elevated Notepad(Run as administrator).

At the end of the hosts file,there will be some lines like these:

    #localhost name resolution is handled within DNS itself.
    #   127.0.0.1       localhost
    #   ::1             localhost

At the very bottom (here,after the localhost),type # and enter the ip address of the server we just obtained preceeded by server name. So, the hosts file should look like this.

# localhost name resolution is handled within DNS itself.
#   127.0.0.1       localhost
#   ::1             localhost
#   169.254.63.1    Juno

3. Now save the hosts file and restart the Client PC. (For me,it worked instantly without restart.)

Annd there you go!

For more info about editing your hosts file,click here

回答14:

In my case the answer was:

• Try everything with no result.
• Remote Desktop to the (non-prod) SQL Server to double check settings.
• Close down Visual Studio you left running after doing a quick SSIS job.
• Go somewhere quiet and kick yourself.