SQL Server Connection Pool internal mechanism

Preface Introduction:

When the application runs, a management control of the connection pool runs in the application's process, unifying all the connections established by the application and SQL Server.

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

Connection back to the user's own managed connection pool. When the user is finished, issue the connection Close command, and the connection pool will put the connection back to its own

Managed connection pool, let him back in the idle state, instead of actually logging out of SQL. So if the next time a user needs the same connection, you can reuse this connection,

Without having to do the physical connection again. That is, the connection pool is placed on the client, which is the client mechanism

Inside the connection pool:

The inside of the connection pool is actually a thread pool, which is managed by the management controls of the connection pool (that is, the client database driver)

The client application makes SQL Server connections by loading data-driven controls for SQL Server. There are currently 3 main client database drivers:

1. MDAC (Microsoft Data Access component) SQL2000 when it started.

2, SQL Server Native Client SQL2005 began to introduce

3. Microsoft JDBC Provider uses a different mechanism than MDAC and SQL Server Native client

ADO is using SQL Server Native Client

Some people ask that this thread pool is not a thread pool provided by Windows, because Windows would have a pooling of threads.

I think it is not the thread pool of windows, 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

And not 32767 so much, the exact number I have forgotten.

So the thread pool should be implemented by the control itself: the control creates a thread pool, then creates the number of threads specified in the connection string and puts it 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 SQL Server

For example: The JDBC thread pool can only be used for programs that are also Java-only, and the thread pool for SQL Server Native client can only give ADO

Program Use!!!

How do these applications differentiate and isolate when multiple applications use the same thread pool?

The answer is through the application's name

For example, there are two connections: their properties (program names) are different, then 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 according to the application name

Do not understand the children's shoes can be seen, the explanation will be more clear

Now that the connection pool is open for applications, how do you clean up the connection pool?

Method: Restart the application (Force clean Connection pool)

Finally, there is a question:

Two applications specify a connection pool with a maximum number of connections of 40000, and if so, SQL Server should pre-

80,000 active connections are maintained and the application is connected so that SQL Server should have an error

The maximum number of connections for SQL Server is 32767

But the program can not catch the error message, then SQL Server will not open 80,000 connections at a time and

Do you maintain these connections?

The answer is in another article of mine:

Very old topic SQL Server Connection pool

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

In fact, the ODBC connection mode also uses the connection pool, you can see in the Control Panel-"Database (ODBC), the following options