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

Question

Can someone provide me with the step time (in milliseconds) how long it will take to establish a new DB connection with SQL with C #. That is, what is the overhead when the connection pool needs to create a new connection.

Answer 1

It depends:

• time for resolving the DNS name to IP
• time to open a TCP socket or Net channel (on top of other TCP sockets): 3 IP packets usually
• time for hshake SSL / TLS if encryption is required: ~ 5 rounds plus time for loading the master key exchange if the SSL / TLS key information is not reused (i.e. one access to the RSA secret key, which is very expensive)
• time to authenticate SQL password for SQL auth (2 rounds, I reckon)
• time for NTLM / Kerberos authentication for integrated auth (1 roundrip for negotiating SPNEGO, 5-6 conversations in topics if there is no Curb ticket, 1 round text if there is a ticket, 4-5 if NTLM is selected)
• login authorization time (search metadata, permission ratings against the entry token)
• possible start time of any input triggers
• time to start a connection (1 roundtrip with initialization of a set of SET sessions)

A few more esoteric times:

• time for opening auto-closing databases, if indicated in the request (may include recovery, usually not)
• time to attach the database if AtachDBFile is used and db is not connected yet.
• time to start the user instance for SQL 2005 RANU. This is approximately 40-60 seconds.

You can usually make 10-15 new connections per second. If a problem occurs (for example, a DNS lookup problem issued by IPsec, SSL problems, Kerberos problems), it can easily go to 10-15 seconds for each connection.

In contrast to the existing joint connection, sp_resetconnection must be performed (this is a one-time movement through the existing channel), and even this can be avoided if necessary.

Answer 2

You can always write code that opens a connection to your server and its time.

Sort of:

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); 

This will give an average time to open and close 100 connections. Notice I did not run the code above

EDIT: Disable pooling for comment by Richard Salay. Otherwise, the results will be distorted.

Answer 3

It depends on which database you are connecting to, and whether it is local or network, and 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 local area network, it can still be pretty fast. Here is a simple example of connecting to a server on a different subnet (I think one hop):

  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(); } 

Below are the print dates. The very first one has the cost of loading assemblies and various DLLs. The following are just a measurement of the initialization of new connections:

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