SQL Server Connection Pool Basics

It is usually time-consuming to connect to the database, because this process is composed of several time-consuming steps.

1. Establish a physical connection, either socket or named pipe.

2. A handshake with the database server is required.

3. The connection string must be parsed.

4. The connection must be authenticated by the server (authenticated ).

5. Some permission verification is required.

6. Wait.

In practical applications, most applications only need to configure one or more database connections. this means that many identical connections are repeatedly enabled and closed during application execution. to minimize the loss of enabling database connections, ADO. NETConnection poolingOptimization Technology.

Connection Pooling reduces the number of new connections opened. pooler has the ownership of the physical connection. configure each database connection so that several active connections are continuously active. Pooler manages database connections in this way. every time a user calls the Open Method for a connection, Pooler searches for an available active connection in the connection pool. if such a connection is available, it returns the connection to the caller instead of opening a new connection. when the application calls the Close method for the connection, pooler will return the connection to pooler and put it in the pool of available connections in the active state, rather than closing it. once the connection returns to the pool, it is ready for the next Open call to be returned to the caller.

Only when the connection is in the same configuration can they be put in the same pool. ADO. NET will maintain multiple pools at the same time, each pool corresponds to a configuration. connection is classified into different pools by connection string. Another criterion is Windows identity when ingetrated security is used. connection is also classified into the pool based on whether it is included in transaction.

Connection Management by pool can significantly enhance the performance and scalability of your applications. by default, the connection pool is composed of ADO.. NET. unless you explicitly turn it off, pooler will optimize the connection operation as long as your application starts and closes the connection. you can also use modifiers for database connection strings to control pooler behavior.

Pool creation and allocation

==============

When a connection is enabled for the first time, a connection pool is created based on the exact matching algorithm of the connection string. each connection pool is associated with a unique connection string. when a new connection is established, if the connection string does not match the existing pool connection string, a new pool will be created. connection is divided into processes, application domain, and connection string to enter different pools. in addition, if integrated security is enabled, it must be assigned according to windows identity.

Add connection for pool

================

When a pool is created, many connection objects are created and added to the pool. In this case, the minimum pool size condition is met. the connection will be added as needed until the value reaches the maximum pool size (the default value is 100 ).

After the connection is closed or disposed, it will be put back into the pool.

When a SqlConnection object is requested, if there is a available connection in the pool, this connection will be returned. in order to meet the "available" condition, this connection must be currently unavailable, with a matched transacton context or not associated with any transaction context, there is a valid link to the server.

 

Connection pooler will return a connection released by someone else to a new request. if the maximum pool value is reached and no connection is available, the request enters the queue (the default value of timeout after entering the queue is 15 seconds ). pooler then tries to recycle any connection. if the pooler cannot meet the request in the queue before timeout, an exception is thrown.

The exception should be like this:

Exception type: System. InvalidOperationException
Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
InnerException: <none>
StackTrace (generated ):
Sp ip Function
0636F4B8 653cf1_system_data_ni! System. Data. ProviderBase. DbConnectionFactory. GetConnection (System. Data. Common. DbConnection) + 0x133f46
0636F4C4 652D69BA System_Data_ni! System. Data. ProviderBase. DbConnectionClosed. OpenConnection (System. Data. Common. DbConnection, System. Data. ProviderBase. DbConnectionFactory) + 0x6a
0636F4F8 652F5440 System_Data_ni! System. Data. SqlClient. SqlConnection. Open () + 0x70

 

Remove connection

==============

Connection pooler will have a connection that has been idle for too long.

In addition, if the connection is not connected to the target server, the connection will be marked as invalid, and the Invalid connection will only be stored in closed, or the reclaimed is removed from the pool.

 

Note:

As an application, SQL server does not and shouldn't actively detect client connections and determine the connection status. the underlying cross-Process communication (IPCs), such as named pipes, IPX/SPX, or TCP/IP sockets, manages client connections.

Cross-process communication generally has its own mechanism to manage client connections. when the client connection does not respond for a period of time, the Windows Server of SQL either sends a "keep alive" signal to maintain this status, the connection will be cleared after waiting for a certain period of time. however, the "keep-alive" package is not sent by client applications by default. the application needs to enable this feature on its connection.

In some cases (such as client general protection fault), the client will also check the keep-alive detection package of the corresponding server, even if the client application has crashed. in this case, the server may always maintain this link as long as the client machine is not shut down.

If a Windows Server does not close the dead connection for some reason, SQL Server assumes that the connection is still active, so it will not be cleared.

 

Supplement:

SQL connection is a very cheap resource, and there are 2000 or 3000 SQL Server instances.

A connection is not associated with an SQL thread.

I mentioned the SQL thread. To give you an intuitive feeling, you can run the following statement under explain. It returns the SQL worker thread and their status.

select * from sys.dm_os_workers

These worker threads are managed by the thread pool. In other words, the worker threads can be used only when there is a job, and the cpu is put off when there is no job.

 

You can use the following statement to view all connections

select * from sys.dm_exec_sessions where session_id > 50

 

Use the following statement to view the running sessions of all non-SQL Systems

select db_name(database_id), * from sys.dm_exec_requests where session_id > 50 

 

Use the following statement to determine the idle connection:

select * from sys.dm_exec_sessions where status = 'sleeping' and session_id > 50

 

Use the following statement to check which processes on which hosts are connected to which databases and the same connection.

select db_name(dbid),hostname,hostprocess,COUNT(*) as countsfrom sysprocesses where spid > 50group by hostname, dbid, hostprocessorder by counts desc

After obtaining the process number, you can continue to capture dump or find the R & D personnel to troubleshoot possible SQL connection leaks in the process.

 

If there are many connections, but there are many idle connections, the SQL status is normal.

The large number of connections is not a problem.

 

References

==============================

SQL Server Connection Pooling (ADO. NET)

Http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

How to troubleshoot orphaned connections in SQL Server

Http://support.microsoft.com/kb/137983