Pooling Connections

for RuBoard

In enterprise applications, it is particularly important to pay attention to issues that influence the scalability of your application. One of the primary issues involved is the handling of connections. This is because connections are generally a limited resource that consumes overhead on the data store. For some time, the idea of connection pooling has been used to achieve scalability by allowing an application thread to free a connection as soon as it is done using it and returning it to a pool of connections that other threads in the same or a different process may use. The overall result is that fewer connections need to be maintained by the data store. In other words, the ratio of connections to users is less than 1 to 1.

Note

In classic ADO, pooling database connections was handled either through the session pooling mechanism of OLE DB or by the connection pooling code implemented by the ODBC Driver manager when using the MSDASQL provider.

In this section, you'll learn how the SqlClient and OleDb providers handle pooling connections to allow applications to scale without consuming precious server resources.

Connection Pooling with SqlClient

Because the SqlClient provider communicates directly with SQL Server, it can't take advantage of OLE DB's connection pooling scheme. Therefore, SqlClient must implement its own connection pooling. It does this by relying on Component Services. As previously mentioned, Component Services supports object pooling. This mechanism allows a predefined number of instantiated object instances to be hosted in a COM+-managed pool. If an object instance is available, one is handed out to a client application when it requests a new instance, and subsequently returned to the pool when dereferenced for use by another client. This scheme allows clients to reuse objects that are expensive to create, thereby reducing the resources required on the server. SqlConnection is therefore a good candidate for a pooled component.

To support creating and configuring a connection pool, several additional attributes are included in the ConnectionString for SqlConnection , as shown in Table 9.2.

Table 9.2. SqlClient connection pooling attributes. This table lists the attributes you can use in the connection string when using connection pooling.

Attribute	Description
Connection Lifetime	Checked when a connection is returned to the pool and its creation time is compared to the current time. If the difference exceeds this value, the connection is destroyed . Used to force connections to be recycled periodically. Defaults to 0, which keeps the connection alive until the process ends.
Connection Reset	Defaults to true and specifies whether the connection's state is reset before it's pulled from the pool. Incurs an extra roundtrip to the server, but could be dangerous when set to false.
Max Pool Size	The maximum number of connections allowed in the pool. Defaults to 100.
Min Pool Size	The minimum number of connections allowed in the pool. The documentation states that the default is 0; however, tests indicate that two connections are created for each pool by default.
Pooling	Determines whether pooling is enabled. Defaults to true.

As you can see from Table 9.2, the Pooling attribute is by default set to true, so connections will be pooled automatically. In addition, the Connection Reset attribute is also set to true, which results in the sp_reset_connection stored procedure being executed on the connection before it is used by subsequent clients. As you might expect, a small performance benefit will be realized if you set Connection Reset to false because it removes the extra roundtrip to the server. However, the state of the connection won't be reset, so database context changes and other SET statements executed by a previous client would still be enforced for a new client, possibly leading to corrupted data. A typical connection string that utilizes pooling might then look like the following:

 Server=ssosa;Initial Catalog=ComputeBooks;Pooling=true;  Connection Reset=false;Enlist=true;Min Pool Size=5;trusted_connection=yes; 

Of course, in order for connections to be pooled, they must be the same. In fact, the connections are pooled per application domain based on the distinct text of the connection string. In other words, any difference at all (including spacing and capitalization) will result in two different pools being created. Obviously, the ideal situation is to pull the connection strings from a central source, as discussed previously, in order to ensure that they are added to the pool. Connections are also pooled based on their transaction context if the Enlist attribute is set to true. This allows components running in the same distributed transaction to pull connections from the pool that have access to the transaction.

Each time a new connection is opened with the Pooling attribute set to true, a new pool is created that contains the connection. If the Min Pool Size attribute is also specified, the appropriate number of additional connections are created and added to the pool.

Note

SqlClient also exposes five performance counters that you can view using the Performance Monitor utility found in the Administrative Tools group . These enable you to view both the current number of connections SqlClient has initiated and the current number pooled.

A second issue to consider is the security context of the pooled connections. Because these connections will be shared by more than one thread, which will likely map to more than one user , as in the case of ASP.NET applications, you'll want to make sure that the connections are made with a shared security context. When using SQL Server standard security, this means creating an account that has the appropriate permissions to the database or databases accessed by the components. When using a trusted connection, this means giving permissions to the Windows account under which the thread is running.

In the case of ASP.NET applications that use trusted connections, you would, of course, want all the threads doing work on behalf of users to be authenticated to SQL Server using the same account. This can be accomplished by using impersonation in ASP.NET. Simply put, impersonation allows the ASP.NET worker threads to run under the identity of an account specified in the Web.config file's identity element like so:

 <identity impersonate="true" /> 

