how database connection pool impacts performance?

Question

Hello everyone,

I am using .Net 2.0 + SQL Server 2005 Enterprise + VSTS 2008 + C# + ADO.Net to develop ASP.Net Web application. The ASP.Net Web application is database centric/driven. I want to know whether there are any performance reference data about what are the differences of performance when we turn on/off thread pool setting in ADO.Net connecting string setting? Performance I mean both the concurrent connection which could be supported and the execution time of a specific SQL Command executed from ADO.Net client side?

thanks in avdance, George

Answer 1

The performance difference is going to vary widely from application to application, so there is no hard data on what kind of gains you should expect.

The best course of action for you to take it to measure it by doing stress tests on your app with it configured with/without pooling and see how it fairs.

WCAT is one kind of stress tool you can use to load your ASP.NET application.

You can also try a profiler (of which there are many) to monitor your application to see how it performs under the stress.

Some Profilers: ANTS, dotTrace

Answer 2

write a small progeam to access the database. open the database connection without close it and loop 100 times.

1. Monitor the time you need to get the data each time.
2. Monitor the sever side connections using 'netstat -a'
3. with/without pooling

Answer 3

I don't have any performance stats at hand, but maybe a word of warning when using connection pooling: you might end up with too many small pools, instead of one large one.

ADO.NET will create a new connection pool for

• each connection string; it is very picky about this, too - if you have two connection strings that are different by even just a single space or something, those are considered two separate connection strings, and will lead to separate connection pools being created

• for each Windows credentials if using the "integrated security" (trusted connection) setting

So if you have a connection string something like

server=MyDBServer;database=MyDatabase;integrated security=SSPI;

one connection pool for each distinguishable user will be created - that's rather counter-intuitive, but that's the way it is (and it cannot be influenced / turned off).

Check out the MSDN docs on ADO.NET connection pooling for details:

When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when integrated *security is used, per Windows* identity. Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.

Also, if you have these two connection strings:

server=MyDBServer;database=MyDatabase;user id=tom;pwd=top$secret

and

server=MyDBServer;database=MyDatabase;user id=tom; pwd=top$secret;

those are considered different connection strings, and thus two separate connection pools will be created.

When attempting to measure the effect of connection pooling, this is something to be aware of!

Marc

Answer 4

You say 'thread pool', but obviously are talking about connection pool as your title shows.

There is a performance overhead for creating new database connections. It's a resource intensive operation, which is why we have ADO.NET connection pooling. Effectively connections are not closed, but merely returned to the pool where they stay active and can be reused by other parts of the same code base (inside the AppDomain).

One ADO.NET application pool is created PER unique connection string, therefore it is worth noting that when you use Integrated Security, you will lose the benefits of connection pooling as you will effectively have one connection pool per DB authenticated user.