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.
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.
Menu
499
