Sql Server connection pool

In an article looking at the cache, see the three words of the connection pool to recharge.

The original link is mentioned in this article.

In fact, we have been using the SqlServer connection pool. In the connection string, Pooling is enabled for connection pooling, with the default value of true, indicating enablement; you can add a property of Pooling = true after the link.

Two important parameters related to connection pool are Min Pool Size and MaxPool 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; when the Close () method is called, the connection pool manager returns the connection to the connection pool for 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 the connection is returned to the pool，Comparing its creation time with the current time, the connection is destroyed if the time length (in seconds) exceeds the value specified by Connection Lifetime.

This is useful in aggregate configurations (for enforcing load balancing between running servers and servers that have just been put online). Zero (0) value will make the connection pool have the largest connection timeout.  

From what we’ve seen above, we can see that even the simplest connection string is using connection pools.

Feeling like “links” are like a person, managed by the Connection Pool Manager, when “outsiders” need to link to instances, go back to the hypervisor to find a usable one; if the “links” come back after work and exceed the specified time, they can be killed….

asp.net Connection pool

Database linking is a dangerous, expensive, limited resource, especially in Multi-tier Web applications. You must manage your links correctly, because your approach will greatly affect the overall upgrade of the application.

High-performance applications maintain the shortest connection to the data sources in use, and take advantage of performance enhancement techniques, such as connection pooling.

