How long does it take to create a new database connection to SQL

Question

Can anyone provide me with a ballpark timing (in milliseconds) for how long it takes to establish a new DB connection to SQL from C#. I.e. what is the overhead when a connection pool must create a new connection.

Answer 1

It depends:

• time to resolve the DNS name to IP
• time to open the TCP socket or the Net pipe (on top of other TCP soket): 3 IP packets usually
• time to hanshake the SSL/TLS if encryption is required: ~5 roundtrips plus time to bootstrap the master key exchange if the SSL/TLS key info is not reused (ie. one RSA private key access, which is very expensive)
• time to authenticate SQL password for SQL auth (2 roundtrips I believe)
• time to authenticate NTLM/Kerberos for integrated auth (1 roundrip to negotiate SPNEGO, 5-6 roundtrips if Kerb ticket is missing, 1 roundtip if the ticket is present, 4-5 roundtrip if NTLM is chosen)
• time to authorize the login (lookup metdata, evaluate permissions against login token)
• possible time to run any login triggers
• time to initiate the connection (1 roundtrip with the inital SET session stuff batch)

Some more esoteric times:

• time to open auto-close databases if specified in request (may include a recovery, usualy doesn't)
• time to attach database if AtachDBFile is used and db is not already attached
• time to start a 'user' instance for SQL 2005 RANU. That is about 40-60 seconds.

Usually you can do some 10-15 new connections per second. If there's an issue (eg. DNS lookup problem, IPsec issued, SSL problems, Kerberos issues) it can easily go up into 10-15 seconds per conection.

By contrast an existing pooled connection only has to execute sp_resetconnection (that is one roundtrip on an existing channel), and even that can be avoided if necessary.

Answer 2

It depends on what database you are connecting to and whether it is local or across a network and the network speed if so. If everything is local, then maybe 1 or 2 milliseconds (again it depends on the DBMS). If, more realistically, it is over a LAN, it can still be pretty fast. Here is a simple example connecting to a server on a different subnet (one hop I think):

        for ( int i = 0; i < 5; i++ )
           {
           Stopwatch timeit = new Stopwatch();
           timeit.Start();
           AdsConnection conn = new AdsConnection( @"Data Source = \\10.24.36.47:6262\testsys\;" );
           conn.Open();
           timeit.Stop();
           Console.WriteLine( "Milliseconds:  " + timeit.ElapsedMilliseconds.ToString() );
           //conn.Close();
           }

The following are the times it printed. The very first one has the cost of loading assemblies and various DLLs. The subsequent ones are only a measurement of the initialization of the new connections:

Milliseconds:  99
Milliseconds:  5
Milliseconds:  4
Milliseconds:  4
Milliseconds:  4

Answer 3

You could always write up some code that opens a connection to your server and time it.

Something like:

StopWatch timer = new StopWatch();

timer.Start();
for(int i=0;i<100;++i)
{
     using(SqlConnection conn = new SqlConnection("SomeConnectionString;Pooling=False;"))
     {
         test.Open();
     }
}
timer.Stop();
Console.WriteLine(test.Elapsed.Milliseconds/100);

That would get the average time to open and close 100 connections. Note, I did not run the above code

EDIT: Disabled connection pooling per Richard Szalay's comment. Otherwise, the results would be skewed