Briefly describe the internal mechanism of SQL Server connection pooling

When the application is running, there will be a connection pool management control running in the application process, unified management of all the connections established by the application and SQL Server,

and maintaining these connections is always active. When a user issues a connection open instruction, the connection pool will find an idle state in the connection pool it maintains

Connection to your own managed connection pool, for this user to use. When the user has finished using the connection Close command, the connection pool will put the connection back to its own

Management of the connection pool, let him back in the idle state, rather than really log out from the SQL. That way, if the next time a user needs the same connection, you can reuse the connection,

Without having to do the physical connection anymore. That is, the connection pool is placed on the client side and is the client mechanism

Connection Pool internal:

The inside of the connection pool is actually a thread pool, which is managed by the management control of the connection pool (plainly: Client database driver)

The client application makes a SQL Server connection by loading the SQL Server's data-driven control. There are currently 3 main client database drivers:

1, MDAC (Microsoft Data Access Components) when the SQL2000 began to have

2, SQL Server Native Client SQL2005 began to introduce

3. The Microsoft JDBC Provider usage mechanism differs from MDAC and SQL Server Native client

ADO and ado.net are using SQL Server Native Client

Some people will ask whether this thread pool is a thread pool provided by Windows because Windows already has a pool of threads.

I don't think it's the Windows thread pool, because you can specify the number of connections in the connection string if you specify the number of connections

is 32767 (that is, the maximum number of connections to SQL Server), the application can still run, and the Windows thread pool

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

And not 32767 so much, the exact number I also forgot.

So the thread pool should be implemented by the control itself: the control creates a thread pool and then creates the number of threads specified in the connection string and then puts it into the thread pool

A thread pool implemented by the same client database driver can only be used by programs that connect to SQL Server using the client database driver

For example: The JDBC thread pool can only be used for programs that are also Java-SQL Server Native client thread pool can only be given to ADO and Ado.net

Program using!!!

Multiple applications use the same thread pool, how are these applications differentiated and isolated?

The answer is through the name of the application

For example, there are two connections: their properties (program names) are different, so the connection pool creates two physical connections instead of reusing the same connection

If the maximum number of connections specified in the connection string for two applications is 40000, then the management control creates 80,000 connections, and then

When an application initiates a connection, the connection pool distinguishes which 40,000 connections the application uses based on the application name

Do not understand the children's shoes can look at the picture below, the figure explained will be more clear