SQL Server Connection Pooling

In an article that looks at the cache, see the three-word connection pool to charge.

The text is connected, the key word is spoken.

In fact, we've been using SQL Server's connection pool. In the connection string, pooling is enabled for connection pooling, the default value is True, and you can add pooling=true after the link;

The two important parameters related to connection pooling are min pool size and max pool size, which are the minimum number of connections in the pool and the maximum number of connections in the pool, with default values of 0 and 100, respectively.

When we create an instance of a connection and call the open () method, the connection pool Manager finds an available connection in the connection pool, and when the close () method is called, the connection pool manager returns the connection to the connection pool for use by the next call to the open () method.

The Connection Lifetime in the connection string sets the life cycle for the connection in the connection pool. Its default value is 0.

When a connection is returned to the pool , its creation time is compared to the current time, and if the length of time (in seconds) exceeds the value specified by Connection Lifetime, the connection is destroyed.

This is useful in a clustered configuration (used to enforce load balancing between a running server and a server that has just been brought online). A value of 0 (0) causes the connection pool to have the maximum connection timeout.

With the above understanding, we can see that even the simplest connection strings are used in connection pooling.

Feel: "Link" is like a person, attributed to the "Connection pool manager" management, when the "outside" need to link the instance, go back to the management program to find a can be used, if the "link" after the work has come back more than the specified time may be killed ....

ASP. NET Connection Pool

Database linking is a risky, expensive, and limited resource, especially in multi-tiered Web applications. You must properly manage your links, because your approach will greatly affect the overall upgrade of your application.

High-performance applications maintain a minimum of connectivity with data sources in use, and leverage performance-enhancing techniques, such as connection pooling.

connection pooling SQL Server, OLE DB, and. NET Framework Data for ODBC provider implicitly buffering connection . You can control the behavior of the connection pool by specifying different property values in the connection string.
connection Pooling overview
Database link pool make the application can reuse existing links in the pool instead of repeatedly establishing a link to the database. This technology will greatly increase the scalability of the application, because a limited number of database links can provide services to many customers. This technique will also improve performance because for creating new links.
specifically, most ADO data providers use connection pooling to improve the performance of applications built around Microsoft's disconnected. NET architecture. The application first opens a connection (or obtains a connection handle from the connection pool), then runs one or more queries, processes the rowset, and finally releases the connection back to the connection pool. Without a connection pool, these applications will take a lot of extra time to open and close the connection.
The following is an example of the SQL Server. NET Framework Data provider connection pool, which illustrates some of the performance and usage of the connection pool. (There are also ODBC. NET Framework Data Provider connection pools, OLE DB. NET Framework Data Provider Connection pools.)
pool creation and allocation

When the connection is open , a connection pool is created based on an exact matching algorithm that associates the connection pool with the string in the connection. Each connection pool is associated with a different connection string.

When a new connection is opened, a new pool is created if the connection string does not exactly match the existing pool .

In the following example, three new SqlConnection objects are created, but only two connection pools are required to manage those objects. Note that the difference between the first and second connection strings is the value assigned to the Initial Catalog

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
conn.Open();      
// Pool A is created.
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=pubs";
conn.Open();      
// Pool B is created because the connection strings differ.
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
conn.Open();      
// The connection string matches pool A.

Once the connection pool is created, it will not be destroyed until the active process terminates. The maintenance of an inactive or empty pool requires minimal system overhead.
You are using SQL Server. NET Data provider link pool, it must be clear:
links are pooled by the law of exact matching of link strings .

The pooling mechanism is sensitive to whitespace between name-value pairs. For example, the following two link strings will generate a separate pool because the second string contains an extra null character .
SqlConnection conn = new SqlConnection ("Integrated Security=sspi; Database=northwind");
Conn. Open (); Pool A is created
Sqlconmection conn = new SqlConnection ("Integrated Security=sspi; Database=northwind");
Conn. Open (); Pool B is created (extra spaces in string)

Summary: 1: When the link is created, a connection pool is created, and the same link creates a connection pool; another connection pool is created as long as the link is not the same (yellow word)

2: Create connection pool is to consume resources, that is, this link does not use CPU;

3: When this link in use, other requests to wait until this release, to use, to wait in the connection pool ;

4: Connection pool seems to have a property that can limit the maximum number of links
Add a connection

