Slow connections to SQL server from Windows 7 client

Question

Hi all,
 
Whenever a connection to an SQL server is done from a Windows 7 client on the local LAN, and the connection string contains the host name of the server, there is a longer delay compared to a connection that was made using the server's IP.
When using XP on the other hand, there is no difference.

Here is a small VB script I have made that demonstrates the differences

cnStr = "Provider=SQLOLEDB;Data Source=ServerName;Connect Timeout=10;User ID=UserName;Password='Pwd'"
start = timer
For i = 1 To 20
    set cn = CreateObject("ADODB.Connection")
    cn.Open cnStr
    Set cn = Nothing
Next
duration = timer - start
msgbox duration & "sec"

On my network it takes up to 6sec to open 20 connections, when using the server's host name, and less the 0.1sec when using the IP (more than 600 times faster!!!).

Somthing is done differently on Windows 7 when resolving the server IP out of its name, but I couldn't figure out what, and how it can be solved.

Any ideas?

Answer 1

Hi Ad P,

 

Welcome to MSDN Forums!

 

Based on your connection string, the SQL connections used the named pipes protocol to connect the remote server.  I created similar codes to build several SQL connections via host name and IP address in Windows 7, but no difference at my side. 

 

You have mentioned that similar codes create no difference on Windows XP, how much time does this process cost? 

 

Could you please try to use TCP/IP protocol to connect the remote server and tell me the testing result? 

 

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hi Lingzhi Sun,
Thanks for you reply.

My connection string does use TCP/IP protocol, since our SQL server does not allow named pipes connections.

Using the following connection string (which forces a tcp/ip protocol) assures it:
Provider=SQLOLEDB;Data Source=tcp: ServerName;Connect Timeout=10;User ID=UserName;Password='Pwd'

You say you had no difference - maybe you tried to connect from the same computer that the SQL server resides on? If yes, try to run my script on a client computer that is different from the SQL server computer.

Anyway, when trying to work with a named pipes connection (using "Data Source=np:\\ServerName\pipe\sql\query" in the connection string), it was as fast as using a TCP/IP protocol that uses the server's IP. Still, I need to connect using TCP/IP using the server name.

On another XP computer, using my script, all the connection types connected in 0.2 secs.

Hope I was able to help you to reproduce my problem...

Thanks, Ad P.

Answer 3

Hi Ad P,

