I recently looked at an ASP.NET application where performance was acceptable up to a certain point, but then decreased rapidly. After a few hundred users, the application hit the wall and performance dropped, despite plenty of network bandwidth and server processing power. To get past this bottleneck, I tuned the connection pool that the application used to retrieve data from SQL Server.
Understanding the connection pool
A connection pool is a set of SQL Server connections maintained by ADO.NET and is used to communicate with SQL Server. Making a SQL Server connection is fairly costly. When ADO.NET opens a SqlConnection object, this delays the application. To minimize this delay, ADO.NET doesn't throw SqlConnection objects away when the Close or Dispose methods are called. Instead, it returns them to the connection pool. If a future request for a SqlConnection can be satisfied by a pooled connection, ADO.NET recycles that connection instead of opening a new one.
ADO.NET determines whether a pooled connection can be recycled by checking three things:
- Is the connection currently unused?
- Does the connection string match the desired connection string exactly?
- Does the transaction context of the thread match the desired transaction context exactly?
Note the requirement for an exact match. If you're using connection strings with user information, the corresponding SqlConnection objects won't pool with each other.
Suppose you initialize a SqlConnection object with this ConnectionString property:
cnn.ConnectionString = "Server=MAINSQL;Initial Catalog=DB1;username=John;password=pw1"
You use that connection, close it, and then initialize a SqlConnection object with this ConnectionString property:
cnn.ConnectionString = "Server=MAINSQL;Initial Catalog=DB1;username=Mary;password=pw2"
In this case, the second request will not be satisfied by returning the first connection from the pool, because the second connection string is not identical to the first one. In this situation, consider whether you can avoid placing authentication information directly in the connection string.
One possibility is to use integrated security rather than user name and password security on your SQL Server. Another is to authenticate users to your application, but then use an application role to connect to the SQL Server.
Customizing the connection pool
ADO.NET allows you to control the connection pool by adding values to the connection string. You'll want to use identical values in all connection strings in your application to be sure the connections can be pooled. There are six pool-related connection string properties.
The pooling property
This property can be true (the default) or false. Use this property to specify that a particular connection should not participate in the pool, but instead should be destroyed when it is closed. This property is helpful if you know that you'll never have an identical connection request in your application and would like to avoid the overhead associated with managing the pool.
The min pool size
This property is an integer with a default of zero. It specifies a minimum number of connections to maintain in the pool. If you set this to 5, for example, the first time you connect to the server, ADO.NET will create five connections and prepare them for pooling.
The max pool size
This property is an integer with a default of 100 and specifies the maximum number of connections to maintain in the pool.
The enlist property
This one can be true (the default) or false. It controls whether a connection will automatically enlist in the calling thread's transaction when it is taken from the pool.
The connection reset
This property can be true (the default) or false. It controls whether the connection will automatically be reset (clearing any pending results) when it's returned from the pool.
The connection lifetime
This property controls the maximum age of connections, in seconds. If a connection has been open for more than this number of seconds when you call its Close or Dispose method, it will be destroyed rather than being returned to the pool. By default, this property is set to zero, which means that connections are kept in the pool regardless of age.
The pool limiter
The Max Pool Size property acts as a limiter to the connection pool. If you leave this property set to 100 (the default), up to 100 connections from your application will be placed in the pool. When the 101st connection request comes along, it is queued to wait for an available connection. If no connection becomes available during the timeout period for the connection string (this period can be customized with the Connect Timeout connection string property and defaults to 60 seconds), an error is returned instead of an open connection.
The Max Pool Size property is designed to limit the resources that a runaway process can grab. But in the case of a busy ASP.NET application, it may have the undesired side effect of limiting the number of simultaneous users for your application. If the connection pool maxes out while new connection requests are still coming in, you'll see connection requests refused, apparently at random. The cure in this case is simply to specify a higher value for the Max Pool Size property.
Monitoring the connection pool
Fortunately, you don't have to guess at an appropriate size for the connection pool. .NET supplied some performance monitor counters that can help you monitor the connection pool on a computer. You’ll find these counters in the .NET CLR Data category in Performance Monitor:
- The SqlClient: Current # of pooled and non-pooled connections: This counter tells you how many connections currently exist, pooled or not.
- The SqlClient: Current # of pooled connections: This counter tells you how many connections are currently in the pool.
- The SqlClient: Current # of connection pools: This counter tells you how many connection pools have been created. This counter can help you tell whether you've configured things so that all connections are being taken from the same pool.
- The SqlClient: Peak # of pooled connections: This counter will tell you the highest number of connections that have been used.
- The SqlClient: Total # of failed connects: This counter tells you how many connection attempts have failed for any reason.
If the Peak counter is at the Max Pool Size value, and the failed connection counter increases while the application is running, it's time to think about increasing the size of the connection pool.
In Figure A, the horizontal blue line indicates the total number of failed commands since monitoring started; this particular counter has had a value of 17 for the duration of the monitoring session. The lower, jagged black line indicates the current number of pooled and nonpooled connections on this server. The vertical red bar is the current time indicator in Performance Monitor, showing which measurements were taken most recently.
|Monitoring SQL connections and failures|
No magic bullet
There's no single solution when fixing applications. Don't automatically assume that connection pooling will solve your performance problems; this is just one of many areas you can tune. Always tackle performance issues by investigating first (for example, by checking the performance monitor counters) and then making changes that address your findings.