Connection poolSQL Server, OLE DB and.NET framework data for ODBCProvider implicitbufferConnect。You can control the behavior of the connection pool by specifying different attribute values in the connection string.
Connection pool overview
data baseLink poolEnabling applicationsCanReuse existing links in the pool，Instead of repeatedly building links to databases. This technology will greatly increase the scalability of the application, because limited database links can serve many customers. This technology will also improve performance, becauseIt can avoid huge time for building new links.。
Specifically, most ADO. NET data providers use connection pools to improve the performance of applications built around Microsoft’s disconnected. NET architecture. The application first opens a connection (or gets a connection handle from the connection pool).One or more queries are processed, then the row set is processed, and finally the connection is released back to the connection pool. If there is no connection pool, these applications will take a lot of extra time to open and close connections.
The following is an example of the SQL Server. NET Framework data provider connection pool to illustrate some of the performance and usage of the connection pool. (also, there are ODBC.NET Framework data provider connection pool, OLE DB.N.ET Framework data provider connection pool.
Pool creation and allocation

WhenWhen connection is open，takeaccording toA precise one.matching algorithmcomeCreate connection pool，This algorithm associates the connection pool with the string in the connection. Each connection pool is associated with a different connection string.

WhenWhen the new connection is opened, if the connection string does not match the existing pool accurately, a new pool will be created.。

In the following example, three new SqlConnection objects are created, but only two connection pools are needed to manage them. Notice that the difference between the first and second connection strings is the value assigned for 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 activity process terminates. The maintenance of inactive or empty pools requires minimal system overhead.
When using the SQL Server.NET data provider link pool, it must be clear:
Links are pooled by precise matching of link strings.。

The pooling mechanism is sensitive to the spaces 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 a link is created, a connection pool is created, and the same link creates a connection pool; as long as the link is different, another connection pool (yellow word) is created.

           2：Creating a connection pool consumes resources, that is, this link does not occupy CPU.

           3：When this link is in use, other requests will wait until the release is released.Wait in the connection pool.；

           4：The connection pool seems to have an attribute that can limit the maximum number of links.
Connection add

The connection pool 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 requirement。The connection will be added to the pool as needed until the maximum pool size is reached.
WhenWhen a SqlConnection object is requested, if the available connection is available, it will be acquired from the pool.。To become a usable connection, the connection must not be used at the moment.，Has a matching transaction context or is not associated with any transaction context and has a valid link to the server.
If the maximum pool size has been reached and there is no available connection, the request will be queued.。When connections are released back into the pool, the connection pool manager reschedules the connections to meet these requests.When Connection calls Close or Dispose, the connection is released back to the pool.。

Removal of connections
If the connection lifetime has expired or the connection pool manager detects that the connection to the server has been disconnected, the connection pool manager removes the connection from the pool. Note that this situation can only be detected if you try to communicate with the server. If a connection is no longer connected to the server, it will beIts markup is invalid. The connection pool manager scans the connection pool periodically to find objects that have been released into the pool and are marked as invalid. Once found, these connections will be permanently removed.
If there is a connection to the 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 marks it invalid. When this happens, an exception will be generated. However, in order to release the connection back to the pool, it must be closed.

Select pool capacity
Establishing maximum limits is important for managing large systems with thousands of simultaneous requests from users. You need to monitor the performance of the linked pool and application to determine the optimal pool capacity of the system. The optimal capacity also depends on the hardware running SQL Server.
During development, you may need to reduce the default maximum pool capacity (currently 100) to help find link leaks.
If a minimum pool capacity is set, there is some performance penalty when the pool is initially filled to that value, although several customers with the initial link benefit from it. Note that the process of creating a new link is serialized, which means that the server cannot handle simultaneous requests when the pool is initially filled.

Monitoring link pooling
To monitor the application of link pooling, you can use the Profiler tool that comes with SQL Server or the performance monitor that comes with Microsoft Windows 2000.
To monitor the pooling of links by using SQL Server Profiler, the operation is as follows:
1.         Click start, point to the program, point to Microsoft SQL Server, and then click Profiler to run Profiler.
2.         In the file menu, point to new, and then click trace.
3.         Provide links, and then click OK.
4.         In the tracking properties dialog box, click the event label.
5.         In the list of selected event categories, ensure that audit login and audit logout events are displayed below security audits.
6.         Click start to track. When the link is created, audit login events will be seen; audit logout events will be seen when the links are closed.
Monitoring the link pooling through the performance monitor is as follows:
1.         Click start, point to the program, point to the management tool, and then click the performance performance performance monitor.
2.         Right click in the chart background, then click Add counter.
3.         In the performance object drop-down list box, click SQL Server: general statistics.
4.         In the list that appears, click user link.
5.         Click add, and then click close.
Adding pooling=false to config indicates that there is no connection pool in the program.
Add max pool size = “1000” to the connection string of the config file to expand the connection pool, and adjust the program to avoid multiple connections as much as possible。

 Connection Pool What is it?The link to the database that has been created is placed in the connection pool without closing it, so that it can be taken directly from the pool the next time it is reused without creating a new one.）
Every time a program needs to read and write databases. Connection. Open () connects to the database using ConnectionString, which establishes a connection for the program and remains open, which can then be checked using T-SQL statementsQuery / update database. When executed to Connection.Close (), the database will close the current connection. All right, everything looks so orderly.  
But if my program needs to open and close connections from time to time (such as ASP. Net or Web Services), such as when Http Request is sent to the server, we need to open Connection and use SElect * from table returns a DataTable / DataSet to the client / browser, and then closes the current Connection. Every time, Open/Close Connection is so frequently operated.The whole system is undoubtedly a waste.  
ADO.Net TeamA better solution is given. Save the previous Connection and hand it over to the next connection the next time you need to open the connection. This is Connection Pool.

View connection number correlation

1、Get the maximum number of user connections allowed by SQL Server

　　SELECT @@MAX_CONNECTIONS
　　

2、Gets the connection information of the current 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 required
7.  
   SELECT * FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = ‘YourDataBaseName’

3、Gets all the connection details of 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 SQL Server service was started.

　　SELECT @@CONNECTIONS

5 —View all requests in the current database system.

I just listed the fields that I think are more important and help me solve problems.

 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 Code

User connection number

1.  
   SELECT login_name,
2.  
   Count(0) user_count
3.  
   FROM Sys.dm_exec_requests dr WITH(nolock)
4.  
   RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock)
5.  
   ON dr.session_id = ds.session_id
6.  
   RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock)
7.  
   ON ds.session_id = dc.session_id
8.  
   WHERE ds.session_id > 50
9.  
   GROUP BY login_name
10.  
    ORDER BY user_count DESC

11.