Connection pooling is created for each unique connection string. when a pool is created, multiple connection objects are created and added to the pool to meet the minimum pool size requirements . The connection is added to the pool as needed until the maximum pool size is reached.
When a SqlConnection object is requested, the object is fetched from the pool if there is an available connection . to become an available connection, the connection must not currently be used , has a matching transaction context, is not associated with any transaction context, and has a valid link to the server.
if the maximum pool size has been reached and no available connections exist, the request will be queued . When the connection is released back into the pool, the connection pool manager satisfies these requests by reallocating the connection. when you call Close or Dispose on Connection, the connection is released back into the pool.

Removal of connections
If the connection lifetime has expired, or if the connection pool manager detects that the connection to the server has been lost, the connection pool Manager will remove the connection from the pool. Note that this can only be detected after trying to communicate with the server. If a connection is found to be no longer connected to the server, it is marked as invalid. The connection pool Manager periodically scans the connection pool for objects that have been released to the pool and marked as invalid. Once found, these connections will be permanently removed.
If there is a connection to a server that has disappeared, it is still possible to remove the connection from the pool even if the connection pool manager does not detect the disconnected connection and mark it as invalid. When this happens, an exception is generated. However, in order for the connection to be released back into the pool, it must still be closed.

Select Pool Capacity
The ability to set maximum limits is important for large systems that manage thousands of of users simultaneously making requests. You need to monitor the performance of the link pool and the application to determine the optimal pool capacity of the system. The optimal capacity is also dependent on the hardware running SQL Server.
During development, it may be necessary to reduce the default maximum pool capacity (currently 100) to help find link leaks.
If the minimum pool capacity is set up, then when the pool is initially populated to achieve this value, some performance penalty is incurred, although several customers who initially linked it will benefit from it. Note that the process of creating a new link is serialized, which means that when the pool is initially populated, the server cannot process requests that occur at the same time.

monitoring the pooling of links
To monitor application usage of linked pooling, you can use the profiler tools that are released with SQL Server, or the Performance Monitor that is released with Microsoft Windows 2000.
To monitor link pooling with SQL Server Profiler, proceed as follows:
1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Profiler to Run Profiler.
2. On the File menu, point to New, and then click Trace.
3. Provide the link content and click OK.
4. In the Trace Properties dialog box, click the Events tab.
5. In the list of selected event categories, ensure that the audit login and audit log out events are displayed under security audits.
6. Click Run to start the trace. When the link is established, you will see an audit login event, and an audit log out event when the link is closed.
To monitor link pooling through Performance Monitor, proceed as follows:
1. Click Start, point to Programs, point to Administrative Tools, and then click Performance Run Performance Monitor.
2. Right-click in the background of the chart and clicking Add Counter.
3. In the Performance object drop-down list box, click SQL Server: General statistics.
4. In the list that appears, click the user link.
5. Click Add, and then click Close.
add Pooling=false in config to indicate that the connection pool is not used in the program.
Add max Pool size = "1000" in the connection string to config file to widen the connection pool, while adjusting the program to avoid multiple open connections。

What is a Connection pool? ( a link to a database that has already been created, not closed and placed in the connection pool so that it can be taken directly from the pool the next time you use it, no need to create a new one)
Whenever the program needs to read and write to the database. Connection.Open () uses ConnectionString to connect to the database, the database establishes a connection to the program and remains open, and the program can then use T-SQL statements to query/update the database. When executed to Connection.close (), the database closes the current connection. Well, everything seems to be so methodical.
But if my program needs to open and close the connection in an indefinite time, such as ASP. NET or Web Service, for example, when HTTP request is sent to the server, we need to open connection and then use select* from Table Returns a datatable/dataset to the client/browser and then closes the current connection. That every time open/close Connection so frequent operation for the entire system is undoubtedly a waste.
The ADO team gives a better solution. Save the previous connection, and the next time you need to open the connection, the previous connection will be handed over to the other connection. This is connection Pool.

How does the Connection Pool work?
First of allwhen a program executes Connection.Open (), ADO needs to determine whether this connection supports connection Pool (Pooling default is True), if you specify false, ADO creates a connection to the database (in order to avoid confusion, all connections in the database use the "Connection" description) and then return to the program.
if specified as True,ADO creates a connection Pool based on ConnectString, and then populate the Connection pool with Connection (all connections in. NET programs, all using the "Connection" description). How many connection are populated is determined by the Min Pool Size (default = 0) property.For example, if you specify 5, ADO opens 5 connections to the SQL database at a time, and then 4 Connection, saved in Connection pool, and 1 Connection returned to the program。

when the program executes to Connection.close (). If pooling is true,ado.net, put the current connection into the connection pool and keep the connection to the database。
The connection Lifetime (default 0) attribute is also judged, and 0 represents infinity if the connection exists longer than connection Lifetime, Connection closes the connection to the database at the same time, instead of re-saving it to the connection pool.

