I'm using the MSDN example for System.Data.SqlClient.SqlConnection. When running the executable on machine where MSSQL instance is located, everything is good and the conn.Open() line only takes milliseconds.
string connString = "server=WIN-8MHRO21AMIN\\MYINSTANCE;Initial Catalog=mydb;User ID=sa;Password=xxxxxx;Connection Timeout=30";
string query = "SELECT 2+2;";
using (SqlConnection conn = new SqlConnection(connString))
{
var dtStart = DateTime.Now;
conn.Open();
Console.WriteLine($"Connected: {DateTime.Now - dtStart}");
using (var cmd = new SqlCommand(query, conn))
{
SqlDataReader Reader = cmd.ExecuteReader();
}
}
But when running from a remote machine, the connection always takes at least 2 seconds, plus some change. Here I run the executable 5 times. First one is slow because of cold start, consequent runs are quite stable.
Connected: 00:00:05.6271518
Connected: 00:00:02.0243444
Connected: 00:00:02.0248543
Connected: 00:00:02.0238635
Connected: 00:00:02.0248544
When looking at the exchange in Wireshark it doesn't look like it's the server's fault. The client sends some DNS requests, the server responds immediately, then the client does nothing for exactly one second, then sends an UDP request, the server responds again, the client waits for one more second, and finally starts TCP conversation with actual data payload. So what is the wait for?
After removing Connection Timeout parameter from connection string, it's even worse. The same can be achieved by setting it to 16 or less (?!).
Connected: 00:00:03.1286925
Connected: 00:00:03.1286923
Connected: 00:00:03.1276323
Connected: 00:00:03.1296922
Connected: 00:00:03.1296835
The conn.Open() now consistently takes at least 3.1 seconds. At 2-second mark there is some TCP conversation going on, but the client stops it prematurely, waits for 1.1sec and starts all over. How is this caused by Connection Timeout is beyond my understanding.
Is there any way I can keep the conn.Open() down to 100ms range on every application run?
The client machine is Win10. I tried compiling the project with .NET framework 4, 4.5, 4.7.2 with the same results. .NET Framework 3 and .NET Core 3.1 don't exhibit the Connection Timeout magic but still take 2 seconds in every case.
The server machine is Win7, SQL Express 2014 SP2, running inside VMware Workstation. Firewall disabled.
UPDATE: Yep this is a DNS issue, connecting by IP address is instant.