Database link is a dangerous, expensive, limited resource, especially in multi-layer web applications. You must manage your link correctly because your method will greatly affect the overall upgrade of the application. High-performance applications maintain the shortest time connection with data sources in use, and utilize performance enhancements, such as connecting pools. Connecting pools for ODBC's SQL Server, OLE DB, and .NET Framework Data Provider Implicit Buffer Connections. You can control the behavior of the connection pool by specifying a different attribute value in the connection string.
Connection pool overview
The database link pool enables the application to reuse the existing links in the pool, rather than repeatedly establish links to the database. This technique will greatly increase the scalability of the application, because limited database links can provide services for many customers. This technology will also increase performance because it is possible to avoid huge time to establish a new link.
Specifically, most ADO.NET data providers use the connection pool to improve the performance of the application built around the .NET structure that is disconnected by Microsoft. The application first opens a connection (or get a connection handle from the connection pool), then runs one or more queries, then process the routine, and finally release the connection back to the pool. If there is no connection, these applications will spend many additional times to open and close connections.
Since the SQL Server .NET Framework data provider connection pool is used as an example to explain some of the performance and usage of the connection pool. (There is also an ODBC .NET Framework data provider connection pool, OLE DB .NET Framework data provider connection pool.)
Create and assignment of the pool
When the connection is open, the connection pool will be created according to a precise matching algorithm, which will associate the connection pool from the string in the connection. Each connection pool is associated with a different connection string. When a new connection is turned on, a new pool will be created if the connection string does not exactly match the pool.
In the following example, three new SQLCONNECTION objects will be created, but only two connection pools are required to manage these 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";
Cn.open ();
// pool a is created.
SqlConnection conn = new sqlConnection ();
Conn.connectionstring = "Integrated security = SSPI; Initial Catalog = PUBS";
Cn.open ();
// Pool B Is Created Because The Connection Strings Differ.
SqlConnection conn = new sqlConnection ();
Conn.connectionstring = "Integrated security = SSPI; Initial Catalog = Northwind";
Cn.open ();
// The connection string matches pool A.
Once the connection pool is created until the active process is terminated, it will be destroyed. Maintenance of non-active or empty pools requires only the least system overhead.
When using the SQL Server .NET Data Supply Link pool, you must clearly:
The link is poolized by a method that exactly matches the link string. The poolization mechanism is sensitive to spaces between name-value. For example, the following two link strings will generate a separate pool because the second string contains an additional empty character. SqlConnection Conn = New SqlConnection ("Integrated Security = SSPI; Database = Northwind");
Conn (); // pool a is created
SqlConMection conn = new sqlconnection ("Integrated Security = SSPI; Database = Northwind");
Conn (); // Pool B Is Created (EXTRA SPACES in String)
Connection added
The connection pool is created for each unique connection string. When you create a pool, you will create multiple connection objects and add it to the pool to meet the requirements of the minimum pool size. The connection will be added to the pool as needed until the maximum pool size is reached.
When requesting a SqlConnection object, if there is a available connection, the object will be obtained from the pool. To be available, the connection must not be used, with a matching transaction context or not associated with any transaction context, and has a valid link to the server.
If the maximum pool size is reached, the request will line up. When the connection is released in the pond, the connection pool management program satisfies these requests by reassigning the connection. When CONNECTION calls Close or Dispose, the connection is released in the back bank.
Removal
If the connection survival has expired, or the connection pool management program detects that the connection to the server is disconnected, the connection pool management program will remove the connection from the pool. Please note that this can only be detected only after the attempt is communicated with the server. If a connection is found to be connected to the server, it will be marked as invalid. The connection pool administrator regularly scans the connection pool and finds an object that is released into the pool and marked as invalid. These connections will be permanently removed after finding.
If there is a connection with the disappeared server, even if the connection pool management program does not detect the disconnected connection and it is not valid, it is possible to remove this connection from the pool. An exception is generated when this happens. However, in order to release the connection back in the pool, it must be closed.
Select pool capacity
It is very important to establish a largest limit for a large system that manages thousands of users simultaneously issued a request. You need to monitor the performance of link pools and applications to determine the best pool capacity of the system. The optimal capacity is also dependent 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 the minimum pool capacity is set, it will result in some performance loss when the pool is initially filled to reach the value, although several customers who initially link will benefit from it. Note that the process of creating a new link is serialized, which means that when the pool is initially filled, the server cannot handle the simultaneous request.
Monitor link poolization
To monitor the application of the application's application, you can use the Profiler tools issued with SQL Server, or the performance monitor released with Microsoft Windows 2000.
To use the SQL Server Profiler to monitor the link poolization, the operation is as follows:
1. Click Start, point to Programs, point to Microsoft SQL Server, and then click PROFILER to run Profiler.
2. In the File menu, point to New, and then click Track.
3. Provide link content and click OK. 4. In the Tracking Properties dialog box, click the Event Tag.
5. In the list of selected event categories, make sure that the review and auditing Turkist events are displayed under the Security Audit.
6. Click Run to start tracking. When the link is established, an audit login event will be seen; see the review of the event when the link is closed.
To monitor the link poolization through the Performance Monitor, the operation is as follows:
1. Click Start, point to Programs, point to Administrative Tools, and then click Performance Run Performance Monitor.
2. Right-click on the chart background and click Add Counter.
3. In the Performance Object drop-down list box, click SQL Server: General Statistics.
4. In the list of appears, click the user link.
5. Click Add, then click Close.