(This setting is primarily used in clustered SQL databases for load balancing purposes.) If pooling is specified as false, the connection to the database is broken directly.

And thenthe next time Connection.Open () executes, ADO. NET will determine whether the new ConnectionString is consistent with the connectionstring of the connection previously saved in the connection pool.
（ADO. NET will turn ConnectionString into a binary stream, so to say,The new ConnectionString and the connectionstring of the connection in the connection pool must be exactly the same, even if you add a space, or modify the connection The order of some properties in a string will cause ADO to think of this as a new connection, and a new connection from the newly created one。 So if you use the Userid,password authentication method, modifying the password will also result in a connection, if you are using SQL Integrated authentication, you need to save two connections using the same one).

Then ADO needs to determine if there are any available connection (not occupied by other programs) in the current connection Pool, and if not, ADO needs to determine the max pool of connectionstring settings Size (default is 100), if all connection in the connection pool do not reach Max Pool size,ado.net, the database is connected again, a connection is created, and connection is returned to the program.

If you have reached maxpoolsize,ado.net, you will not create any new connections again, but instead wait for the connection in the connection pool to be released by other programs. This wait time is limited by sqlconnection.connectiontimeout (the default is 15 seconds), which means that if the time exceeds 15 seconds, SqlConnection throws a timeout error (so sometimes if the Sqlconnection.open () method throws a timeout error, one possible reason is not to close the previous connnection in time, while connection The pool number reached maxpoolsize. ）

If a connection is available, the connection removed from the connection Pool is not returned directly to the program, and ADO needs to check the Connectionreset properties of the ConnectionString ( The default is True) if a reset is required for connection. This is because the connection that were previously returned from the program may have been modified, such as using Sqlconnection.changedatabase method to modify the current connection. The returned connection may not have been connected to the initial catalog database specified by the current connection string. So you need to reset the current connection. However, due to all the additional checks, the overhead of the system is increased by the ADO Connection Pool.

View Connection Count 1, get the maximum number of simultaneous user connections allowed by SQL Server

SELECT @ @MAX_CONNECTIONS

2. Get connection information for the currently specified database

1. SELECT * from master.dbo.sysprocesses WHERE dbid in
2. (
3. SELECT dbid from master.dbo.sysdatabases
4. WHERE name=' yourdatabasename '
5. )
6. --Change yourdatabasename as needed
7. SELECT * from master.dbo.sysprocesses WHERE db_name (dbid) = ' YourDatabaseName '

3. Get all connection details for the current SQL Server

SELECT * FROM sysprocesses
The above query results include: System process and user process.
If you just want to check the user process, you need to use the following method

4. Gets the number of connections or attempts to connect since the last time the SQL Server service was started

SELECT @ @CONNECTIONS

5---View all requests for the current database system.

Lists only the fields that I think are more important to help me solve the problem.

 
 
 1     SELECT ds.session_id,
 2        ds.status,
 3        Db_name(dr.database_id) AS database_name,
 4        ds.login_name,
 5        ds.login_time,
 6        ds.host_name,
 7        dc.client_net_address,
 8        dc.client_tcp_port,
 9        ds.program_name,
10        dr.cpu_time,
11        dr.reads,
12        dr.writes,
13        dc.num_reads,
14        dc.num_writes,
15        ds.client_interface_name,
16        ds.last_request_start_time,
17        ds.last_request_end_time,
18        dc.connect_time,
19        dc.net_transport,
20        dc.net_packet_size,
21        dr.start_time,
22        dr.status,
23        dr.command,
24        dr.blocking_session_id,
25        dr.wait_type,
26        dr.wait_time,
27        dr.last_wait_type,
28        dr.wait_resource,
29        dr.open_transaction_count,
30        dr.percent_complete,
31        dr.granted_query_memory
32 FROM   Sys.dm_exec_requests dr WITH(nolock)
33        RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock)
34                      ON dr.session_id = ds.session_id
35        RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock)
36                      ON ds.session_id = dc.session_id
37 WHERE  ds.session_id > 50
38 ORDER  BY ds.program_name

View CodeNumber of user connections

2. SELECT login_name,
4. Count (0) user_count
6. From sys.dm_exec_requests Dr with (NOLOCK)
8. right OUTER JOIN sys.dm_exec_sessions ds with (nolock)
10. On dr.session_id = ds.session_id
12. right OUTER JOIN sys.dm_exec_connections DC with (NOLOCK)
14. On ds.session_id = dc.session_id
16. WHERE ds.session_id >
18. GROUP by login_name
20. ORDER by user_count DESC

SQL Server Connection Pooling