I am connecting the remote SQL Server.  Here are my testing results (C# application):

 

Provider	Protocol	Platform	Server Name/IP Address	Result
.NET Framework Data Provider for SQL Server	TCP/IP	Windows 7	Server Name	0.3sec  (20 connections)
.NET Framework Data Provider for SQL Server	TCP/IP	Windows XP	Server Name	0.3sec (20 connections)
.NET Framework Data Provider for SQL Server	TCP/IP	Windows 7	IP Address	0.3sec  (20 connections)
.NET Framework Data Provider for SQL Server	TCP/IP	Windows XP	IP Address	0.3sec (20 connections)
Microsoft OLE DB Provider for SQL Server	TCP/IP	Windows 7	Server Name	0.4sec (20 connections)
Microsoft OLE DB Provider for SQL Server	TCP/IP	Windows XP	Server Name	0.4sec (20 connections)
Microsoft OLE DB Provider for SQL Server	TCP/IP	Windows 7	IP Address	0.4sec (20 connections)
Microsoft OLE DB Provider for SQL Server	TCP/IP	Windows XP	IP Address	0.4sec (20 connections)

 

The testing results are not 100% accurate but there is no huge difference when testing on Windows 7 and Windows XP.

 

Could you please tell me the SQL Server version and the data provider you are currently using?  

 

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Hi Lingzhi Sun,

Thanks for your last reply, I must appreciate your efforts on it.

The connection string I use has the information about the provider:
Provider=SQLOLEDB ;Data Source= ServerName;Connect Timeout=10;User ID=UserName;Password='Pwd'

I'm trying to connect both to SQL server 2000 and 2005 versions, with the same results.

I guess there's some differences between my test code and yours. Maybe that's the reason why you did not encountered my problem.
Did you try to run my VB script test code? You can save it to a .vbs file and run it.

I ran an equivalent C# code, but still got the same problem:

public static void TestConnection()
{
    string cnStr = @"Provider=SQLOLEDB;Data Source=ServerName;Connect Timeout=10;User ID=UserName;Password='Pwd'";
    DateTime start = DateTime.Now;
    for (int i = 1; i <= 20; i++)
    {
        System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(cnStr);
        oleDbConnection.Open();
    }
    TimeSpan duration = DateTime.Now - start;
    string message = string.Format("{0} secs", duration);
    System.Windows.Forms.MessageBox.Show(message);
}

Can you try it (or the VB script) and tell me about the results?

Thanks, Ad P.

Answer 5

Hi Ad P,

 

My C# codes are similar with yours.  Here are my codes:

==============================================================
string connStr = @"Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DBName;Connect Timeout=10;User ID=sa;Password=pwd";

     

DateTime dt1 = DateTime.Now;

OleDbConnection[] conns = new OleDbConnection[20];

for (int i = 0; i < 20; i++)

{

    conns[i] = new OleDbConnection(connStr);

    conns[i].Open();

}

DateTime dt2 = DateTime.Now;

MessageBox.Show((dt2 - dt1).Milliseconds.ToString());

 

foreach (var c in conns)

    c.Close();
==============================================================

 

Do you find this problem in other Windows 7 machines in LAN?  

 

Hope you have a nice day!

 

 

Best Regards,
Lingzhi Sun

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 6

Hi Lingzhi Sun,

Thanks for your fast reply.

I tried to run your code, and as expected, got the same results...

We encountered this problem on our customers computers which recently installed new windows 7 computers.
So this problem occurs on my computer, and on our customers computers. I have an x64 version, but I was told that this problem is also on x86 versions.

Do you have any idea what can be the reason? Maybe a network configuration? Or something related to MDAC?
Thanks, Ad P.

Answer 7

Hi Ad P,

 

To narrow the problem, could you please tell me does the problem also exist on Windows Vista or Windows Server 2008?  Besides, could you please also use the .NET Framework Data Provider for SQL Server and SqlConnection via C# codes? 

 

Thank you very much!

 

 

Best Regards,
Lingzhi Sun   

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 8

Hi Lingzhi Sun,

I used the SQL Native Client driver with the same results:
Provider=SQLNCLI ;Data Source= ServerName;Connect Timeout=10;User ID=UserName;Password='Pwd'

Using an SqlConnection ended with the same results:

public static void TestConnection2()
{
    string cnStr = @"Data Source=ServerName;Connect Timeout=10;User ID=UserName;Password='Pwd'";
    DateTime start = DateTime.Now;
    for (int i = 1; i <= 20; i++)
    {
        System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(cnStr);
        sqlConnection.Open();
    }
    TimeSpan duration = DateTime.Now - start;
    string message = string.Format("{0} secs", duration);
    System.Windows.Forms.MessageBox.Show(message);
}

Testing on Vista & Server 2008 will take some time...

Thanks again, Ad P.

Answer 9

Hi Ad P,

 

It could be DNS lookup issue.  To verify it, we can edit the local host file to add a mapping of IP address to the host names to mimic the DNS lookup affect.  

 

By default, the local host file is named hosts which is saved under %SystemRoot%\System32\drivers\etc.  This folder information is hold under registry key \HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\DataBasePath.   For detail, please see http://en.wikipedia.org/wiki/Hosts_file.  

 

In Windows 7, we can run the notepad.exe as administrator and open the local host file to edit it.  Please add one line like the following:

=================================================
***.***.***.***      MyServer
=================================================

 

Then use this connection string to connect the remote SQL Server and tell me the testing results.

"Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=DatabaseName;Connect Timeout=10;User ID=sa;Password=Pwd"

 

 

For similar network traffic tracking, we can also use the Network Monitor help us troubleshoot the problem. 

 

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 10

Hi Lingzhi Sun,

Using your suggestion is very similar to use an IP in the connection string. Both avoiding a DNS lookup, and connect fast to the server.

DNS lookup issue that is done differently in Windows 7 was my assumption from the first place. But when I was previously referred the server with named pipes (using "\\ServerName\pipe\sql\query" at my second message), awkwardly, the problem did not occur, although it seems that a DNS lookup is also needed in this case... That's why I threw away my first assumption...

So, is it a DNS lookup issue after all? What is the difference between these two cases?

Does Network Monitor is a network sniffer?

Thanks, Ad P.

Answer 11

I looked on the network traffic with Network Monitor.

It seems that the delayed caused by the LLMNR protocol which is a new name resolution protocol for both IPv4 & IPv6 that is used on Vista, Server 2008 & Windows 7.
The client computer which uses Windows 7, sends an LLMNR request to the server which is 2003 server.
I guess the server does not familier with this protocol, and thus doesn't respond to it.
The client resend again the request after 0.1s.
After waiting another 0.2s for respond, with no answer, it sends NbtNs (NetBios I guess), and only then get an NbtNs respond from the server with its IP.
In total, 0.3sec where wasted on waiting for a response from the server.

On the next connection attempt, the client sends again LLMNR request as described above, and waste another 0.3s. This time, no NbtNs request was send, since I guess its uses the previously received IP.

So, for every connection we waste 0.3s, multiply it by 20 connections, and voila, we'll get to 6s of delay...

I guess you didn't encountered this problem since your connection was against a server which uses Vista, Server 2008 or Windows 7. Try your tests against XP or Server 2003.

BTW, when using named pipes, the SMB protocol is used, and it already has the information about the IP of \\ServerName (from a previous connections to it, I couldn't find how to flush this information, since "ipconfig /flushdns" didn't help in this case)

So now what? Turning off the LLMNR protocol via the GPEdit.msc will solve this problem, but what are the risks of doing so?

Thanks, Ad P.

Answer 12

Hi Ad P,

 

I am very happy to hear that the cause of the problem has been found and the problem has been resolved by turning off the LLMNR protocol.   However, what makes me confused is I also connected to a remote SQL Server on Windows XP, but I did not encounter the same issue.  

 

Based on your scenario, turning off the LLMNR protocol is fine.  For detail, please see http://technet.microsoft.com/en-us/library/bb878128.aspx.

 

Another suggestion:  you can consider using fully qualified domain name (FQDN) here, which brings us with two benefits: 1) LLMNR won’t be used.  2) DNS client service will cache the response so subsequent connections will happen faster. 

 

 

Hope you have a nice day!

 

 

Best Regards,
Lingzhi Sun

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 13

Lingzhi Sun,

I Turned off the LLMNR protocol via the GPEdit.msc as my solution, but its good to know about the alternatives.

Anyway, thank you very much for your help and short replies.
Ad P.

Answer 14

Hi Ad P,

 

You are welcome!

 

Have a great day!

 

 

Best Regards,
Lingzhi Sun

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 15

 

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”

Answer 16

This "netsh interface tcp set global autotuninglevel=disabled" fixes my performance problems from: using windows 7 x86/x64 + SSMS 2008/2005 connecting to a dell optiplex 980 running server 2003 EE x64 + SQL 2005 EE x64. The conn is slow, the Obj explorer is slow, the DB picking dropdown is slow, sp_helptext or any query execution has delays... the most obvious is the DB dropdown, it feels like it freezes. Also, the RDP into this Server2003 has major mouse lag issues, using KB to navigate is fine, but using mouse always have lags of 5 -10 seconds.

although Disabled worked also, but we didnt have to set to disabled, just restricted is fine, as:

netsh interface tcp set global autotuninglevel=restricted

Thanks!