How long does it take to create a new database connection to SQL
Can anyone provide me with a ballpark timing (in milliseconds) for how 开发者_StackOverflow社区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.
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
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
精彩评论