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.