The maximum number of connections for SQL Server has expired but has not yet obtained a connection from the pool

Many friends who do architecture design, program development, operations, technical management may have such confusion more or less:

1. How many connections does SQL Server support for concurrency?
2. Can SQL Server meet existing applications?
3. How many connections are supported by the existing technology architecture?
4. How many concurrent connections can theoretically be supported in the case of hardware performance and network performance?
5. What is the current concurrency of a database in a production environment?
6. How do I monitor the number of concurrent existing databases?
7. What is the amount of concurrency in a production environment that is theoretically the maximum amount of concurrent reimbursement?
   

To do this, I specifically wrote the program to do the next test, using the loop to open the connection and keep the connection open not closed, the test code is as follows:

usingSystem;
usingSystem.Collections.Generic;

namespaceSqlservermaxconnectiontest
{
classProgram
{
StaticvoidMain (string[] args)
{
intMaxCount =40000;
ListNewList for(inti =0; i < MaxCount; i++)
{
Console.WriteLine (string. Format ("successfully created connection object {0}", i));
vardb =NewHotspotentities ();
Db. Connection.Open ();
Collection. ADD (DB);
}
}
}
}

I was surprised by the results of the first round of tests.

When you create a 101 connection, you cannot create a new connection. That is, if the connection string does not do any processing, our program can only establish 101 connections with SQL Server. The test results are as follows:

Second round test, enable connection pooling.

Add code to the connection string:

Pooling=true; Max Pool size=40000; Min Pool size=0;

Several times in a row the memory was exhausted, but the results were very gratifying.

The highest record is 29299, which is obviously not the highest record, just because my notebook memory is limited, so it is difficult to break through.

Microsoft's definition of the maximum number of connections

Microsoft defines the maximum number of connections on MSDN as: "The default is 0, which means no limit, but the maximum number of connections is 32767". As shown in the following:

Interested friends can write their own code to test, share a bit.

How to monitor the number of SQL Server connections

sp_who ' LoginName '
LoginName is of course the user name of the login SQL, the general program will use a username to log in to the SQL so that the user name can be seen after the login of the connection.
If you do not write loginname, then all connections are returned.

Since the number of connections is predictable and measurable, then it is measurable, so we can evaluate or test the program and distribute it according to the actual situation.

With these, I believe that the above confusion should be able to untie it.

Transferred from: http://www.cnblogs.com/wlb/archive/2012/04/08/2437617.html

Source: http://wlb.cnblogs.com/

The maximum number of connections for SQL Server has expired but has not yet obtained a connection from the pool