Internal Mechanism of sqlserver connection pool

Internal Mechanism of sqlserver connection pool

Preface:

When the applicationProgramWhen running, there will be a management control of the connection pool running in the application process, unified management of all the connections established between the application and sqlserver,

And maintain these connections to remain active. When a user sends a connection open command, the connection pool will find a idle connection pool in the Self-maintained connection pool.

To the user. After the user completes the use, the connection close command is issued, and the connection pool will put the connection back to itself.

In the management connection pool, make him Idle again, instead of logging out from SQL. In this way, if a user needs the same connection next time, the connection can be reused,

Instead of physical connections. That is to say, the connection pool is placed on the client, and the client mechanism is used.

 

Inside the connection pool:

The connection pool is actually a thread pool, which is managed by the management controls of the Connection Pool (to put it bluntly: the client database driver)

The client application connects to sqlserver by loading the data driver control of sqlserver. Currently, there are three main types of client database drivers:

1. MDAC (Microsoft Data Access Component) started at SQL2000
 
2. introduced by sqlserver native client sql2005
 
3. The usage mechanism of Microsoft JDBC provider is different from that of MDAC and sqlserver native client.

ADO and ADO. net use sqlserver native Client

 

Some may ask if this thread pool is provided by Windows because windows already has a thread pool.

I think it is not a Windows Thread Pool, because the number of connections can be specified in the connection string. If the number of connections is specified

Is 32767 (the maximum number of sqlserver connections), the application can still run, Windows Thread Pool

There are not as many as 32767, and I forgot the specific quantity.

Therefore, the thread pool should be implemented by the control itself: the control creates a thread pool, then creates the specified number of threads in the connection string, and then puts them into the thread pool.

 

The thread pool implemented by the same client database driver can only be used by the same program that uses the client database driver to connect to sqlserver.

For example, the JDBC thread pool can only be used by Java programs, and the SQL Server native client thread pool can only be used by ADO and ADO. net

Program use !!!

 

How are these applications differentiated and isolated when multiple applications use the same thread pool?

The answer is through the Application name

For example, if two connections have different properties (Program names), the connection pool will create two physical connections instead of reusing the same connection.

If the maximum number of connections specified in the connection strings of the two applications is 40000, the management control creates 80000 connections and

When an application initiates a connection, the connection pool uses the application name to identify which 40000 connections the application uses.

If you do not understand the shoes, you can see them clearly.

 

Since the connection pool is enabled by an application, how can we clean up the connection pool?

Method: restart the application (force clear the connection pool)

 

The last question is:

Both applications specify the maximum number of connections in the connection pool as 40000. If so, sqlserver should

80000 active connections are maintained and applications are connected. Therefore, sqlserver should report an error.

The maximum number of sqlserver connections is 32767.

However, the program cannot capture the error information, so will sqlserver open 80000 connections at a time and

What about the maintenance of these connections?

The answer is in my other article.ArticleIn:

Very old topic sqlserver connection pool

Http://www.cnblogs.com/lyhabc/articles/2797351.html

 

In fact, the ODBC connection method also uses the connection pool. You can see the following options in the control panel-> database (ODBC)