1

We are expecting a large number of users to hit a Website built with IIS/.Net 4.0 that our SQL Server 2008 database server is providing data for. The database is around 2GB in size. We are contemplating increasing the MAX CONNECTION POOL to between 500 to 1000 -- to handle the estimated traffic. Two questions

(1) Does anyone have any hard performance numbers indicating the kind of improvement this may provide? (2) What is the impact of hitting that MAX CONNECTION POOL number in a production enviroment?

1 Answer 1

1

It really depends on the application and how it connects. "Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration. Connections are separated into pools by connection string, and by Windows identity when integrated security is used. Connections are also pooled based on whether they are enlisted in a transaction" -from http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Note you can actually decrease performance due to additonal blocking issues if you have crappy code executing without returning connections to the pool.

as far as # 2 goes your connection is queued until the application has closed any connections.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .