Picture this: Your application is running great executing code quickly on your local machine, displaying its opening screen when the user clicks on an icon that requires database access to retrieve some relatively trivial information. The application pauses for 2 to 3 seconds before the information is displayed. Why the relatively long delay? The delay occurs while your application is opening a connection to the database.
Creating and opening a connection to a data store can be a time-consuming and resource-intensive proposition. One of the most common solutions to this problem has been to immediately open a connection to the data store when the application is starting and hold the connection open until the application is closed. Unfortunately, this approach means that the data store maintains connections for long periods of time, even if the user is in a part of the application that doesn't need data store connectivity. The problem grows significantly on multi-user systems, such as multi-tier and Web-based systems, because a separate connection might be required on a user-by-user basis from a single machine to the data store. We can easily get into a situation where every user has one or more open connections to the database and the database is consuming too many resources just managing connections. Ideally, we want the data store to spend most of its time delivering data and as little time as possible maintaining connections. This is where connection pooling can help.
Connection pooling is the reuse of existing active connections instead of creating new connections when a request is made to the database. It involves the use of a connection manager that is responsible for maintaining a list, or pool, of available connections. When the connection manager receives a request for a new connection, it checks its pool for available connections. If a connection is available, it is returned. If no connections are available and the maximum pool size has not been reached, a new connection is created and returned. If the maximum pool size has been reached, the connection request is added to the queue and the next available connection is returned, as long as the connection timeout has not been reached.
Connection pooling is controlled by parameters placed into the connection string. Table 4-1 lists the parameters that affect pooling.
| Parameter | Description |
|---|---|
| Connection Timeout | The time in seconds to wait while a connection to the data store is attempted. The default is 15 seconds. |
| Min Pool Size | The minimum amount of pooled connections to keep in the pool. The default is 0. It's usually good to set this to a low number, such as 5, when your application requires consistent, fast response even if the application is inactive for long periods. |
| Max Pool Size | The maximum allowed connections in the connection pool. The default is 100, which is usually more than enough for most Web site applications. |
| Pooling | A value of true causes the request for a new connection to be drawn from the pool. If the pool does not exist, it is created. The default is true. |
| Connection Reset | Indicates that the database connection will be reset when the connection is removed from the pool. The default is true. A value of false results in fewer roundtrips to the server when creating a connection, but the connection state is not updated. |
| Load Balancing Timeout, Connection Lifetime | The maximum time in seconds that a pooled connection should live. The maximum time is checked only when the connection is returned to the pool. This setting is useful in load-balanced cluster configurations to force a balance between a server that is online and a server that has just started. The default is 0. |
| Enlist | When this value is true, the connection is automatically enlisted into the creation thread's current transaction context. The default is true. |
To implement connection pooling, you must follow a few rules:
The connection string must be the same for every user or service that will participate in the pool. Remember that each character must match in terms of lowercase and uppercase as well.
The user ID must be the same for every user or service that will participate in the pool. Even if you specify integrated security=true, the Windows user account of the process will be used to determine pool membership.
The process ID must be the same. It has never been possible to share connections across processes, and this limitation extends to pooling.
Connection pooling is a client-side technology. The database has no idea that there might be one or more connection pools involved in your application. Client-side means that the connection pooling takes place on the machine that is initiating the DbConnection object's Open statement.
The connection pool group is an object that manages the connection pools for a specific ADO.NET provider. When the first connection is instantiated, a connection pool group is created. However, the first connection pool is not created until the first connection is opened.
A connection is removed from the pool of available connections for use and then returned to the pool of available connections. By default, when a connection is returned to the connection pool, it has an idle lifetime of 4 to 8 minutes (a time that is set somewhat randomly). This means the connection pool does not continue to hold on to idle connections indefinitely. If you want to make sure that at least one connection is available when your application is idle for long periods, you can set the connection string's Min Pool Size to one or more.
The connection string has a setting called the Load Balancing Timeout, formerly known as the Connection Lifetime. Connection Lifetime still exists for backward compatibility, but the new name better describes this setting's intended use. You should use this setting only in an environment with clustered servers because it is meant to aid in load balancing database connections. This setting is examined only when the connection is closed. If the connection stays open longer than its Load Balancing Timeout setting, the connection is destroyed. Otherwise, it is added back into the pool.
Look at this scenario where we can use the Load Balancing Timeout setting to solve a problem. Let's say two database servers are clustered together and they appear heavily loaded, so a third database server is added. The original databases still seem overloaded and the new server has few or no connections.
What happened? Connection pooling was doing its job by maintaining connections to the existing database servers. To solve the problem, we can specify a Load Balancing Timeout setting that essentially throws out some of the perfectly good connections so the new connection can go to a newly added database server. The tradeoff is some loss of performance because connections occasionally have to be re-created, but the connections will potentially go to a new server.
The default maximum connection pool size is 100. You can modify this by changing the Max Pool Size connection string setting, although the default setting is fine for most scenarios. How do you know if you need to change this value? You can use Performance Monitor to watch the NumberOfPooledConnections. If the maximum pool size is reached, any new requests for a connection are blocked until a connection frees up or the Connection Timeout connection string setting expires. The Connection Timeout setting has a default value of 15 seconds. If you exceed the Connection Timeout value, an InvalidOperationException is thrown:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
This same exception is thrown if you try to connect to a database server and the server cannot be reached or if the server is found but the database service is down.
Your application might need a larger Max Pool Size if you have a large Web application with hundreds of users hitting the site at the same time. If that's the case, by all means increase the Max Pool Size connection string setting.
If you are watching your application in Performance Monitor and notice that the NumberOfPooledConnections continues to rise uncontrollably, you might be suffering from connection leaks. A connection leak occurs when you open a connection but never close it. In some cases where you have code that closes the connection, if an exception occurs before you reach that line of code, you might never close the connection. The following code shows an example of this.
Visual Basic
Imports System.Data Imports System.Data.SqlClient Imports System.Configuration Public Class Form1 Private Sub Button1_Click(_ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Try ConnectionLeak() Catch ex As Exception 'do something cool to recover End Try MessageBox.Show("Done") End Sub Public Sub ConnectionLeak() Dim cnSettings As SqlConnectionStringBuilder cnSettings = New SqlConnectionStringBuilder(_ "Data Source=.;" _ + "Database=PUBS;" _ + "Integrated Security=True;" _ + "Min Pool Size=3;Max Pool Size=5") Dim cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString) cn.Open() Dim cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "raiserror ('Simulate an error in sql', 17,1)" cmd.ExecuteNonQuery() 'throws the SqlException cn.Close() 'Calls connection close, but this code is not reached End Sub End Class
C#
using System; using System.Windows.Forms; using System.Configuration; using System.Data.SqlClient; namespace ConnectionPooling { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { try { ConnectionLeak(); } catch (SqlException) { //do something cool to recover } MessageBox.Show("Done"); } public void ConnectionLeak() { SqlConnectionStringBuilder cnSettings; cnSettings = new SqlConnectionStringBuilder( @"Data Source=.;" + "Database=PUBS;" + "Integrated Security=True;" + "Min Pool Size=3;Max Pool Size=5"); SqlConnection cn = new SqlConnection(cnSettings.ConnectionString); cn.Open(); SqlCommand cmd = cn.CreateCommand(); cmd.CommandText = "raiserror ('Simulate an error in sql', 17,1)"; cmd.ExecuteNonQuery(); //throws the SqlException cn.Close(); //Calls connection close, but this code is not reached } } } As you can see, every time the button is clicked a SqlException is thrown and immediately bubbles up to the calling method. This means the Close method is not executed in the ConnectionLeak method. Depending on when garbage collection takes place, you might be able to continue clicking the button and not exceed the maximum pool size, but if this code is hit frequently, it won't take long to get an InvalidOperationException (timeout expired) because you exceeded the maximum pool size.
You can use Performance Monitor to identify orphaned connections by watching the NumberOfReclaimedConnections while the application is running. Figure 4-1 shows the NumberOfReclaimedConnections rising each time the button is clicked.
Figure 4-1: NumberOfReclaimedConnections rises due to connection not being closed.
To solve the problem in the previous code sample, you must rewrite your code to ensure that the Close or Dispose method is always called. You can do this by implementing a try/catch/finally block or a using block. The following code shows the ConnectionLeak method rewritten, implementing using blocks to solve the problem.
Visual Basic
Public Sub NoConnectionLeak() Dim cnSettings As SqlConnectionStringBuilder cnSettings = New SqlConnectionStringBuilder(_ "Data Source=.;" _ + "Database=PUBS;" _ + "Integrated Security=True;" _ + "Min Pool Size=3;Max Pool Size=5") Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString) cn.Open() Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "raiserror ('Simulate an error in sql', 17,1)" cmd.ExecuteNonQuery() 'throws the SqlException End Using End Using End Sub
C#
public void NoConnectionLeak() { SqlConnectionStringBuilder cnSettings; cnSettings = new SqlConnectionStringBuilder( @"Data Source=.;" + "Database=PUBS;" + "Integrated Security=True;" + "Max Pool Size=5"); using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString)) { cn.Open(); using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "raiserror ('Simulate an error in sql', 17,1)"; cmd.ExecuteNonQuery(); //throws the SqlException } } } Notice that the Close method is no longer in the code because the using block automatically calls the Dispose method. Also, the cmd object has a Dispose method, so a using block was added for the command as well. This is a much more elegant way to code connections, commands, and anything else that implements the Dispose method. The code examples in the rest of this book implement using blocks instead of explicitly calling the Close or Dispose method unless code is required to conditionally close the connection.
It's a good idea to keep pooling on at all times, but if you need to troubleshoot connection-related problems, you might want to turn it off. Pooling is on by default, but you can change the Pooling setting in the connection string to false to turn off pooling. Remember that performance will suffer because each Open statement will create a new connection to the database and each Close statement will destroy the connection. Also, without any limits, the server might deny the requests for a connection if the licensing limit is exceeded or the administrator has set connection limits at the server.
When you're working with a database server, it might not always be available it might have been removed from a cluster, or you might have needed to stop and start the service. When a database server becomes unavailable, the connections in the pool become corrupted.
You can use two methods in your code to recover from a corrupted connection pool: ClearPool and ClearAllPools. These are static methods on the SqlConnection and OracleConnection classes. The following code snippet works perfectly well and takes advantage of the connection pool until the database service is stopped and restarted.
Visual Basic
Private Sub Button3_Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Dim ver As String = Nothing Dim cnSettings As SqlConnectionStringBuilder cnSettings = New SqlConnectionStringBuilder( _ "Data Source=.;" _ + "Database=PUBS;" _ + "Integrated Security=True;" _ + "Max Pool Size=5") Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString) cn.Open() Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "Select @@Version" ver = CType(cmd.ExecuteScalar(), String) End Using End Using MessageBox.Show(ver) End Sub
C#
private void button3_Click(object sender, EventArgs e) { string ver=null; SqlConnectionStringBuilder cnSettings; cnSettings = new SqlConnectionStringBuilder( @"Data Source=.;" + "Database=PUBS;" + "Integrated Security=True;" + "Max Pool Size=5"); using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString)) { cn.Open(); using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "Select @@Version"; ver = (string)cmd.ExecuteScalar(); } } MessageBox.Show(ver); } If the database service is stopped and restarted, the previous code causes the following SqlException to be thrown.
A transport- level error has occurred when receiving results from the server. (provider: Session Provid er, error: 18 - Connection has been closed by peer)
To silently recover from this exception, you can clean the pools and then re-execute the code. The following code shows how to do this.
Visual Basic
Private Sub Button4_Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button4.Click try DisplayVersion() catch xcp as SqlException if xcp.Number <> 1236 then throw xcp 'first chance? System.Diagnostics.Debug.WriteLine("Clearing Pools") SqlConnection.ClearAllPools() 'recover DisplayVersion() 'retry end try End Sub public sub DisplayVersion() Dim ver As String = Nothing Dim cnSettings As SqlConnectionStringBuilder cnSettings = New SqlConnectionStringBuilder( _ "Data Source=.;" _ + "Database=PUBS;" _ + "Integrated Security=True;" _ + "Max Pool Size=5") Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString) cn.Open() Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "Select @@Version" ver = CType(cmd.ExecuteScalar(), String) End Using End Using MessageBox.Show(ver) End Sub
C#
private void button4_Click(object sender, EventArgs e) { try { DisplayVersion(); } catch (SqlException xcp) { if (xcp.Number != 1236) throw xcp; //first chance? System.Diagnostics.Debug.WriteLine("Clearing Pools"); SqlConnection.ClearAllPools();//recover DisplayVersion();//retry } } public void DisplayVersion() { string ver = null; SqlConnectionStringBuilder cnSettings; cnSettings = new SqlConnectionStringBuilder( @"Data Source=.;" + "Database=PUBS;" + "Integrated Security=True;" + "Max Pool Size=5"); using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString)) { cn.Open(); using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "Select @@Version"; ver = (string)cmd.ExecuteScalar(); } } MessageBox.Show(ver); }
When you are ready to put your application into production, how can you know whether the database can withstand having many people bang on the application? What happens if the database server goes down? What happens if the database server needs to be quickly rebooted? How about that quick stop and start of the database service?
In the last example, you saw what happens when the database service is stopped and restarted. In that scenario, we caught the first chance exception, cleared the pools, and retried. How can you get the same result with essentially no down time if the database server needs to be rebooted? This is where a failover partner in SQL Server 2005 can come into play.
So here's the scenario: You have set up database mirroring by using three SQL Server 2005 machines. The first machine is a "principal" database owner, the second machine is a database partner "mirror," and the third machine is the "witness," as shown in Figure 4-2. (The data-base mirroring feature is not available in SQL Server Express Edition.) When database mirroring is implemented, the client application normally talks to the principal. The mirror database is in the recovering state, which means that you cannot use it for any data access activity, including read-only access. When a transaction successfully commits on the principal, it is sent to the mirror as a transaction. You can think of this as a continuous backup-and-restore operation. The witness is simply checking to see if the principal and mirror are running, but it's the witness's job to automate the reversal of roles from principal to mirror and mirror to principal if the principal goes down.
Figure 4-2: Database mirroring with initial role configuration
In this scenario, what happens if your application has a connection string that is configured to point to the principal? You might think that you have to write some fancy code to detect a transition and then start talking to the mirror, which just became the principal, and then do the same to switch back. However, you can simply use the Failover Partner setting in the connection string. This setting is new in ADO.NET 2.0 and works with SQL Server 2005. When Failover Partner is set, if the connection detects a problem connecting to the database server, it will automatically clear the pool for this connection string and switch to the failover server, as shown in Figure 4-3.
Figure 4-3: Database mirroring after the witness reverses the roles
To simulate this feature, I have three instances of SQL Server 2005 installed on a virtual machine. The principal is the default instance ("."), the mirror is the Partner instance (".\Partner"), and the witness is the Witness instance (".\Witness"). The following code inserts a row into the principal.
Visual Basic
Private Sub Button5_Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button5.Click Dim ver As String = Nothing Dim cnSettings As SqlConnectionStringBuilder cnSettings = New SqlConnectionStringBuilder( _ "Data Source=.;" _ + "Database=FailTest;" _ + "Integrated Security=True;" _ + "Max Pool Size=5;" _ + "Failover Partner=.\Partner") Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString) cn.Open() Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = string.Format( _ "Insert into TestTable(Id, Name) Values('{0}','{1}')", _ Guid.NewGuid(),DateTime.Now.ToLongTimeString()) cmd.ExecuteNonQuery() End Using MessageBox.Show("Data entered into server: " + cn.DataSource) End Using End Sub
C#
private void button5_Click(object sender, EventArgs e) { SqlConnectionStringBuilder cnSettings; cnSettings = new SqlConnectionStringBuilder( "Data Source=.;" + "Database=FailTest;" + "Integrated Security=True;" + "Max Pool Size=5;" + @"FailOver Partner=.\Partner"); using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString)) { cn.Open(); using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = string.Format( "Insert into TestTable(Id, Name) Values('{0}','{1}')", Guid.NewGuid(),DateTime.Now.ToLongTimeString()); cmd.ExecuteNonQuery(); } MessageBox.Show("Data entered into server: " + cn.DataSource); } } Notice that there is no fancy code, just the addition of the Failover Partner setting in the connection string. If the principal instance is shut down, the witness detects the shutdown and makes the mirror operational. The SqlConnection object gets a first-chance exception, clears the pool for this connection string, and replaces the DataSource with the Failover Partner. The resulting message indicates that the data was inserted into the ".\Partner" instance. If the original principal becomes available, it assumes the role of mirror until the current principal goes down and roles reverse again.