If a name and password are provided, they will be used in preference to the token passed to ASP.NET from IIS. In this way, you can specify any Windows account and give that account permissions in SQL Server.

As you might be aware, IIS will pass either the IUSR_ machinename token when using anonymous authentication or the token for the account specified by the client when using basic, digest, or Windows authentication. So, when using basic, digest, or Windows authentication, you want to make sure that you are impersonating a different account and not simply setting the impersonate attribute to true. Otherwise, each user would attempt to log on to SQL Server using the authenticated account as passed from IIS.

By default, however, impersonation is set to false, which means that ASP.NET code will run under the SYSTEM account used by the ASP.NET runtime process (aspnet_wp.exe). It's also possible to change the account under which aspnet_wp.exe executes for all ASP.NET applications on the server by changing the processModel element in the system.Web section of Machine.config as follows :

 <system.web>   <processModel enable="true" username="domain\user" password="pwd"/> </system.web> 

Note

The username attribute can also be set to SYSTEM (the default) or MACHINE, which causes the ASP.NET runtime process to run under a special account called ASPNET, which is created when ASP.NET is installed on the server. In both cases, the password must be set to AutoGenerate.

In the case of serviced components, using a trusted connection means that the identity of the thread executing the component will be used to create the connection. In serviced components whose COM+ application is marked as Library (using the administrative interface or the ApplicationActivation attribute), this will be the thread that created the component. However, for COM+ applications marked as Server, you can configure the account used to run the components in the application in a separate DLLHOST.exe process.

As you probably guessed, because SqlClient is handling the pooling, the client code simply needs to call the Close method of the SqlConnection object in order to return the connection to the pool. The programming pattern then is one where the method opens and closes a connection with each invocation, rather than having the class hold on to an open connection. Of course, behind the scenes, the connection will be pulled from the pool and returned to it, thereby increasing performance and reducing the number of connections required.

Tip

Remember that the SqlDataAdapter implicitly opens and closes connections and so works well in this context.

To programmatically remove a connection from the pool, you simply need to call the SqlConnection object's Dispose method. Finally, by default, the pooled SqlConnection objects are not destroyed until the process that created them ends or the connection is somehow broken (noticed by the pooler when the connection is actually used).

Connection Pooling with OleDb

The scheme for pooling connections is different when using OleDb than when using SqlClient in that the support for pooling is provided by the OLE DB infrastructure (introduced in OLE DB 2.0), and is then made available to all OLE DB providers. This feature is referred to as OLE DB session pooling (or sometimes as resource pooling ).

The idea in session pooling is that, if enabled, OLE DB creates separate pools for each distinct set of connection attributes and transactions contexts used by the process. This creates less contention during the process of locking and finding connections to assign. In addition, an index is maintained on the pools in each process and makes finding the correct pool more efficient (rather than having to traverse each pool). In session pooling, if a connection in the pool hasn't been used for 60 seconds, it will be closed and removed from the pool. This value is configurable by modifying the registry, although OleDb has no connection string attribute equivalent to the Connection Lifetime attribute of SqlClient to enable connection recycling. Also, in the event a connection becomes unresponsive , OLE DB will requery the data source at intervals of 5, 10, and 50 seconds before giving up and destroying the connection.

To enable session pooling for a particular OLE DB provider on a machine-wide basis, you must edit the system registry. To enable session pooling, you must add a DWORD value of OLEDB_SERVICES to the HKEY_CLASSES_ROOT\CLSID\ provider key, in which provider is the COM class identifier for the OLE DB provider, such as SQLOLEDB or MSDAORA. By setting this value to 0xffffffff , all OLE DB services will be enabled, one of which is session pooling. Session pooling can be disabled by setting this value to 0xfffffffe , and all services except session pooling and automatic transaction enlistment can be disabled by using 0xfffffffc .

You can also enable or disable session pooling on a per-connection basis by adding OLE DB SERVICES=-1 to enable all service, OLE DB SERVICES=-2 to disable pooling, or OLE_DB_SERVICES=-4 to disable pooling and automatic transaction enlistment in the connect string. The latter is the equivalent of setting the Pooling and Enlist attributes to false in a SqlClient connection string. The result might be a connection code like the following:

 OleDbConnection con = new OleDbConnection(  "Provider=MSDAORA;OLE_DB_SERVICES=-4;Data Source=ORACLE8i7;  User ID=scott;Password=tiger"); con.Open(); 

In this case, pooling and transaction enlistment will be disabled.

for RuBoard